Deadlocks occur when several blocked processes are waiting to share a resource or table while each holds a resource that is being held by another process because those processes or transactions are in a deadlock state.
When exclusive locks are placed on resources needed by numerous processes and those processes are unable to proceed to completion, a deadlock on an SQL Server develops.
There are three sections in the error log that give information about the deadlock.
1. Deadlock Victim
2. Process list
3. Resource list
* Deadlock Victim:
The deadlock victim was killed by the SQL server. Normally, the error or exception message that the system displays to the user when their connection is cut off uses the phrase "deadlock victim".
* Process list:
The list of processes that are involved in the deadlock.
* Resource list:
The list of resources that are involved in the deadlock. Resources are database objects.
In the above case, to prevent the deadlock, always ensure that the database objects are accessed in the same order at every time.
The above diagram explains the deadlock situation, Here, there are two processes- Process A and Process B, and two tables, Table A and Table B. Process A is locked in Table A, and Process B is locked in Table B. In this case, Process B is waiting on Process A to release the lock on Table A. Similarly, Process A is waiting on Process B to release the lock on Table B.
process A: BEGIN;
process B: BEGIN;
process A: UPDATE users SET name = "Emp_1" WHERE emp_id = 001;
process B: UPDATE users SET name = "Emp_2" WHERE emp_id = 002;
Here acquired an exclusive lock in the transactions for both processes A and B.
process A: UPDATE users SET name = "Emp_3" WHERE emp_id = 002;
Here process A is in a waiting state, that is waiting for process B. After only the end of the transaction of process B, Process A will take place.
process B: UPDATE users SET name = "Emp_3" WHERE emp_id = 001;
Here, process B is in a waiting state, that is, waiting for process A. Process B only takes place after the end of the transaction of process A.
Examples of Deadlock in SQL server:
Here, we just created a table and inserted values.
First updated a value into the first row,
and an error occurred when trying to insert values into this table. The error is that the current transaction is aborted, and commands are ignored until the end of the transaction block.
If Rolling back the deadlock victim’s transaction, at that time, it will release all the locks held by that transaction and will allow execution of the other transactions.
To reduce the deadlock, increase LockTimeout. By reducing the deadlock, we can improve database performance.