Enable Dark Mode!
how-to-analyse-deadlocks-in-sql-server.jpg
By: Saleekha Musthari

How To Analyse Deadlocks in SQL Server

Technical Odoo 16

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.

How To Analyse Deadlocks in SQL Server-cybrosys

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:

How To Analyse Deadlocks in SQL Server-cybrosys

Here, we just created a table and inserted values.

How To Analyse Deadlocks in SQL Server-cybrosys

First updated a value into the first row,

How To Analyse Deadlocks in SQL Server-cybrosys

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.


If you need any assistance in odoo, we are online, please chat with us.



0
Comments



Leave a comment

 


whatsapp_icon
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message