Lock request time out period exceeded (Error:1222)

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 2008 Forums

Transact-SQL (2008)

Lock request time out period exceeded (Error:1222)

Author

Topic

Sinist0r
Starting Member

1 Post

Posted - 2011-02-17 : 06:47:39

Hello

I am trying to execute the following code:


BEGIN TRANSACTION
GO

USE TyroneTest
IF NOT EXISTS(select column_name from information_schema.columns  where column_name='CreatedDate' AND TABLE_NAME='tTest')
                BEGIN
                                PRINT 'Column CreatedDate not found, proceeding to create new column.'
                                
                                ALTER TABLE [TyroneTest].[dbo].[tTest]
                                ADD   CreatedDate DateTime NULL default getdate()

                                IF EXISTS (select column_name from information_schema.columns  where column_name='CreatedDate' AND TABLE_NAME='tTest')
                                BEGIN
                                                PRINT 'Created CreatedDate column.'
                                END
                END
ELSE
                BEGIN
                                PRINT 'Column UserCareId found, database script stopping.'
                                RETURN
                END

USE TyroneTest
IF NOT EXISTS(select column_name from information_schema.columns  where column_name='CreatedDate1' AND TABLE_NAME='tTest')
                BEGIN
                                PRINT 'Column CreatedDate1 not found, proceeding to create new column.'
                                
                                ALTER TABLE [TyroneTest].[dbo].[xtTest] -- Deliberate error (table xtTest doesnt exist)
                                ADD   CreatedDate1 DateTime NULL default getdate()

                                IF EXISTS (select column_name from information_schema.columns  where column_name='CreatedDate1' AND TABLE_NAME='tTest')
                                BEGIN
                                                PRINT 'Created CreatedDate1 column.'
                                END
                END
ELSE
                BEGIN
                                PRINT 'Column CreatedDate1 found, database script stopping.'
                                RETURN
                END

IF @@ERROR <> 0
                BEGIN
                                COMMIT TRANSACTION               
                END
ELSE
                BEGIN
                                ROLLBACK TRANSACTION
                END
GO

I am just trying to add a column to a table that is missing a necessary column. Now for testing purposes, I wanted to see what would happen if the table selected [xTest] did not exist.

I get the following error which is expected:

Column CreatedDate not found, proceeding to create new column.
Created CreatedDate column.
Column CreatedDate1 not found, proceeding to create new column.
Msg 4902, Level 16, State 1, Line 26
Cannot find the object "TyroneTest.dbo.xtTest" because it does not exist or you do not have permissions.

Ok that is fine, but where the problem occurs is that there is a lock
present with the following details:

When this happens i can't open any containers for this database in Management Studios and I have to manually kill the SPID causing the block. Obviously I dont want this to be a problem on the production database so what is the best way to proceed? Should I amend the lock_timeout variable??

Please help!