title: Locking a table for getting MAX in LINQ
body: <p>I have a query in LINQ, I want to get MAX of Code of my table and increase it and insert new record with new Code. just like the IDENTITY feature of SQL Server, but here my Code column is char(5) where can be alphabets and numeric.</p>
  
  <p>My problem is when inserting a new row, two concurrent processes get max and insert an equal Code to the record.</p>
  
  <p>my command is:</p>
  
  <pre><code>var maxCode = db.Customers.Select(c=&gt;c.Code).Max();
  var anotherCustomer = db.Customers.Where(...).SingleOrDefault();
  anotherCustomer.Code = GenerateNextCode(maxCode);
  db.SubmitChanges();
  </code></pre>
  
  <p>I ran this command cross 1000 threads and each updating 200 customers, and used a Transaction with IsolationLevel.Serializable, after two or three execution an error occured:</p>
  
  <pre><code>using (var db = new DBModelDataContext())
  {
      DbTransaction tran = null;
      try
      {
          db.Connection.Open();
          tran = db.Connection.BeginTransaction(IsolationLevel.Serializable);
          db.Transaction = tran;
          .
          .
          .
          .
          tran.Commit();
      }
      catch
      {
          tran.Rollback();
      }
      finally
      {
          db.Connection.Close();
      }
  }
  </code></pre>
  
  <p>error:</p>
  
  <blockquote>
    <p>Transaction (Process ID 60) was
    deadlocked on lock resources with
    another process and has been chosen as
    the deadlock victim. Rerun the
    transaction.</p>
  </blockquote>
  
  <p>other IsolationLevels generates this error:</p>
  
  <blockquote>
    <p>Row not found or changed.</p>
  </blockquote>
  
  <p>Please help me, thank you.</p>
  
  <p>UPDATE2: I have a .NET method generating the new code, which is alphanumeric.
  UPDATE3: My .NET function generates code like this: 0000, 0001, 0002, ... , 0009, 000a, 000b, 000c, ... , 000z, 0010, 0011, 0012, ... , 0019, 001a, 001b, 001z, ... ...</p>