Firstly we should know about deadlock.
Deadlocks occur when two users have locks on separate objects and each user wants a lock on the other's object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
Let us use a scenario where a transaction A attempts to update table 1 and subsequently read/update data from table 2. At the same time there is another transaction B which is trying to update table 2, and subsequently read /update data from table 1. In this scenario, transaction X holds a lock that transaction Y needs to complete its tasks and vice versa. So in this scenario neither transaction can complete until the other transaction is release.
Transaction deadlock situation:
Transaction A:
BEGIN TRAN
UPDATE EMPLOYEE SET EMPLOYEENAME=' XYZ' WHERE EMPLOYEEID=111
WAITFOR DELAY '00:00:05'
UPDATE SALARY SET BASIC= 200 WHERE EMPLOYEEID=111
COMMIT TRAN
Transaction B:
BEGIN TRAN
UPDATE SALARY SET HRA=200 WHERE EMPLOYEEID=111
WAITFOR DELAY '00:00:05'
UPDATE EMPLOYEE SET EMPLOYEENAME='ABC' WHERE EMPLOYEEID=111
COMMIT TRAN
Result:
(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Reason for above deadlock:
What we did is we copied these two transactions to two different query windows and run them simultaneously. Consequently what happened is that Transaction X locks and updates Employee table whereas transaction X locks and updates Salary table. After a delay of 20 ms, transaction X looks for the lock on Salary table which is already held by transaction Y and transaction Y looks for lock on Employee table which is held by transaction X. So both the transactions cannot proceed further; the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.
How deadlock is resolved:
The user can choose which process should stop to allow another process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process running for a shorter period than another process. But it is recommended that we should provide a solution for handling deadlocks by finding the problem in our query code and then modify our processing to avoid deadlock situations.
Let us rewrite our transaction query.
Transaction A:
RETRY:
BEGIN TRAN
BEGIN TRY
UPDATE EMPLOYEE SET EMPLOYEENAME='XYZ' WHERE EMPLOYEEID=111
WAITFOR DELAY '00:00:10'
UPDATE SALARY SET BASIC= 100 WHERE EMPLOYEEID=111
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() =1205 -- DEADLOCK NUMBER
BEGIN
WAITFOR DELAY '00:00:00.05'
GOTO RETRY
ENDEND CATCH
Transaction B:
RETRY:
BEGIN TRAN
BEGIN TRY
UPDATE SALARY SET HRA=200 WHERE EMPLOYEEID=111
WAITFOR DELAY '00:00:10'
UPDATE EMPLOYEE SET EMPLOYEENAME='ABC' WHERE EMPLOYEEID=111
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER()=1205
BEGIN
WAITFOR DELAY '00:00:00.05'
GOTO RETRY
END
END CATCH
Result:
If we run these two Trans statement at the same time, we will get the result below.
(1 row(s) affected)
Rollback Transaction
(1 row(s) affected)
(1 row(s) affected)
In this way we can avoid deadlock in transaction.
