![]() ![]() If the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back, and if the exception is occurred due to deadlock ( Error_Number 1205), the transaction waits for 5 milliseconds. The TRY/ CATCH method is used to handle the exceptions in the transactions. Here I have used Label RETRY at the beginning of both the transactions. 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 - Go to Label RETRY END END CATCH UPDATE Orders SET ShippingId = 12 Where OrderId = 221 WAITFOR DELAY ' 00:00:05' - Wait for 5 ms UPDATE Customer SET FirstName = ' Mike' WHERE CustomerId=111 RETRY: - Label RETRY BEGIN TRANSACTION BEGIN TRY The below example shows the deadlock situation between the two transactions. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa neither transaction can complete until the other transaction releases locks. Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. This article will explain how to handle deadlocks in a user-friendly way. ![]() Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. A deadlock is a situation wherein two transactions wait for each other to give up their respective locks. ![]()
0 Comments
Leave a Reply. |