title: The following code can’t cause a deadlock, and yet I get Transaction ( Process ID 54 ) was deadlocked …
body: <p>Service operation <code>MyMethod(int id)</code> retrieves particular row ( based on an <code>id</code> parameter ) from a single DB table and just before it returns it also saves that state back to the table. If two calls ( first call happens within transaction <strong>T1</strong> while second within transaction <strong>T2</strong> ) to <code>MyMethod()</code> are made at the same time, then service will try to execute the two calls concurrently. Since both <strong>T1</strong> and <strong>T2</strong> try to access same DB table, one of the two transaction will be granted access to the resource, while other should get blocked until the original transaction commits or aborts. But instead I get an exception <em>Transaction ( Process ID 54 ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim</em></p>
  
  <p>I don’t understand reasoning behind throwing deadlock exception since as far as I can tell there isn’t any danger of a deadlock. For one thing, the two transactions accessed and operated on different rows. Why wasn’t instead the DB resource just locked until original transaction committed or aborted?!  </p>
  
  <p>Here is the code:</p>
  
  <pre><code>[ServiceContract]
  public interface IService
  {
      [OperationContract]
      [TransactionFlow(TransactionFlowOption.Allowed)]
      void Process(int id);
  }
  
  [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall, IncludeExceptionDetailInFaults=true)]
  public class Service : IService
  {
      string state_Data = "";
  
      [OperationBehavior(TransactionScopeRequired = true)]
      public void Process(int id)
      {
          GetState(id);
          Thread.Sleep(6000);
          SaveState(id);
      }
  
  
      private void GetState(int id)
      {
          using (SqlConnection con = new SqlConnection())
          {
              con.ConnectionString = "data source=localhost; initial catalog=WCF; integrated security=sspi;";
  
              SqlCommand cmd = new SqlCommand();
              cmd.CommandText = "SELECT * FROM StateTable WHERE id = @id";
              cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
              cmd.Connection = con;
              con.Open();
  
              SqlDataReader reader = cmd.ExecuteReader();
              if (reader.Read())
                  state_Data = reader["State"].ToString();
          }
      }
  
      private bool SaveState(int id)
      {
          using (SqlConnection con = new SqlConnection())
          {
              con.ConnectionString = "data source = localhost; initial catalog=WCF; integrated security=sspi;";
  
              SqlCommand cmd = new SqlCommand();
              cmd.CommandText = "UPDATE StateTable SET State=@State WHERE Id = @id";
              cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
              cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = state_Data;
              cmd.Connection = con;
              con.Open();
  
              int ret = cmd.ExecuteNonQuery();
              return ret == 1;
          }
      }
  }
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>In case this will help, here is client code:</p>
  
  <p>FIRST CLIENT:</p>
  
  <pre><code>        ServiceClient proxy = new ServiceClient("WSDualHttpBinding_IService");
  
          using (TransactionScope scope = new TransactionScope())
          {
              proxy.Process(1);
              scope.Complete();
          }
  </code></pre>
  
  <p>SECOND CLIENT:</p>
  
  <pre><code>        ServiceClient proxy = new ServiceClient("WSDualHttpBinding_IService");
  
          using (TransactionScope scope = new TransactionScope())
          {
              proxy.Process(2);
              scope.Complete();
          }
  </code></pre>
  
  <p>Thank you</p>