concurrent user read and update to mysql using c#

Question

I have a problem on my program, by solving this issue, it maybe can be done by using singleton method. But, i read from some article, they said,it is not a good way to implement it as the asp.net application concurrent users might have a lot.

My problem is,system running number is base on the particular record in the database example:

• document_type="INV" , document_year=2015 , document_month=04, document_running=0102.
• document_type="INV" , document_year=2015 , document_month=05, document_running=0002.

Therefore, when user create a new record, the logic of getting the new running number as below:

1. Get the document type = "inv" , current year and current month,
2. If not found. create a new document type , year and month record with the running number 0001.
3. if found. running + 1.

now, the problem come out. my situation is 5 users received the record information as when they pressed "save" button to create a new record: - document_type="INV" , document_year=2015 , document_month=05, document_running=0002.

and 5 users get the same running number INV15-05-0003 ( after 0002 + 1). By right, should be INV15-05-0003, INV15-05-0004, INV15-05-0005, INV15-05-0006 and INV15-05-0007.

how we should do to avoid if all users getting the wrong/outdated information. hope you all able to understand on my poor english. Regards, MH

Answer 1

The only way I see is to use a lock on the method you are calling to do the database management.

public class Dal
{
    private static object syncObject = new object();

    public static void InsertUpdate(...) 
    {
        lock (syncObject)
        {
            // 1. Get the document type = "inv" , current year and current month,
            // 2. If not found. create a new document type , year and month record with the running number 0001.
            // 3. if found. running + 1.
        }
    }
}

Answer 2

The simplest way you can get close to desired effect is to use Auto Incrementing for document_running field: https://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html.

Warning: Be aware, in this case your document_running is always going to be unique across all documents, not just for the records with the same type/year/month.

Alternative solution: Use GUID as document_running field. The same warning applies.