Locking a table for getting MAX in LINQ

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.

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, ... ...

Avoid locking and continuous access to the same "slow access" resources:

• At the start your application (service) calculate next id (max + 1, for example)
• In some variable (your should lock access to this variable ONLY) reserve, for example 100 values (it depends on your id's usage)
• Use these ids

Avoid using IDENTITY columns (if transaction rollbacks the id will be still incremented)

Use some table to store keys (last or next ids) for every table (or for the all tables as variant).

Luck.

For your web application:

How to change and access Application state:

Application.Lock();
Application["IDS"] = <some ids>
Application.UnLock();

Second solution:

Use stored procedure and code some like this:

declare @id int

update t set
    @id = id
    ,id = @id + 1 
from dbo.TableIdGenerator t where t.TableName = 'my table name that id I need'

select @id

Update operation is atomic and you can increment id and return current one. Don't forget to insert the first and only record for every table's ids.

Third solution:

Use CLR function.

If possible, try to rethink your database design. As you already noticed, having to use isolation level Serializable, when locking a whole table, can be troublesome.

I assume that the 5 character unique incrementing value is a requirement, because when it is not, you should definitely simply use an IDENTITY column. However, assuming this is not the case, here is an idea that might work.

Try to create a method that allows to express that 5 char identifier as a number. How to do this depends on which characters are allowed in your char identifier and which combinations are possible, but here are some examples: '00000' -> 0, '00009', -> 9, '0000z' -> 36, '00010' -> 37, '0001z' -> 71, 'zzzzz' -> 60466175. When you've found a method, use a incrementing primary key for the table and use a trigger that calculates the char identifier after you inserted a record. When a trigger is not appropriate, you can also do this in .NET. Or you can choose not to store that 5 char value in your database, because it is calculated. You can define it in a view or simply as property in your domain entity.

I have a solution, but not complete, It reduces the errors and problems: I have a file named: "lock.txt" and every try to get lock should open this file, get maximum code and generate next and update my table and close the file. the file is just for opening and closing, and there is no content in it.

public void DoTheJob()
{
int tries = 0;
    try
    {
        using (var sr = new StreamReader(@"c:\lock.txt"))
        {
            try
            {
                // get the maximum code from my table
                // generate next code
                // update current record with the new code
            }
            catch (Exception ex)
            {
                Logger.WriteError(ex);
            }
            finally
            {
                sr.Close();
            }
        }
    }
    catch
    {
        Thread.Sleep(2000); // wait for lock for 2 second
    tries++;
    if (tries > 15)
        throw new Exception("Timeout, try again.");
    }
}

Please say if this solution is correct. Or use StreamWriter.

It would be really useful to see your function GenerateNextCode, because it could be crucial piece of information. Why? Because I don't believe it is not possible to change this function from

f(code) -> code

to

f(id) -> code

If the latter is true, you could redesign your table and whole concept would be much easier.

But assuming it is really not possible, some quick solution -- use the pool table with pregenerated codes. Then use simply ids (autoincremented) in your main table. Disadvantage: you have to use extra join to retrieve the data. Personally I don't like it.

Another solution, "normal" one: keep lower isolation level and simply handle the exception (i.e. get the code again, calculate new code again and save the data). It is pretty classic situation, web, no web, does not matter.

Please note: you will get the same problem on concurrent editing of the same data. So in some sense, you cannot avoid this kind of problem.

EDIT:

So, I guessed right this function is simply f(id) -> code. You can drop the code column and use autoincrement id. Then add a view where the code is calculated on fly. Using view as a way of retrieving the data from the table is always a good idea (think of it as getter of property in C#). If you are afraid of CPU usage ;-) you can calculate code while inserting records (use the triggers).

Of course problems with locking records are not removed entirely (concurrent edits still can occur).

here is my answer, not completely correct, but without error.

public static void WaitLock<T>() where T : class
{
    using (var db = GetDataContext())
    {
        var tableName = typeof(T).Name;
        var count = 0;
        while (true)
        {
            var recordsUpdated = db.ExecuteCommand("UPDATE LockedTable SET IsLocked = 1 WHERE TableName = '" + tableName + "' AND IsLocked = 0");
            if (recordsUpdated <= 0)
            {
                Thread.Sleep(2000);
                count++;
                if (count > 50)
                    throw new Exception("Timeout getting lock on table: " + tableName);
            }
            else
            {
                break;
            }
        }
    }
}


public static void ReleaseLock<T>() where T : class
{
    using (var db = GetDataContext())
    {
        var tableName = typeof(T).Name;
        db.ExecuteCommand("UPDATE LockedTable SET IsLocked = 0 WHERE TableName = '" + tableName + "' AND IsLocked = 1");
    }
}
public static void GetContactCode(int id)
{
    int tries = 0;
    try
    {
        WaitLock<Contact>();
        using (var db = GetDataContext())
        {
            try
            {
                var ct = // get contact
                var maxCode = // maximum code
                ct.Code = // generate next
                db.SubmitChanges();
            }
            catch
            {
            }
        }
    }
    catch
    {
        Thread.Sleep(2000);
        tries++;
        if (tries > 15)
            throw new Exception("Timeout, try again.");
    }
    finally
    {
        ReleaseLock<Contact>();
    }
}