一、问题

现场应用适配时，每轮跑批均有大量死锁报错，经过确认，应用中未配置出错重试机制，死锁可能会造成数据不一致。分析sqllog发现是并发delete传入了相同参数导致的。

二、问题分析

1、V$DEADLOCK_HISTORY

select *from SYS.V$DEADLOCK_HISTORY where trx_id=37740122690;

DEADLOCK_CYCLE=self -> (37740122696, 280883908639200) -> self

2、dmsql文件

V$DEADLOCK_HISTORY中显示死锁时间为2026-06-09 23:39:46.677089，对应时间的sqllog文件为dmsql_GRP1_RT_01_20260609_233929.log 。
2026-06-09 23:39:45.681，事务ID为37740122690的会话(用户YSSFA1,IP10.56.0.94)试图获取表ID为13782的TID锁(排他锁，mode:X)，但被另一个事务37740122705阻塞，已等待825毫秒。

2026-06-09 23:39:44.741 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:0 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) PREPARE
2026-06-09 23:39:44.741 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:0 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) [ORA]: delete  from T_D_OD_ZDJSMX where  QSRQ = ? AND  ZQZH = ? AND  YWLX = ? AND  JLLX = ? AND  QSBZ = ? AND  ZQDM1 = ? AND  ZQDM2 = ? AND  XWH1 = ? AND  JYFS = ?
2026-06-09 23:39:44.741 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:0 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) GET SQL ALL PLN:  delete  from T_D_OD_ZDJSMX where  QSRQ = ? AND  ZQZH = ? AND  YWLX = ? AND  JLLX = ? AND  QSBZ = ? AND  ZQDM1 = ? AND  ZQDM2 = ? AND  XWH1 = ? AND  JYFS = ?
1   #DELETE : [0, 0, 0]; table(T_D_OD_ZDJSMX), type(select), mpp_opt(0), hp_opt(0)
2     #PRJT2: [1, 1, 444]; exp_num(1), is_atom(FALSE); INFO_BITS(0)
3       #SSEK2: [1, 1, 444]; scan_type(ASC), IDX_DEL_XC_D_OD_ZDJSMX(T_D_OD_ZDJSMX), scan_range[(exp_param(no:0),exp_param(no:1),exp_param(no:2),exp_param(no:3),exp_param(no:4),exp_param(no:5),exp_param(no:6),exp_param(no:7),exp_param(no:8)),(exp_param(no:0),exp_param(no:1),exp_param(no:2),exp_param(no:3),exp_param(no:4),exp_param(no:5),exp_param(no:6),exp_param(no:7),exp_param(no:8))], is_global(0)
2026-06-09 23:39:44.741 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:NULL appname: ip:::ffff:10.56.0.94) TRX: START
2026-06-09 23:39:44.851 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) EXECUTE
2026-06-09 23:39:44.851 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) DLCK used time:4(us)
2026-06-09 23:39:44.851 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:NULL appname: ip:::ffff:10.56.0.94) trx[37740122690] alloc pseg page[0, 3290779], page_lsn[480784812029], n_pages[1]
2026-06-09 23:39:44.851 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '603813'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}
2026-06-09 23:39:45.681 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:NULL appname: ip:::ffff:10.56.0.94) trx[37740122690] LOCK_TID (mode:X, table id:13783) wait for 1 trxs, trx[37740122705] used time:825(ms)
2026-06-09 23:39:45.691 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) DLCK used time:25(us)
2026-06-09 23:39:45.701 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '603687'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}
2026-06-09 23:39:45.711 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '603022'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}
2026-06-09 23:39:45.721 (EP[0] sess:0xff772ac32bc0 thrd:2987498 user:YSSFA1 trxid:37740122690 stmt:0xff772ac7b820 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '601600'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}

2026-06-09 23:39:45.681事务37740122690等待事务37740122705。
2026-06-09 23:39:45.685事务37740122705等待事务37740122690。相差4ms。
两者相互等待，形成了死锁。检测到死锁后自动回滚事务37740122705，并记录错误码-6403的日志。

2026-06-09 23:39:44.755 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:0 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) PREPARE
2026-06-09 23:39:44.755 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:0 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) [ORA]: delete  from T_D_OD_ZDJSMX where  QSRQ = ? AND  ZQZH = ? AND  YWLX = ? AND  JLLX = ? AND  QSBZ = ? AND  ZQDM1 = ? AND  ZQDM2 = ? AND  XWH1 = ? AND  JYFS = ?
2026-06-09 23:39:44.755 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:0 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) GET SQL ALL PLN:  delete  from T_D_OD_ZDJSMX where  QSRQ = ? AND  ZQZH = ? AND  YWLX = ? AND  JLLX = ? AND  QSBZ = ? AND  ZQDM1 = ? AND  ZQDM2 = ? AND  XWH1 = ? AND  JYFS = ?

