SQL SERVER - How To Handle Deadlock

Posted by Muhsinathk under Sql Server on 6/8/2012 | Points: 10 | Views : 2154 | Status : [Member] | Replies : 1
BEGIN TRANSACTION
BEGIN TRY
UPDATE student SET st_Name = 'John1' WHERE st_ID=4
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Qualification SET Qualification = 'B.SC1' WHERE st_Quali_ID = 2
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY
END
END CATCH
COMMIT TRANSACTION

BEGIN TRANSACTION
BEGIN TRY
UPDATE Qualification SET Qualification = 'BSC2' WHERE st_Quali_ID =2
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE student SET st_Name = 'Mike1' WHERE st_ID=4
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY
END
END CATCH
COMMIT TRANSACTION

When i execute this transaction,i got an error

"A GOTO statement references the label 'RETRY' but the label has not been declared."

Is there any solution?




Responses

Posted by: Lakhangarg on: 6/12/2012 [Member] [Moderator] Silver | Points: 25

Up
0
Down
Hi,

Try the Following Code:
BEGIN TRANSACTION 

RETRY:
BEGIN TRY
UPDATE student SET st_Name = 'John1' WHERE st_ID=4
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Qualification SET Qualification = 'B.SC1' WHERE st_Quali_ID = 2
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY
END
END CATCH
COMMIT TRANSACTION

BEGIN TRANSACTION
RETRY:
BEGIN TRY
UPDATE Qualification SET Qualification = 'BSC2' WHERE st_Quali_ID =2
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE student SET st_Name = 'Mike1' WHERE st_ID=4
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY
END
END CATCH
COMMIT TRANSACTION


Thanks,
Lakhan Pal Garg

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response