Deadlock when querying INFORMATION_SCHEMA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deadlock when querying INFORMATION_SCHEMA

Author

Topic

newbieee
Starting Member

6 Posts

Posted - 2011-08-07 : 18:54:00

Hi,

I have a process which dynamically alters my SQL2K5 table structure according to changes in a published meta-data layer.

For example, if a new column needs to be added and the table has NO dependancies - the steps would be:
1. Create scripts using T-SQL for any indexes & primary keys that already exist on the table [these scripts are included below]
2. Drop the table
3. Re-create the table from the meta-layer that has the new column
4. Execute the scripts created in step#1
5. Populate the table using BulkCopy

The above is initiated via a .NET assembly and runs in 3 concurrent streams on a daily basis.

I am receiving a deadlock error in step #1 - when I access the INFORMATION_SCHEMA tables to script out the indexes/keys. I have used the hint WITH(NOLOCK) in these scripts thinking this should prevent any locking when 3 streams of these actions are running concurrently.

Is there something more I need to do???

Any comments greatly appreciated.

[Scripts]

ALTER Procedure [dbo].[s$spScriptPrimaryKeyForTable]
@Tablename varchar(100)
AS

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH(NOLOCK)
WHERE upper(TABLE_NAME)=upper(@Tablename)
ORDER BY TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY CLUSTERED ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WITH(NOLOCK)
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ', '

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
-- PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK

SELECT ISNULL(@PKSQL,' ')

================

ALTER Procedure [dbo].[s$spScriptIndexesForTable]
@Tablename varchar(100)

AS

DECLARE @RetVal varchar(4000)
SET @RetVal = ''

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI WITH(NOLOCK)
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC WITH(NOLOCK) ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
AND upper(OBJECT_NAME(SI.Object_ID))=upper(@Tablename)
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000)
--SET @PKSQL = ''
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON [' + @IxTable + '] ('

-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name,IC.[is_included_column],IC.is_descending_key
FROM Sys.Index_Columns IC WITH(NOLOCK)
JOIN Sys.Columns SC WITH(NOLOCK) ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID,IC.is_included_column

DECLARE @IxColumn SYSNAME
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit set @IxIsIncl = 0
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIncl, @Desc
WHILE (@@FETCH_STATUS = 0)
BEGIN

IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
END
ELSE
BEGIN
--check to see if it's an included column
IF ((@IxIsIncl = 0) AND (@IxIncl = 1))
BEGIN
SET @IxIsIncl = 1
SET @IXSQL = @IXSQL + ') INCLUDE ('
END
ELSE
BEGIN
SET @IXSQL = @IXSQL + ', '
END
END

SET @IXSQL = @IXSQL + '[' + @IxColumn + ']'
--check to see if it's DESC
IF @Desc = 1
SET @IXSQL = @IXSQL + ' DESC'

FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIncl, @Desc
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')'

-- Print out the CREATE statement for the index
--SELECT 'IXSQL: ' + @IXSQL
IF @RetVal IS NULL
SET @RetVal = ''
--SELECT 'Retval: ' + @RetVal
SET @RetVal = @RetVal + @IXSQL + ' '

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

SELECT ISNULL(@RetVal,' ')

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-08 : 02:39:28

I hate to ask this question but are you usually only adding new columns? In that case there is no need to re-create the table entirely. Just do an ALTER TABLE ADD NewColumnName varchar(50) NOT NULL DEFAULT ('N/A') or the like...

And on a side note changing a database schema dynamically like this is NOT recommended practice. Just wanted to say that.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

newbieee
Starting Member

6 Posts

Posted - 2011-08-08 : 16:21:41

If the table has dependencies then we do an ALTER TABLE and add the column(s)(& yes we only do column additions. any data typing changes are handled by individual scripts). If no dependencies exist we drop & re-create.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-09 : 02:03:29

Can you please elaborate on the reasons _why_ you recreate the table when it has no dependencies? Doing it like this is 1) completely unnecessary, 2) requires bazillions more resources and 3) takes a multitude of more time. It just doesn't make sense. The only "reason" I can think of to do it like this is to order the columns in some specific way but that is also completely fubar.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources