Locking a table for getting MAX in LINQ
- by Hossein Margani
Hi Every one!
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.
My problem is when inserting a new row, two concurrent processes get max and insert an equal Code to the record.
my command is:
var maxCode = db.Customers.Select(c=>c.Code).Max();
var anotherCustomer = db.Customers.Where(...).SingleOrDefault();
anotherCustomer.Code = GenerateNextCode(maxCode);
db.SubmitChanges();
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:
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();
}
}
error:
Transaction (Process ID 60) was
deadlocked on lock resources with
another process and has been chosen as
the deadlock victim. Rerun the
transaction.
other IsolationLevels generates this error:
Row not found or changed.
Please help me, thank you.
