What is DeadLock in SQL Server

What is DeadLock? Although it is like a movie star, it causes serious problems in applications.

If we want to briefly summarize DeadLock, two or more transactions are locking each other. SQL Server understands that this wait will not end, and kills one of the transactions and rollback this transaction.

The most classic example for DeadLock is:

Two students went to the canteen. One of the students reached out to a chocolate and held it. At that time, also the other student held the chocolate and a deadlock occurred. The canteen owner canceled the last incoming student’s request to solve the problem and gave his money back. The first student took the chocolate and left.

You need to know the transaction structure of SQL Server to understand deadlock.

Normally SQL Server works in auto commit. So, even if you don’t write BEGIN TRANSACTION at the beginning of the transaction and COMMIT at the end, sql server assumes that these statements exist.(It performs the Commit transaction based on the statement. If you want to commit multiple statements at the same time, you must explicitly specify the BEGIN TRANSACTION and COMMIT Statement.)

If you manually write BEGIN TRANSACTION at the beginning of the transaction, SQL Server does not complete the process until COMMIT is executed.

Example:

In the “A” procedure, there is a code block as follows:

In the “B” procedure, there is a code block as follows:

The “A” procedure started processing and performed an update on tableA but did not perform COMMIT yet. In the process of updating tableA, it put a lock so that another transaction does not update the same records. The lock that it put into tableA was not released yet because he did not execute COMMIT. So there is still the lock of the “A” procedure on tableA.

The “B” procedure also started processing immediately and performed an update on tableB but did not perform COMMIT yet. In the process of updating tableB, it put a lock so that another transaction does not update the same records. The lock that it put into tableB was not released yet because he did not execute COMMIT. So there is still the lock of the “B” procedure on tableB.

The “A” procedure continued without COMMIT, and then tried to update tableB as it appeared in the code block above. But it saw that the B procedure had lock on tableB, and it waited for the B procedure.

The “B” procedure also continued without COMMIT, and then tried to update tableA as it appeared in the code block above. But it saw that the A procedure had lock on tableA, and it waited for the A procedure.

As you will understand, if one of these above transactions is not automatically killed by SQL Server, both will wait forever. It’s a bit like a love story, but SQL Server isn’t willing to wait for each other forever. It kills one to keep the transactions going, and the other goes on his life.

There’s no such thing as Happy End in the transactions. 🙂

I recommend that you read the following article about capturing DeadLocks.

“How To Capture DeadLocks With Extended Events”