Oracle12c:对不相交的行集进行并行选择以更新NOWAIT导致ORA-00054:资源繁忙

Question

我在几个并发进程中运行以下查询：

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 (...)列表中都有自己的一组值，并且这些集合保证是不相交的。

不确定是否重要，但FK到表D也是不相交的--无论是在更新之前还是之后都是这样做的。

不过，我偶尔也会得到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行加载到表C后才能重现这个问题。

Answer 1

好吧，我找到了根本原因。这是个国际交易日志的锁&等等。

更多信息在这里：国际交易日志等待神秘

诀窍是，这三行都是几乎已满的同一物理块的一部分，这些行的锁也存储在物理块中。第三个锁没有足够的空间，因此第三个事务将等待。