title: sql server 2008 Deadlock in two parallel transactions
body: <p>I have two transaction: T1 with <code>SERIALIZABLE</code> isolation level and T2 (I think - with default <code>READ COMMITTED</code> isolation level, but it doesn't matter).</p>
  
  <p>Transaction T1 performs <code>SELECT</code> then <code>WAITFOR</code> 2 seconds then <code>SELECT</code>.</p>
  
  <p>Transaction T2 performs <code>UPDATE</code> on data which T1 read.</p>
  
  <p>It causes deadlock, why transaction T2 don't wait for end of T1?</p>
  
  <p>When T1 has <code>REPEATABLE READ</code> isolation level everything is OK i.e. phantom rows occur.
  I thought when I raise isolation level up to <code>SERIALIZABLE</code>, T2 will wait for end of T1.</p>
  
  <p>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.</p>
  
  <p>Here's the code, unfortunately names of fields are in Polish.</p>
  
  <p>T1:
  </p>
  
  <pre>
  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;
  </pre>
  
  <p>T2:
  </p>
  
  <pre>
  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;
  </pre>
  
  <p>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.</p>
  
  <p>I read about deadlocks and locking mechanism in mssql but apparently, I haven't understand this topic yet.</p>
  
  <p><a href="https://stackoverflow.com/questions/3725822/deadlock-issue-in-sql-server-2008-r2-net-2-0-application">Deadlock issue in SQL Server 2008 R2 (.Net 2.0 Application)</a></p>
  
  <p><a href="https://stackoverflow.com/questions/661908/sql-server-deadlocks-between-select-update-or-multiple-selects">SQL Server deadlocks between select/update or multiple selects</a></p>
  
  <p><a href="https://stackoverflow.com/questions/959501/deadlock-on-select-update">Deadlock on SELECT/UPDATE</a></p>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/ms173763(v=sql.105).aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms173763(v=sql.105).aspx</a></p>
  
  <p><a href="http://www.sql-server-performance.com/2004/advanced-sql-locking/" rel="nofollow noreferrer">http://www.sql-server-performance.com/2004/advanced-sql-locking/</a></p>
  
  <h1><strong>edit</strong></h1>
  
  <p>I figure out the first UPDATE statement in T2 causes the problem, why?</p>