Multi-instances accessing to the same database and primary key conflict problem?

Question

0.00/5 (No votes)

See more:

I am building an application that allows multi-instances to run at the same time and access to the same database.
Suppose that the instance 1 of the app is executing the SQL statement

SQL

"Insert into MyTable(ID,Value) values(1, 'A')"

Here "ID" is the primary key of MyTable and the Value 1 was calculated before based on the current values of ID column of MyTable (to avoid conflict), I wonder if unexpectedly another instance of that app has also calculated for the same ID value (1) because both of instances have the same algorithm to find out the next ID to insert into MyTable.
And then the second instance may execute a Sql statement like this

SQL

"Insert into MyTable(ID,Value) values (1, 'B')".

That situation of course will lead to a primary key conflict.
Could you please help me solve this hard problem?
Thank you very much!

Posted 2-Nov-11 1:43am

King Boy

Updated 2-Nov-11 1:49am

member60

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RaisKazi · Accepted Answer · 2011-11-02T01:47:00

Solution 1

Use "Identity" on your Primary Key column. RDBMS will take care of rest.

http://msdn.microsoft.com/en-us/library/aa933196%28v=sql.80%29.aspx

Edit -
When you use "Identity" column, then your Insert statement should be as below. Need not to pass value for column having "Identity".

SQL

Insert into MyTable(Value) values('A')

Posted 2-Nov-11 1:47am

RaisKazi

Updated 2-Nov-11 1:51am

v3

Comments

BobJanova 2-Nov-11 8:02am

Good answer. In MySQL you set the column to be 'auto_increment' to do the same thing. One problem with this approach is that it can be difficult to find the ID of the row you just added.

RaisKazi 2-Nov-11 8:05am

Thank you Bob. Well not that difficult. :)
http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/

[no name] 2-Nov-11 8:04am

Thank you very much!
But unfortunately, I can't let the DBMS care for me about autoinserting values for ID column because the values of ID must be formatted and displayed as an important info, in fact it is not simply an integer, it may be preceded with some characters like "AAA".
Thank you very much!

RaisKazi 2-Nov-11 8:09am

In your question you mentioned only integer values. That's the reason I suggested "Identity".
In this case, You may have to look for some "Locking" kind of logic while generating your next Id.

[no name] 2-Nov-11 8:54am

I'm sorry, I should have cleared the things first. However your solution has introduced the term "Identity" and its usage to me that I have had little knowledge about it before. If there isn't any solution better than yours, I should accept your solution anyway :)
Thank you very much!

Amir Mahfoozi · Accepted Answer · 2011-11-02T02:47:00

I had the same problem and transactions solved my problem. There are transaction commands in TSQL and ADO or ADO.NET.
In all of them, you have to do something like this :

start transaction
  get biggest id
  compute next id
  insert new record with new ID
commit transaction

And another point is that transactions have multiple isolation levels and the clearest one for this type of problems is Serializable one.

For more information please read :
http://msdn.microsoft.com/en-us/library/ms188929.aspx[^]

http://msdn.microsoft.com/en-us/library/ms173763.aspx[^]

http://msdn.microsoft.com/en-us/library/2k2hy99x%28v=vs.71%29.aspx[^]

If you say that in which of TSQL , ADO or ADO.NET you are interested, I'll provide the code for you. However I'm sure that you have got the point.

Gopal Krishna Ranjan · Accepted Answer · 2011-11-02T01:51:00

Solution 2

You should change your ID generation algorithm.
You have to consider all the incremented values.
You can record all the issued ID's in another table and issue the id's from this table. By this way you will issue a single id only once.
And then insert them in main table only those id's for which all the fields are fulfilled successfully.

Posted 2-Nov-11 1:51am

Gopal Krishna Ranjan

Comments

[no name] 2-Nov-11 8:16am

I think the problem here is the time the two instances of the app execute the sql statement, if after the first inserted a new row into MyTable successfully, the second then will find out the next ID as 2 not 1 and no conflict happens. But my situation is the two instances seem to operate with the same sequence of commands (finding next ID, Inserting) nearly simultaneously.
I hope you figure out the situation here!
Thank you very much!