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)  TSQL simple deadlock question

Author  Topic  facemann Starting Member 1 Post Posted - 2008-06-09 : 17:25:36 I have a simple stored procedure that is deadlocking when two users attempt to run it at the same time. If the procedure deadlocks, I don't want to fail, I just want to try again. I have installed a try-catch loop but it seems to get stuck in the loop and fail anyway. Any ideas? Here is the error: GetNextKey IN CATCH LOOP on MappingInstruction table Msg 3609, Level 16, State 1, Procedure TrUDLD, Line 327 The transaction ended in the trigger. The batch has been aborted. And here is the procedure: ALTER PROCEDURE [dbo].[GetNextKey] @TableName VARCHAR(25), @NextKey INT OUTPUT, @flag INT = 0 AS DECLARE @RowCount INT, @Success CHAR(1) SET @Success = 'N' WHILE @Success = 'N' BEGIN BEGIN TRANSACTION BEGIN TRY UPDATE updb..KeyStorage SET lastkey = lastkey + 1 WHERE tablename = @TableName SELECT @ROWCOUNT = @@ROWCOUNT IF @ROWCOUNT = 1 BEGIN SELECT @NextKey = lastkey FROM updb..keystorage WHERE tablename = @TableName SET @Success = 'Y' END COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION PRINT 'GetNextKey IN CATCH LOOP on ' + @TableName + ' table' WAITFOR DELAY '000:00:05' --wait five seconds SET @Success = 'N' END CATCH END --WHILE @Success = 'N' GO It seems to me that the loop should just keep on trying until it succeeds in updating the KeyStorage table. I don't understand why it fails. Any suggestions would be appreciated.