>我在几个并发进程中运行以下查询:
SELECT A_ID, B_ID, C_ID, C_STATUS, D_ID
FROM A NATURAL JOIN B NATURAL JOIN C
WHERE A_ID IN (...)
FOR UPDATE OF C_STATUS, D_ID NOWAIT;
- 表A:A_ID(PK)
- 表B:B_ID(PK),A_ID(FK)
- 表C:C_ID(PK)、C_STATUS、B_ID(FK)、D_ID(FK)
- 表D:D_ID(PK)
每个进程在IN (...)
列表中都有自己的一组值,这些值集保证是不相交的。
不确定这是否重要,但表 D 的 FK 也是不相交的——无论是在更新之前还是之后,稍后都会完成。
但是,我偶尔会得到ORA-00054: resource busy
,我将其读作"两个进程试图在NOWAIT语句中锁定同一行以进行更新"。
在我有FOR UPDATE OF C_STATUS, D_ID NOWAIT
子句之前,并行查询正在等待其他查询完成(等待释放锁),并且在稍后尝试更新表 C 的相应行时很少遇到死锁:
死锁图:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000F001F-0000F3B5-.. 39 1414 X 75 835 S
TX-0009000B-000124A5-.. 75 835 X 39 1414 S
锁定的行来自表 C。但是,在检查阻塞行上的调试日志和 rowid 时,我发现至少有一个进程不应该接触该行。
知道为什么在多个进程更新不相交的行时资源繁忙/死锁吗?为什么 Oracle 锁定了实际上未使用的行?
编辑:我能够将问题缩小到这个bash脚本:
#!/bin/bash
sub(){
sqlplus -S "$DB_ACCESS" << EOF
exec dbms_lock.sleep($2);
select '$1:'||C_ID from C where C_ID in ($3)
for update nowait;
exec dbms_lock.sleep(2);
rollback;
EOF
}
sub 1 0.1 1510223
sub 2 0.3 1510600
sub 3 0.5 1512100
wait
你可以看到C_IDs是不同的,我检查了父母B_ID和祖父母A_ID对于这三个人来说也是不同的。
我得到以下输出:
PL/SQL procedure successfully completed.
'1:'||C_ID
------------------------------------------
1:1510223
PL/SQL procedure successfully completed.
Rollback complete.
PL/SQL procedure successfully completed.
'2:'||C_ID
------------------------------------------
2:1510600
PL/SQL procedure successfully completed.
Rollback complete.
PL/SQL procedure successfully completed.
select '3:'||C_ID from C where C_ID in (1512100)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
PL/SQL procedure successfully completed.
Rollback complete.
我希望我能提供示例数据,但我只能在将 ~1000000 行加载到 Table C 中后重现此问题。
好的,我找到了根本原因。这是一个ITL锁和等待。
更多信息在这里:ITL等待揭开神秘面纱
诀窍在于,这三行都是几乎已满的同一物理块的一部分,并且这些行的锁也存储在物理块中。没有足够的空间容纳第三个锁,因此第三个事务将等待。