将键并发插入表中
可能是一个微不足道的问题，但我想得到最好的解决方案。
问题：
我有两个或多个工作人员将键插入一个或多个表中。当两个或更多工作人员尝试同时将相同的密钥插入这些密钥表之一时，就会出现问题。 典型问题。
- 如果键存在（SELECT），则工作人员 A 读取表。没有钥匙。
- 如果某个键存在，则工作人员 B 读取该表 (SELECT)。没有钥匙。
- 工人 A 插入钥匙。
- 工人B插入钥匙。
- 工人 A 提交。
- 工作人员 B 提交。由于违反唯一约束而引发异常
键表是简单的对。第一列是自动增量整数，第二列是 varchar 键。
解决此类并发问题的最佳方案是什么？我相信这是一个普遍的问题。一种肯定的方法是处理抛出的异常，但不知何故，我不认为这是解决这个问题的最佳方法。
我使用的数据库是 Firebird 2.5
编辑：
一些附加信息可以使事情变得清晰。
- 客户端同步不是一个好方法，因为插入来自不同的进程（工作进程）。有一天我可能会让工作人员跨不同的机器，所以即使是互斥体也是不行的。
- 此类表的主键和第一列是自动增量字段。那里没问题。 varchar 字段是问题所在，因为它是客户端插入的内容。
典型的此类表是用户表。例如：
1 2056 2 1044 3 1896 4 5966 ...
每个工作人员检查用户“xxxx”是否存在，如果不存在则插入它。
编辑2：
仅供参考，如果有人会走同样的路线。 IB/FB 返回一对错误代码（我正在使用 InterBase Express 组件）。检查重复值违规如下所示：
except
on E: EIBInterBaseError do
begin
if (E.SQLCode = -803) and (E.IBErrorCode = 335544349) then
begin
FKeysConnection.IBT.Rollback;
EnteredKeys := False;
end;
end;
end;
Probably a trivial question, but I want to get the best possible solution.
Problem:
I have two or more workers that insert keys into one or more tables. The problem arises when two or more workers try to insert the same key into one of those key tables at the same time.
Typical problem.
- Worker A reads the table if a key exists (SELECT). There is no key.
- Worker B reads the table if a key exists (SELECT). There is no key.
- Worker A inserts the key.
- Worker B inserts the key.
- Worker A commits.
- Worker B commits. Exception is throws as unique constraint is violated
The key tables are simple pairs. First column is autoincrement integer and the second is varchar key.
What is the best solution to such a concurrency problem? I believe it is a common problem. One way for sure is to handle the exceptions thrown, but somehow I don't believe this is the best way to tackle this.
The database I use is Firebird 2.5
EDIT:
Some additional info to make things clear.
- Client side synchronization is not a good approach, because the inserts come from different processes (workers). And I could have workers across different machines someday, so even mutexes are a no-go.
- The primary key and the first columns of such a table is autoincrement field. No problem there. The varchar field is the problem as it is something that the client inserts.
Typical such table is a table of users. For instance:
1 2056 2 1044 3 1896 4 5966 ...
Each worker check if user "xxxx" exists and if not inserts it.
EDIT 2:
Just for the reference if somebody will go the same route. IB/FB return pair of error codes (I am using InterBase Express components). Checking for duplicate value violation look like this:
except
on E: EIBInterBaseError do
begin
if (E.SQLCode = -803) and (E.IBErrorCode = 335544349) then
begin
FKeysConnection.IBT.Rollback;
EnteredKeys := False;
end;
end;
end;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
第一个选择——不要这样做。
不要这样做；除非 WORKERS 正在做大量的工作（我们谈论的是计算机，因此每条记录需要 1 秒才算“大量的工作”），否则只需使用单个线程；更好的是，在存储过程中完成所有工作，您会对不通过任何协议将数据传输到应用程序中所获得的加速感到惊讶。
第二个选项 - 使用队列
确保您的工作线程不会全部在同一 ID 上工作。设置一个队列，将所有需要处理的 ID 推入该队列，让每个工作线程从该队列中出列一个 ID。这样您就可以保证不会有两个工作人员同时处理同一条记录。如果您的工作人员不属于同一流程，这可能很难实施。
最后的手段
设置一个基于数据库的“保留”系统，以便工作线程可以将一个键标记为“正在进行的工作”，这样就不会有两个工作线程在同一个键上工作。我会建立一个像这样的表：
您的每个工作人员都会使用短事务来处理该表：选择一个候选键，该键不在 KEY_RESERVATIONS 表中。尝试插入。失败的？尝试使用其他 KEY。定期删除具有旧 RESERVED_UNTIL 时间戳的所有保留密钥。确保使用 KEY_RESERVATIONS 的事务尽可能短，以便两个线程同时尝试保留相同的密钥会很快失败。
First option - don't do it.
Don't do it; Unless the WORKERS are doing extraordinary amounts of work (we're talking about computers, so requiring 1 second per record qualifies as "extraordinary amount of work"), just use a single thread; Even better, do all the work in a stored procedure, you'd be amazed by the speedup gained by not transporting data over whatever protocol into your app.
Second option - Use a Queue
Make sure your worker threads don't all work on the same ID. Set up a Queue, push all the ID's that need processing into that queue, have each working thread Dequeue an ID from that Queue. This way you're guaranteed no two workers work on the same record at the same time. This might be difficult to implement if your workers are not all part of the same process.
Last resort
Set up an DB-based "Reservation" system so an Worker Thread can mark a Key for "work in process" so no two workers would work on the same Key. I'd set up a table like this:
Each of your workers would use short transactions to work on that table: Select a candidate Key, one that's not in the KEY_RESERVATIONS table. Try to INSERT. Failed? Try an other KEY. Periodically delete all reserved key with old RESERVED_UNTIL timestamps. Make sure the transactions for working with KEY_RESERVATIONS are as short as possible, so that two threads both trying to reserve the same key at the same time would fail quickly.
这是您必须在乐观（或无）锁定方案中处理的问题。
避免这种情况的一种方法是在表上围绕整个选择、插入、提交序列放置悲观锁。
但是，这意味着您将不得不处理无法访问表的问题（处理表锁定异常）。
如果工作人员指的是同一应用程序实例中的线程而不是不同用户（应用程序实例）中的线程，那么您将需要线程同步，就像 kubal5003 在选择-插入-提交序列周围所说的那样。
如果您有多个用户/应用程序实例，每个实例都有多个线程，则需要两者的组合。
This is what you have to deal with in an optimistic (or no-) locking scheme.
One way to avoid it is to put a pessimistic lock on the table around the whole select, insert, commit sequence.
However, that means you will have to deal with not being able to access the table (handle table-locked exceptions).
If by workers you mean threads in the same application instance instead of different users (application instances), you will need thread synchronization like kubal5003 says around the select-insert-commit sequence.
A combination of the two is needed if you have multiple users/application instances each with multiple threads.
同步你的线程，使其不可能插入相同的值或使用数据库端密钥生成方法（我不知道Firebird，所以我什至不知道它是否存在，例如，在MsSQL Server中也有身份列或GUID解决问题，因为不太可能生成两个相同的）
Synchronize your threads to make it impossible to insert the same value or use a db side key generation method (I don't know Firebird so I don't even know if it's there, eg. in MsSQL Server there is identity column or GUIDs also solve the problem because it's unlikely to generate two identical ones)
如果可能存在重复项，则不应依赖客户端生成唯一密钥。
使用触发器和生成器（也许在存储过程的帮助下）创建始终唯一的键。
有关 Firebird 中正确的 autoinc 实现的更多信息，请访问：http://www.firebirdfaq.org/faq29/
You should not rely the client to generate the unique key, if there's possibility for duplicates.
Use triggers and generators (maybe with help of stored procedure) to create always unique keys.
More information about proper autoinc implementation in Firebird here: http://www.firebirdfaq.org/faq29/
对于 Firebird，您可以使用以下语句：
这是文档。
更新：上面的语句将避免异常并导致每个语句都成功。但是，如果有许多重复的键值，也会导致许多不必要的更新。
这可以通过另一种方法来避免：只需在客户端处理唯一约束异常并忽略它。详细信息取决于您使用哪个 Delphi 库来处理 Firebird，但应该可以检查服务器返回的 SQLCode，并仅忽略唯一约束违规的特定情况。
With Firebird you can use the following statement:
BEFORE INSERTtrigger which will generate the MY_ID if a NULL value is being inserted.Here is the documentation.
Update: The above statement will avoid exceptions and cause every statement to succeed. However, in case of many duplicate key values it will also cause many unnecessary updates.
This can be avoided by another approach: just handle the unique constraint exception on the client and ignore it. The details depend on which Delphi library you're using to work with Firebird but it should be possible to examine the SQLCode returned by the server and ignore only the specific case of unique constraint violation.
我不知道这样的东西在 Firebird 中是否可用，但在 SQL Server 中你可以在插入密钥时进行检查。
I do not know if something like this is avalible in Firebird but in SQL Server you can check when inserting the key.