In this article, the term connection refers to a single logged-on session of the database. The article describes blocking in SQL Server and demonstrates how to troubleshoot and resolve blocking. So the transaction can try three times to check whether the required lock is available.Applies to: SQL Server (all supported versions), Azure SQL Managed Instance This scenario would be very useful if the transaction looking for the lock which was not released by the other transactions for a long time. In this example, the transaction can try to execute up to three times if it fails due to a deadlock. The Retråounter variable used here gives a chance for the transaction to execute again if it fails due to deadlock ( Error_Number 1205). IF ERROR_NUMBER() = 1205 - Deadlock Error Number BEGIN SET = 1 - Set to 1 only for Deadlock END IF = 1 BEGIN SET = + 1 - Increment Retry Counter By one IF ( > 3) - Check whether Retry Counter reached to 3 BEGIN RAISERROR( 18, 1) - Raise Error Message - if still deadlock occurred after three retries END ELSE BEGIN WAITFOR DELAY ' 00:00:00.05' - Wait for 5 ms GOTO RETRY - Go to Label RETRY END END ELSE BEGIN RAISERROR( 18, 1) IF ERROR_NUMBER() = 1205 - Deadlock Error Number BEGIN SET = 1 - Set to 1 only for Deadlock END IF = 1 BEGIN SET = + 1 - Increment Retry Counter By one IF ( > 3) - Check whether Retry Counter reached to 3 BEGIN RAISERROR( 18, 1) - Raise Error Message if - still deadlock occurred after three retries END ELSE BEGIN WAITFOR DELAY ' 00:00:00.05' - Wait for 5 ms GOTO RETRY - Go to Label RETRY END END ELSE BEGIN RAISERROR( 18, 1) PRINT ' Rollback Transaction' ROLLBACK TRANSACTION DECLARE bit - Whether to Retry transaction or not DECLARE varchar( 500) WAITFOR DELAY ' 00:00:05' - Wait for 5 ms UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221 COMMIT TRANSACTION END TRY UPDATE Customer SET LastName = ' John' WHERE CustomerId=111 After the delay, the transaction starts executing from the beginning ( RETRY: Label RETRY at the beginning of the transaction) using the below statement: You can increase the delay according to the size of your transactions. The delay is used here because the other transaction (which is not aborted) can complete its operation within delay duration and release the lock on the table which was required by the aborted transaction. 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. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |