Lock request time out period exceeded (Error:1222)
-
Hello all
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! :unsure:
-
I am working on the script for you
but the issue for the lock is that your transaction is still waiting until you rollback or commit manually
also in the second query use should check to see if the table exists
then if the column doesnt exist it wont error that way
use begin try
end try
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply