title: Deadlock on SELECT/UPDATE
body: <p>I'm having a problem with deadlock on SELECT/UPDATE on SQL Server 2008.
  I read answers from this thread: <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> but I still don't understand why I get deadlock.</p>
  
  <p>I have recreated the situation in the following testcase.</p>
  
  <p>I have a table:</p>
  
  <pre><code>CREATE TABLE [dbo].[SessionTest](
      [SessionId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
      [ExpirationTime] DATETIME NOT NULL,
      CONSTRAINT [PK_SessionTest] PRIMARY KEY CLUSTERED (
          [SessionId] ASC
      ) WITH (
          PAD_INDEX  = OFF, 
          STATISTICS_NORECOMPUTE  = OFF, 
          IGNORE_DUP_KEY = OFF, 
          ALLOW_ROW_LOCKS  = ON, 
          ALLOW_PAGE_LOCKS  = ON
      ) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  
  ALTER TABLE [dbo].[SessionTest] 
      ADD CONSTRAINT [DF_SessionTest_SessionId] 
      DEFAULT (NEWID()) FOR [SessionId]
  GO
  </code></pre>
  
  <p>I'm trying first to select a record from this table and if the record exists set expiration time to current time plus some interval. It is accomplished using following code:</p>
  
  <pre class="lang-cs prettyprint-override"><code>protected Guid? GetSessionById(Guid sessionId, SqlConnection connection, SqlTransaction transaction)
  {
      Logger.LogInfo("Getting session by id");
      using (SqlCommand command = new SqlCommand())
      {
          command.CommandText = "SELECT * FROM SessionTest WHERE SessionId = @SessionId";
          command.Connection = connection;
          command.Transaction = transaction;
          command.Parameters.Add(new SqlParameter("@SessionId", sessionId));
  
          using (SqlDataReader reader = command.ExecuteReader())
          {
              if (reader.Read())
              {
                  Logger.LogInfo("Got it");
                  return (Guid)reader["SessionId"];
              }
              else
              {
                  return null;
              }
          }
      }
  }
  
  protected int UpdateSession(Guid sessionId, SqlConnection connection, SqlTransaction transaction)
  {
      Logger.LogInfo("Updating session");
      using (SqlCommand command = new SqlCommand())
      {
          command.CommandText = "UPDATE SessionTest SET ExpirationTime = @ExpirationTime WHERE SessionId = @SessionId";
          command.Connection = connection;
          command.Transaction = transaction;
          command.Parameters.Add(new SqlParameter("@ExpirationTime", DateTime.Now.AddMinutes(20)));
          command.Parameters.Add(new SqlParameter("@SessionId", sessionId));
          int result = command.ExecuteNonQuery();
          Logger.LogInfo("Updated");
          return result;
      }
  }
  
  public void UpdateSessionTest(Guid sessionId)
  {
      using (SqlConnection connection = GetConnection())
      {
          using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
          {
              if (GetSessionById(sessionId, connection, transaction) != null)
              {
                  Thread.Sleep(1000);
                  UpdateSession(sessionId, connection, transaction);
              }
              transaction.Commit();
          }
      }
  }
  </code></pre>
  
  <p>Then if I try to execute test method from two threads and they try to update same record I get following output:</p>
  
  <pre class="lang-none prettyprint-override"><code>[4] : Creating/updating session
  [3] : Creating/updating session
  [3] : Getting session by id
  [3] : Got it
  [4] : Getting session by id
  [4] : Got it
  [3] : Updating session
  [4] : Updating session
  [3] : Updated
  [4] : Exception: Transaction (Process ID 59) was deadlocked 
  on lock resources with another process and has been 
  chosen as the deadlock victim. Rerun the transaction.
  </code></pre>
  
  <p>I can't understand how it can happen using Serializable Isolation Level. I think first select should lock row/table and won't let another select to obtain any locks. The example is written using command objects but it's just for test purposes. Originally, i'm using linq but I wanted to show simplified example. Sql Server Profiler shows that deadlock is key lock. I will update the question in few minutes and post graph from sql server profiler. Any help would be appreciated. I understand that solution for this problem may be creating critical section in code but I'm trying to understand why Serializable Isolation Level doesn't do the trick.</p>
  
  <p>And here is the deadlock graph:
  <a href="http://img7.imageshack.us/img7/9970/deadlock.gif" rel="nofollow noreferrer">deadlock http://img7.imageshack.us/img7/9970/deadlock.gif</a></p>
  
  <p>Thanks in advance.</p>