sql server 2008 Deadlock in two parallel transactions

Question

I have two transaction: T1 with SERIALIZABLE isolation level and T2 (I think - with default READ COMMITTED isolation level, but it doesn't matter).

Transaction T1 performs SELECT then WAITFOR 2 seconds then SELECT.

Transaction T2 performs UPDATE on data which T1 read.

It causes deadlock, why transaction T2 don't wait for end of T1?

When T1 has REPEATABLE READ isolation level everything is OK i.e. phantom rows occur. I thought when I raise isolation level up to SERIALIZABLE, T2 will wait for end of T1.

This is a part of my college exercise. I have to show negative effects in two parallel transactions which have incorrect isolation level and absence of these effects with correct isolation level.

Here's the code, unfortunately names of fields are in Polish.

T1:

USE MR;

SET IMPLICIT_TRANSACTIONS OFF;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

-- 1. zapytanie
SELECT
    www.IdSamochodu, s.Model, s.Marka, s.NrRejestracyjny, o.PESEL, o.Nazwisko, o.Imie, o.NrTelefonu 
FROM
    WizytyWWarsztacie www
JOIN
    Samochody s
    ON s.IdSamochodu = www.IdSamochodu
JOIN
    Osoby o
    ON o.PESEL = s.PESEL
WHERE
    www.[Status] = 'gotowy_do_odbioru'
ORDER BY www.IdSamochodu ASC
;

WAITFOR DELAY '00:00:02';

-- 2. zapytanie
SELECT
    u.IdSamochodu, tu.Nazwa, tu.Opis, u.Oplata
FROM
    Uslugi u
JOIN
    TypyUslug tu
    ON tu.IdTypuUslugi = u.IdTypuUslugi
JOIN
    WizytyWWarsztacie www
    ON www.IdSamochodu = u.IdSamochodu AND
        www.DataOd = u.DataOd
WHERE
    www.[Status] = 'gotowy_do_odbioru'
ORDER BY u.IdSamochodu ASC, u.Oplata DESC
;

COMMIT;

T2:

USE MR;

SET IMPLICIT_TRANSACTIONS OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

UPDATE
    Uslugi
SET
    [Status] = 'wykonano'
WHERE
    IdUslugi = 2
;

UPDATE
    www
SET
    www.[Status] = 'gotowy_do_odbioru'
FROM
    WizytyWWarsztacie www
WHERE
    www.[Status] = 'wykonywanie_usług' AND
    EXISTS (
        SELECT 1
        FROM
            Uslugi u
        WHERE
            u.IdSamochodu = www.IdSamochodu AND
            u.DataOd = www.DataOd AND
            u.[Status] = 'wykonano'
        GROUP BY u.IdSamochodu, u.DataOd
        HAVING COUNT(u.IdUslugi) = (
            SELECT
                COUNT(u2.IdUslugi)
            FROM
                Uslugi u2
            WHERE
                u2.IdSamochodu = www.IdSamochodu AND
                u2.DataOd = www.DataOd
            GROUP BY u2.IdSamochodu, u2.DataOd
        )
    )
;

COMMIT;

I use SQL Management Studio and I have each transaction in different file. I run this by clicking F5 in T1 then quickly switch to file which contains T2 and again - F5.

I read about deadlocks and locking mechanism in mssql but apparently, I haven't understand this topic yet.

Deadlock issue in SQL Server 2008 R2 (.Net 2.0 Application)

SQL Server deadlocks between select/update or multiple selects

Deadlock on SELECT/UPDATE

http://msdn.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

http://www.sql-server-performance.com/2004/advanced-sql-locking/

edit

I figure out the first UPDATE statement in T2 causes the problem, why?

Please supply table definitions including indexes and the `SELECT` and `UPDATE` queries. — Martin Smith, Jan 12 '13 at 20:50
You'll get better guidance if you post the code/queries. I'm particularly interested to see what you mean by parallel transactions (your query code/batch will help me figure that out). — DWright, Jan 12 '13 at 20:50

score 6 · Answer 1 · answered Jan 12 '13 at 21:48

Troubleshooting deadlocks starts with obtaining the deadlock graph. This is an xml document that tells you the relevant bits about the transactions and resources involved. You can get it through Profiler, extended events, or event notifications (I'm sure that there are other methods, but this will do for now). Once you have the graph, examine it to see what each transaction had what type of locks on what resources. Where you go from there really depends on what's going on in the graph so I'll stop there. Bottom line: obtain the deadlock graph and mine it for details.

As an aside, to say that one or the other transaction is "causing" the deadlock is somewhat misleading. All transactions involved in the deadlock were necessary to cause the deadlock situation so neither is more at fault.

Thank you for the tip, I didn't know about such things as Profiler and deadlock graph. I'm newbie in MS SQL Server. — rzymek, Jan 13 '13 at 22:31
No worries. That's the great thing about anything: everyone starts out knowing nothing. :) — Ben Thul, Jan 14 '13 at 17:30

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

I had some problems with my SQL Managmenet Studio (Profiler didn't work) but finally I've obtained deadlock graph. This article was helpful for me.

To understand this graph I had to learn about locking mechanism and symbols.
I think here it is explained quite clearly.

Now, when I know about all these stuff, the cause of deadlock is quite obvious.
I've made sequence diagram for the described situation:

As I wrote earlier, when we get rid of the first UPDATE statement from transaction T2, deadlock does not occur.

In this situation T2 does not acquire a lock on the pk_uslugi index, thus second SELECT statement from transaction T1 will execute successfully and index pk_wizytywwarsztacie will be unlocked. After that, also T2 will be finished.

score 0 · Answer 3 · answered Jan 12 '13 at 21:59

The problem could be this:

The T1 Select S-locks the row
The T2 Update U-locks the row (succeeds)
The T2 Update X-locks the row (waits, lock is queued)
T2 tries to S-lock again, but the S-lock is incompatible with the queued X-lock.

Locks in SQL Server are queued. If the head of the queue waits, everything else behind it also waits.

Actually, I'm not entirely sure that this is the cause because the same problem should occur with REPEATABLE READ. I'm still posting this idea hoping that it helps.

score 0 · Answer 4 · edited Apr 23 '13 at 07:42

I ran into a similar issue where I was selecting from a list of available items and then inserting those items into a holding queue table. When I had too many concurrent requests, the select statement would return items that were also concurrently selected during another parallel request. When attempting to insert them into the holding queue table, I would receive a Unique Constraint error (because the same item couldn't go into the holding table twice).

I then tried wrapping a SERIALIZABLE transaction around the whole thing but then I ran into DEADLOCK errors because both transactions were holding onto a lock on the UC index (determined by my Deadlock graph).

I was finally able to resolve the issue by using an exclusive Row lock within the select statement.

You could try using an exclusive row lock on the table/rows in question. This would ensure that the lock on the rows within T1 will complete before T2 attempts to update the same rows.

EXAMPLE:

SELECT *
FROM Uslugi u WITH (XLOCK, ROWLOCK)

I'm not sure yet of the performance impact of this but while running load testing using multiple threads, it doesn't appear to have a negative impact.

sql server 2008 Deadlock in two parallel transactions

edit

4 Answers4