英文:
Intentionally Recreating a Multithreading SQL Error
问题
所以我有一个多线程的批处理器，它接收一些值并将它们插入到Oracle数据库中。我收到报告说这个处理器正在记录“唯一约束”错误。
这是处理器用于插入的查询：
INSERT INTO TABLE_T
(VALUE_A_PK, VALUE_B_PK, VALUE_C, VALUE_D, VALUE_E, VALUE_F, VALUE_G, VALUE_H, VALUE_I, VALUE_J)
SELECT ?,?,?,?,?,?,?,?,?,? FROM DUAL
WHERE NOT EXISTS
(SELECT 1 FROM TABLE_T WHERE
VALUE_A_PK= ? AND VALUE_B_PK = ?);
我知道修复这个问题的方法，但首先我需要重新创建这个错误，以证明我实际上已经修复了它。
尽管我将多个重复的记录提供给处理器，但我无法手动让错误重新出现。
在我看来，这几乎可以肯定是一个多线程问题，多个线程正在同时尝试插入新记录。
有可靠的方法可以强制多个线程在完全相同的时间执行吗？如果我能做到这一点，似乎我应该能够让错误重新出现。
英文:
So I have a batch processor which is multithreaded that takes some values and inserts them into an Oracle DB. I'm receiving reports that this processor is logging Unique Constraint errors.
This is the query that the processor is using to insert:
INSERT INTO TABLE_T
(VALUE_A_PK, VALUE_B_PK, VALUE_C, VALUE_D, VALUE_E, VALUE_F, VALUE_G, VALUE_H, VALUE_I, VALUE_J)
SELECT ?,?,?,?,?,?,?,?,?,? FROM DUAL
WHERE NOT EXISTS
(SELECT 1 FROM TABLE_T WHERE
VALUE_A_PK= ? AND VALUE_B_PK = ?);
I know of ways to fix this, but first I need to recreate the error to prove that I've actually fixed it.
Despite feeding multiple duplicate records to the processor, I cannot manually get the error to resurface.
It seems almost certain to me that this is a multithreading issue where multiple threads are attempting to insert a new record at the same time.
Is there a reliable way to force more than one thread to execute at exactly the same time? If I could do that, then it seems like I should be able to get the error pop back up.
答案1
得分: 0
不能以*精确*相同的时间在两个会话中执行两个语句。诸如DBMS_SCHEDULER之类的工具可能会在一秒内可靠地启动两个进程，但要模拟单行插入的主键错误，您需要更高的精度。
可能更容易在两个单独的会话中循环运行该语句，然后只需等待其中一个失败即可。我以前多次这样做过，以重现间歇性问题。
在两个或更多会话中运行此伪代码，并等待错误发生：
begin
for i in 1 .. 1000000 loop
INSERT INTO TABLE_T ...
end loop;
end;
/
英文:
There's no way to fire two statements in two sessions at the exact same time. Tools like DBMS_SCHEDULER could perhaps reliably start two processes within a second, but to imitate primary key errors for one-row inserts you'll need a much higher precision.
It might be easier to run the statement in two separate sessions in a loop, and just wait until one of them fails. I've done this several times before to reproduce intermittent problems.
Run this pseudo-code in two or more sessions and wait for an error:
begin
for i in 1 .. 1000000 loop
INSERT INTO TABLE_T ...
end loop;
end;
/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论