I am designing a multi-user application with MySql backend.
One of my table is:
create table accountInfo
(
id smallint primary key,
name varchar(30) not null
)Engine = InnoDB charset = utf8;
The id column's value start with 1 and must be incremented for each new insert query.
I must also not use an auto_increment column for id field.
Now, consider the situation,
There are 2 users A and B, who are likely to insert records simultaneously at the same instance of time. For this, they first read the max(id) from the table and increment it by 1 and then executes the insert query with incremented value for ID field.
But, since both are reading value of ID at the same time, both will receive the same value, lets say 1. Now while inserting, one of them would get a duplicate value error, which affects user experience.
Also, applying lock in shared mode, may again cause the same problem of duplicate value.
I tried to implement this scenario with FOR UPDATE lock. But, what happens is either of the user A or B is ending up with a deadlock.
I do not understand why this deadlock occurs.
What I have tried:
User A executes:
1. select MAX(id) as id from account as id for update;
2. gets id = 1;
3. _ledgerID = 1;
User B executes (at the same time):
1. select MAX(id) as id from account as id for update;
2. user 2 is waiting....
User A
1. insert into account_ledger(id,name) values("+_ledgerID+",'Jack');
2. on Command.ExecuteNonQuery(), exception occurs as "Deadlock found when trying to get the lock.."
User B
1. gets id = 1