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?

Answer 1

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.