title: Deadlock with single stored procedure and multiple threads
body: <p>I'm running some tests on SQL transactions in multithreaded environment. I am trying to generate deadlock by executing single stored procedure in a loop from 2 threads running in paralel. Both of my threads use the same method on start, which continuously executes a single stored procedure:</p>
  
  <pre><code>using (TestDataContext db = new TestDataContext())
  {
      while (true)
      {
          db.DeadLocking();
      }
  }
  </code></pre>
  
  <p>Can someone give an example of "DeadLocking" stored procedure that would reliably generate deadlock in this case senario. It must use transactions (single or multiple). I've researched for quite a bit and saw many examples of how to generate deadlocks in sql, however, none of them worked in my code. Please, help.</p>
  
  <p>Update: Following Marc's suggestions I tried this sproc to no avail:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[DeadLocking]
  AS
  BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  SET NOCOUNT ON
          BEGIN TRANSACTION
              DECLARE @val varchar(1)
              SELECT @val = Record FROM Test.dbo.Records WHERE RecordId = 1
              UPDATE Test.dbo.Records SET Record = @val WHERE RecordId = 1
          COMMIT TRANSACTION
  END
  </code></pre>
  
  <p>Running it from both threads in paralel supposed to lock those threads on eachother. What am I doing wrong?</p>
  
  <p>Update: The above procedure does cause deadlock, however, it takes at least 3 threads to do this not 2 (no idea why, maybe it takes 2 but also takes forever). Funny thing is that this also causes deadlock:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[DeadLocking]
  AS
  BEGIN
      SET NOCOUNT ON
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
      UPDATE Test.dbo.Records SET Record = 1 WHERE RecordId = 1
  END
  </code></pre>
  
  <p>I'm guessing that happens because stored procedure itself implements some sort of transaction logic behind the scene. If anyone has more info on why it happens, please share. 
  Mind you, the deadlock happens only on UPDATE and doesn't happen on SELECT. This occurs on both SERIALIZABLE and REPEATABLE READ isolation levels.</p>