1   #DELETE : [0, 0, 0]; table(T_D_OD_ZDJSMX), type(select), mpp_opt(0), hp_opt(0)
2     #PRJT2: [1, 1, 444]; exp_num(1), is_atom(FALSE); INFO_BITS(0)
3       #SSEK2: [1, 1, 444]; scan_type(ASC), IDX_DEL_XC_D_OD_ZDJSMX(T_D_OD_ZDJSMX), scan_range[(exp_param(no:0),exp_param(no:1),exp_param(no:2),exp_param(no:3),exp_param(no:4),exp_param(no:5),exp_param(no:6),exp_param(no:7),exp_param(no:8)),(exp_param(no:0),exp_param(no:1),exp_param(no:2),exp_param(no:3),exp_param(no:4),exp_param(no:5),exp_param(no:6),exp_param(no:7),exp_param(no:8))], is_global(0)
2026-06-09 23:39:44.755 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:NULL appname: ip:::ffff:10.56.0.94) TRX: START
2026-06-09 23:39:44.835 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) EXECUTE
2026-06-09 23:39:44.835 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) DLCK used time:4(us)
2026-06-09 23:39:44.835 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:NULL appname: ip:::ffff:10.56.0.94) trx[37740122705] alloc pseg page[0, 651758], page_lsn[480784808921], n_pages[1]
2026-06-09 23:39:44.845 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '601600'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}
2026-06-09 23:39:44.855 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '603687'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}
2026-06-09 23:39:44.865 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '603022'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}
2026-06-09 23:39:45.685 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:NULL appname: ip:::ffff:10.56.0.94) trx[37740122705] LOCK_TID (mode:X, table id:13783) wait for 1 trxs, trx[37740122690] used time:820(ms)
2026-06-09 23:39:45.685 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, '20240903'), (1, VARCHAR2, 'B886696531'), (2, VARCHAR2, '001'), (3, VARCHAR2, '001'), (4, VARCHAR2, '060'), (5, VARCHAR2, '603813'), (6, VARCHAR2, ' '), (7, VARCHAR2, '28452'), (8, VARCHAR2, '001')}
2026-06-09 23:39:45.685 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:0xff7b03ea6050 appname: ip:::ffff:10.56.0.94) [ERR(-6403)]: delete  from T_D_OD_ZDJSMX where  QSRQ = ? AND  ZQZH = ? AND  YWLX = ? AND  JLLX = ? AND  QSBZ = ? AND  ZQDM1 = ? AND  ZQDM2 = ? AND  XWH1 = ? AND  JYFS = ? EXECTIME: 856(ms) ROWCOUNT: 0(rows) EXEC_ID: 67197578.^
2026-06-09 23:39:45.695 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:NULL appname: ip:::ffff:10.56.0.94) MSG: COMMIT
2026-06-09 23:39:45.695 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:37740122705 stmt:NULL appname: ip:::ffff:10.56.0.94) TRX: COMMIT
2026-06-09 23:39:45.695 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:0 stmt:NULL appname: ip:::ffff:10.56.0.94) trx[37740122705]: pseg_page_free_for_insert_only_trx free pseg page (0, 651758), page_lsn = 480784884630
2026-06-09 23:39:45.695 (EP[0] sess:0xff7b03e5d3f0 thrd:2986323 user:YSSFA1 trxid:0 stmt:NULL appname: ip:::ffff:10.56.0.94) TRX: COMMIT LSN[480784884618

死锁涉及的两个事务
事务 A（trxid: 37740122690）：会话 sess:0xff772ac32bc0，用户 YSSFA1，开始时间 23:39:44.741。
事务 B（trxid: 37740122705）：会话 sess:0xff7b03e5d3f0，用户 YSSFA1，开始时间 23:39:44.755。
执行的 SQL 语句
两个事务均执行相同的 DELETE 语句（使用 绑定参数）：
delete from T_D_OD_ZDJSMX where QSRQ = ? AND ZQZH = ? AND YWLX = ? AND JLLX = ? AND QSBZ = ? AND ZQDM1 = ? AND ZQDM2 = ? AND XWH1 = ? AND JYFS = ?
执行计划均使用索引 IDX_DEL_XC_D_OD_ZDJSMX 进行等值范围扫描（SSEK2）。
死锁原因分析
两个事务均对 表 ID 13783（即 T_D_OD_ZDJSMX）请求 行级排他锁（X 锁）。
事务 A 先锁定了通过参数 603813、B886696531、28452 对应的记录，事务 B 也锁定了通过参数 603813、B886696531、28452 对应的记录。
当双方继续请求对方已持有的行锁时，形成了循环等待：
事务 A 等待事务 B 释放某行锁
事务 B 等待事务 A 释放某行锁（在删除 603813 时产生冲突）
达梦数据库的死锁检测机制在 23:39:45.685 自动发现该循环，并选择 事务 B 作为牺牲者（ERR(-6403)），回滚了事务 B 中导致死锁的那条 DELETE 语句（参数 603813）。事务 B 的其他操作正常提交。

优化建议

1、并发delete时避免全表扫描导致锁范围扩大。
2、避免多个事务使用相同参数（如同一个主键值或唯一索引值）同时删除同一张表中的数据。
3、应用层添加重试机制，捕获到死锁错误后，回滚当前事务，等待一段时间后重新尝试执行该事务。
4、DEADLOCK_CHECK_INTERVAL参数主要用于控制数据库检测死锁的间隔时间，单纯放大检测间隔只会延长死锁被发现和处理的时间，反而可能加剧系统阻塞。