生产环境enq: TX - 行锁竞争引发ORA-00060死锁的解决方案咨询

生产环境中两个不同脚本的SQL语句并发执行时，频繁触发如下错误：

ORA-00060: deadlock detected while waiting for resource

涉及SQL语句 #

实际执行语句 #

UPDATE ITM.SHEL_CAP
SET ITM.SHEL_CAP.SEC_QTY = itm_rec.qty,
ITM.SHEL_CAP.ITMS_TOT_CNT = prim_shel_qty + itm_rec.qty
WHERE ITM.SHEL_CAP.ITM_ID = itm_rec.itm_id;


UPDATE SHEL_CAP
SET SEC_QTY = f_pres_stock,
ITMS_TOT_CNT = PRIM_QTY +f_pres_stock
WHERE ITEM_ID = f_item_id 
AND PRIM_LC_IND = 'Y'
AND ROWNUM = 1;

追踪文件记录的死锁语句 #

UPDATE ITM.SHEL_CAP
SET ITM.SHEL_CAP.SEC_QTY = :B2 , 
ITEM.SHEL_CAP.ITMS_TOT_CNT = :B3 + :B2 
WHERE ITM.SHEL_CAP.ITM_ID = :B1 


UPDATE SHEL_CAP
SET SEC_QTY = :B2 ,
ITMS_TOT_CNT = PRIM_QTY + :B2 
WHERE ITM_ID = :B1 
AND PRIM_LC_IND = 'Y' 
AND ROWNUM = 1

表索引信息 #

CREATE INDEX "ITEM"."XIF506SHEL_CAP" ON "ITM"."SHEL_CAP" ("ITM_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ITEM_INDX01" ;
--------------------------------------------------------
--  DDL for Index XPK_SHEL_CAP
--------------------------------------------------------

  CREATE UNIQUE INDEX "ITM"."XPK_SHEL_CAP" ON "ITEM"."SHEL_CAP" ("SHEL_POSTN_NUM", "SHEL_NUM", "SEC_NUM", "AI_NUM", "AI_LOC_CD") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ITEM_INDX01" ;

已确认问题为enq: TX - row lock contention，以下是具体规避调整方案：

死锁规避调整方案 #

1. 统一行锁定顺序，消除循环等待 #

• 第一个UPDATE通过ITM_ID批量锁定多行，第二个语句通过ITM_ID + PRIM_LC_IND = 'Y' + ROWNUM=1锁定单行，二者并发时易出现循环锁定不同行的情况。
• 调整更新逻辑，确保对同一ITM_ID下的行，始终按唯一索引XPK_SHEL_CAP的列顺序（SHEL_POSTN_NUM -> SHEL_NUM -> SEC_NUM -> AI_NUM -> AI_LOC_CD）锁定。可先查询目标行的主键值，再用主键更新，避免ROWNUM带来的非确定性锁定：

-- 先查询主键
SELECT SHEL_POSTN_NUM, SHEL_NUM, SEC_NUM, AI_NUM, AI_LOC_CD
INTO v_postn, v_shel, v_sec, v_ai, v_loc
FROM SHEL_CAP
WHERE ITEM_ID = f_item_id 
AND PRIM_LC_IND = 'Y'
AND ROWNUM = 1;

-- 用主键更新
UPDATE SHEL_CAP
SET SEC_QTY = f_pres_stock,
ITMS_TOT_CNT = PRIM_QTY + f_pres_stock
WHERE SHEL_POSTN_NUM = v_postn
AND SHEL_NUM = v_shel
AND SEC_NUM = v_sec
AND AI_NUM = v_ai
AND AI_LOC_CD = v_loc;

2. 优化执行计划，缩小锁竞争范围 #

• 针对第二个UPDATE的查询条件，创建复合索引CREATE INDEX IDX_SHEL_CAP_ITM_PRIM ON ITM.SHEL_CAP(ITM_ID, PRIM_LC_IND)，让数据库快速定位目标行，避免扫描额外行产生不必要的锁。
• 检查第一个UPDATE的执行计划，确保它使用XIF506SHEL_CAP索引，避免全表扫描锁定大量无关行。

3. 缩短事务锁持有时间 #

• 两个脚本的事务需尽可能短小，避免在UPDATE前后执行无关IO或业务逻辑，确保锁被快速释放。
• 若业务允许，将批量UPDATE拆分为小批次处理，减少单次锁定的行数，降低冲突概率。

4. 调整存储参数，缓解并发冲突 #

• 当前索引XIF506SHEL_CAP的INITRANS=2，高并发场景下可适当提高至4-8，增加数据块的初始事务槽数量，减少事务槽不足导致的锁竞争。
• 调整FREELISTS为与并发事务数匹配的值（如8），FREELIST GROUPS设为CPU核心数，减少数据块的竞争。

5. 优化并发控制逻辑 #

• 若业务允许，对同一ITM_ID的更新操作使用SELECT ... FOR UPDATE SKIP LOCKED，避免多个事务同时锁定同一组行。
• 明确划分两个脚本的更新范围，避免对同一ITM_ID下的行进行交叉更新。

死锁与数据库设计优化学习资源 #

• Oracle官方文档《Oracle Database Concepts》：详细讲解锁与事务的核心机制、TX锁原理及死锁检测逻辑。
• 《Oracle Performance Tuning Guide》：涵盖索引优化、事务设计、并发控制的实操方案。
• 《数据库系统概论》（萨师煊版）：深入理解事务ACID特性、锁机制及并发控制理论基础。
• Oracle MOS文档：搜索"ORA-00060 deadlock troubleshooting"，获取官方排查与解决案例。

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