Click here to Skip to main content
15,868,292 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am designing a multi-user application with MySql backend.

One of my table is:
SQL
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
Posted
Updated 3-Oct-16 0:15am

1 solution

In the query, try to update and evaluate the number of records changed. If it's < 1, then do an insert.

I think the secret is to properly handle exceptions. Put your call to ExecuteNonQuery into a while loop and exit that loop when there's no exception. Don't forget to include a short delay (500ms or so) before attempting the query again.

EDIT ===========================

This is what I do in MS-SQL, and MYSQL should be similar:

SQL
UPDATE [table]
SET [field1]=@value1
    ...
WHERE [field2] = @value2
IF (@@ROWCOUNT = 0)
    INSERT...


Of course, you probably need to put your lock/unlock and transaction statements in the appropriate places.
 
Share this answer
 
v2
Comments
Member 11040029 3-Oct-16 6:21am    
That's pretty good idea John. I made mind for this very path as well but it looked to me a costlier alternative from two points of view, first the programming part and second one is resource utilisations of the system. I am looking for a way that mysql can provide by using locks, transactions or any other technique if present.

And John, what do you mean by try to update and evaluate the number of records changed? Do u mean to handle this counter at frontend level or something else?
#realJSOP 3-Oct-16 8:16am    
I update my solution to include some sample SQL.
#realJSOP 3-Oct-16 8:34am    
Why did you vote 1 on my answer?
Member 11040029 3-Oct-16 8:55am    
Sorry..that was just to get u return on this post ASAP! :) .. upvoted it to 4
#realJSOP 3-Oct-16 9:09am    
You don't use voting for that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900