Why does deadlock occur?

Question

I use a small transaction consisting of two simple queries: select and update:

SELECT * FROM XYZ WHERE ABC = DEF 

and

 UPDATE XYZ SET ABC = 123 WHERE ABC = DEF 

Often a situation arises when a transaction is started by two threads, and depending on the level of isolation level lock (RepeatableRead, Serialization). Both transactions try to read and update the exact same line. I wonder why this is happening. What is the order of queries leading to a deadlock? I read a little about the lock (general, exclusive) and how long the locks remain for each isolation level, but I still do not quite understand ...

I even prepared a simple test that always leads to a deadlock. I looked at the test results in SSMS and SQL Server Profiler. I started the first request, and then immediately the second.

First request:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT ... WAITFOR DELAY '00:00:04' UPDATE ... COMMIT 

Second request:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT ... UPDATE ... COMMIT 

Now I can’t show you detailed logs, but it looks less or more similar (I most likely missed Lock: deadlock, etc. somewhere):

 (1) SQL:BatchStarting: First query (2) SQL:BatchStarting: Second query (3) Lock:timeout for second query (4) Lock:timeout for first query (5) Deadlock graph 

If I understand the locks well, in (1) the first request takes a general lock (for SELECT), then goes into sleep mode and saves the general lock until the end of the transaction. In (2), the second query also accepts a general lock (SELECT), but cannot accept an exclusive lock (UPDATE), while the same line has general locks, which leads to a lock: timeout. But I can’t explain why the second timeout for the second request occurs. I guess I don’t understand the whole process very well. Can anyone give a good explanation?

I did not notice deadlocks using ReadCommitted, but I'm afraid they might happen. Which solution do you recommend?

Answer 1

A deadlock occurs when two or more tasks constantly block each other with each task that has a resource lock that other tasks try to block.

http://msdn.microsoft.com/en-us/library/ms177433.aspx

Answer 2

"But I can’t explain why the timeout for the second request occurs."

Since the first request contains a common lock. Then the update in the first request also tries to get an exclusive lock, which makes it sleep. Thus, the first and second request both wait for the other to wake up - and this is a dead end, which leads to a timeout :-)

In mysql, it works better - a deadlock is detected immediately and one of the transactions is rolled back (you do not need to wait for a timeout :-)).

In addition, in mysql you can do the following: prevent deadlock :

 select ... for update 

which will put a write lock (i.e. exclusive lock) only from the start of the transaction, and in this way you will avoid a deadlock situation! Perhaps you can do something similar in your database engine.

Answer 3

For MSSQL, there is a mechanism to prevent deadlocks. What you need here is called the WITH NOLOCK hint.

In 99.99% of cases of SELECT it is useful and there is no need to bind SELECT to UPDATE. There is also no need to put SELECT in a transaction. The only exception is when dirty reads are not allowed.

Changing your requests in this form will solve all your problems:

 SELECT ... FROM yourtable WITH (NOLOCK) WHERE ... SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION UPDATE ... COMMIT 

Answer 4

It has been a long time since the last time I did this, but I believe that the select statement creates a read lock, which only prevents data from changing - therefore, several requests can be executed and the read lock can be divided by the same data. Locking general reading for consistent reading, that is, if you read the same line several times in a transaction, the reading sequence should mean that you should always get the same result.

The update statement requires an exclusive lock, and so the update statement needs to wait until the read lock is released.

None of the two transactions will release the locks, so the transactions fail.

Different database implementations have different strategies for solving this problem: with Sybase and MS-SQL servers that use lock escalation with a timeout (escalation from read-write-lock) - Oracle, I believe (at some point) is implemented consistent read consistency, although using a backlog, where MySQL has another strategy.