title: Pessimistic locking in EF code first
body: <p>I'd like to lock specified row(s) in my table exclusively, so no reads no updates allowed until the actual transaction completes. To do this, I've created a helper class in my database repository:</p>
  
  <pre><code>public void PessimisticMyEntityHandler(Action&lt;IEnumerable&lt;MyEntity&gt;&gt; fieldUpdater, string sql, params object[] parameters)
  {
      using (var scope = new System.Transactions.TransactionScope())
      {
          fieldUpdater(DbContext.Set&lt;MyEntity&gt;().SqlQuery(sql, parameters));
          scope.Complete();
      }
  }
  </code></pre>
  
  <p>Here is my test code. Basicly I'm just starting two tasks and both of them tries to lock the row with the Id '1'. My guess was that the second task won't be able to read(and update) the row until the first one finishes its job, but the output window shows that it actually can.</p>
  
  <pre><code>Task.Factory.StartNew(() =&gt;
  {
        var dbRepo = new DatabaseRepository();
        dbRepo.PessimisticMyEntityHandler(myEntities =&gt;
        {
                  Debug.WriteLine("entered into lock1");
  
                  /* Modify some properties considering the current ones... */
                  var myEntity = myEntities.First();
                  Thread.Sleep(1500);
                  myEntity.MyEntityCode = "abcdefgh";
                  dbRepo.Update&lt;MyEntity&gt;(myEntity);
  
                  Debug.WriteLine("leaving lock1");
       }, "SELECT * FROM MyEntities WITH (UPDLOCK, HOLDLOCK) WHERE Id = @param1", new SqlParameter("param1", 1));
  });
  
  Task.Factory.StartNew(() =&gt;
  {
        Thread.Sleep(500);
        var dbRepo = new DatabaseRepository();
        dbRepo.PessimisticMyEntityHandler(myEntities =&gt;
        {
                  Debug.WriteLine("entered into lock2");
  
                  /* Modify some properties considering the current ones... */
                  var myEntity = myEntities.First();
                  myEntity.MyEntityCode = "xyz";
                  dbRepo.Update&lt;MyEntity&gt;(myEntity);
  
                  Debug.WriteLine("leaving lock2");
       }, "SELECT * FROM MyEntities WITH (UPDLOCK, HOLDLOCK) WHERE Id = @param1", new SqlParameter("param1", 1));
  });
  </code></pre>
  
  <p>Output window:</p>
  
  <pre><code>entered into lock1
  entered into lock2
  leaving lock2
  leaving lock1
  </code></pre>