IBM DB2存储过程DELETE语句阻塞超时问题及疑问

问题现象 #

在IBM DB2存储过程中执行MERGE语句时，当传入@action = 'I'时INSERT分支可正常执行，但传入@action = 'D'时，DELETE WITH NC分支会持续执行直至触发阻塞超时。将WITH NC替换为WITH UR后，DELETE语句恢复正常执行。

原代码（超时版本） #

SET TRANSACTION ISOLATION LEVEL UR; 

MERGE INTO LIBTMP.T1TMP A 
USING (SELECT @ACTION ACTION, @CLDOC CLDOC FROM SYSIBM.SYSDUMMY1) B
ON A.CLDOC = B.CLDOC

WHEN NOT MATCHED AND @ACTION = 'I' 
    THEN 
        INSERT (A.CLDOC, A.USER, A.IP, A.LOCATION, A.LANGUAGE, A.CHANNEL, A.TENANT, A.PROCESS, A.CRDATE)
        VALUES (@CLDOC, @USER, @IP, @LOCATION, @LANGUAGE, @CHANNEL, @TENANT, @PROCESS, CURRENT_TIMESTAMP)

WHEN MATCHED AND @ACTION = 'D' 
    THEN
        DELETE WITH NC;  

COMMIT;

修改后代码（正常执行版本） #

SET TRANSACTION ISOLATION LEVEL UR; 

MERGE INTO LIBTMP.T1TMP A 

USING (SELECT @ACTION ACTION, @CLDOC CLDOC FROM SYSIBM.SYSDUMMY1) B
ON A.CLDOC = B.CLDOC

WHEN NOT MATCHED AND @ACTION = 'I' 
    THEN 
        INSERT (A.CLDOC, A.USER, A.IP, A.LOCATION, A.LANGUAGE, A.CHANNEL, A.TENANT, A.PROCESS, A.CRDATE)
        VALUES (@CLDOC, @USER, @IP, @LOCATION, @LANGUAGE, @CHANNEL, @TENANT, @PROCESS, CURRENT_TIMESTAMP)

WHEN MATCHED AND @ACTION = 'D' 
    THEN
        DELETE WITH UR;  

COMMIT;

原因分析 #

核心差异在于WITH NC和WITH UR两个选项的锁行为规则，且MERGE分支中的WITH子句会覆盖会话级的隔离级别设置：

• WITH NC（No Commit）：
  这个选项会忽略会话级设置的UR隔离级别，强制对DELETE操作施加持久排他锁——锁会一直持有到整个事务执行COMMIT才释放。如果此时有其他并发事务正在访问匹配的行（比如读取、修改），就会触发锁等待，直到达到DB2的阻塞超时阈值，最终导致操作失败。

• WITH UR（Uncommitted Read）：
  这个选项遵循会话级的UR隔离级别策略，执行DELETE时会采用更宽松的锁机制：锁的持有时间被大幅缩短（通常在完成行级修改后立即释放，而非等到事务提交），同时允许其他事务进行脏读，从根源上减少了锁竞争的概率，因此不会出现长时间阻塞超时的情况。

简单来说，WITH NC强制延长了锁的持有周期，即便会话级设置了低隔离级也无效，导致并发场景下锁冲突超时；而WITH UR遵循低隔离级的锁策略，避免了长时间锁持有，解决了超时问题。

内容的提问来源于stack exchange，提问作者triby