多实例访问同一数据库和主键冲突问题? [英] Multi-instances accessing to the same database and primary key conflict problem?

问题描述

我正在构建一个允许多实例同时运行并访问同一数据库的应用程序.
假设应用程序的实例1正在执行SQL语句

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

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

这里的"ID"是MyTable的主键，值1是根据MyTable的ID列的当前值计算出来的(为避免冲突)，我想知道是否出乎意料的是，该应用程序的另一个实例是否也针对相同的ID值进行了计算(1)因为两个实例具有相同的算法来找出要插入到MyTable中的下一个ID.
然后第二个实例可以执行这样的Sql语句

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

"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!

推荐答案

在主键"列上使用身份" . RDBMS将照顾您.

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

编辑-
当您使用身份"列时，那么您的Insert语句应如下所示.不必为具有"Identity"的列传递值.

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

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

我遇到了同样的问题，交易解决了我的问题. TSQL和ADO或ADO.NET中有事务命令.
在所有这些中，您都必须执行以下操作:

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

还有一点是，事务具有多个隔离级别，而对于这种类型的问题，最清晰的隔离级别是Serializable.

欲了解更多信息，请阅读:
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 [ ^ ]

如果您对TSQL，ADO或ADO.NET感兴趣，那么我将为您提供代码.但是，我确定您已经明白了.

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.

您应该更改ID生成算法.
您必须考虑所有增量值.
您可以将所有已发布的ID记录在另一个表中，并从该表中发布ID.这样，您将只发布一次单个ID.
然后将它们仅插入那些成功完成所有字段的ID.

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.

这篇关于多实例访问同一数据库和主键冲突问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！