达梦数据库插单行语句耗时43秒的问题分析

巡检的时候发现一条单行的语句居然耗时46秒：

1. insert语句于2022-03-03 16:45:54.089发生了回退。

2022-03-03 16:45:11.043 (EP[0] sess:0x7ef114a1a680 thrd:1746386 user:SDS trxid:738371510 stmt:0x7ef114a4de78 appname: ip:::ffff:22.21.64.13) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, 'SDS00000202203030000000000716724'), (1, CHAR, '20211119'), (2, VARCHAR2, 'SRS00000202203030000000000017837'), (3, VARCHAR2, 'GSRS00000202203030000000000017837'), (4, VARCHAR2, '601'), (5, CHAR, '0'), (6, CHAR, '0'), (7, VARCHAR2, '01'), (8, VARCHAR2, '03'), (9, VARCHAR2, 'SRS'), (10, CHAR, '1')}

...

2022-03-03 16:45:54.089 (EP[0] sess:0x7ef114a1a680 thrd:1746386 user:SDS trxid:738371510 stmt:0x7ef114a4de78 appname: ip:::ffff:22.21.64.13) [ERR(-6602)]: insert into T_SDS_ACCT

DUP_VAL_ON_INDEX -6602 违反唯一性约束

该表的唯一索引：

CREATE UNIQUE  INDEX "UDX_SDS_ACCT_01" ON "SDS"."T_SDS_ACCT"("REQUEST_NO" ASC,"SDS_DATA_SYS_SOURCE" ASC) STORAGE(ON "TS_SDS_DATA", CLUSTERBTR) ;

事务738371510视图传入参数：

REQUEST_NO: SRS00000202203030000000000017837

SDS_DATA_SYS_SOURCE: SRS

1. 与之发生冲突的事务为事务738371179，它插入了相同的数据后，直到2022-03-03 16:45:54.088才提交。中途session 0x7ef125232960还执行了其他语句，并非完全挂起，因此应该是长事务所致。

2022-03-03 16:45:10.381 (EP[0] sess:0x7ef125232960 thrd:1746382 user:SDS trxid:738371179 stmt:0x7ef125266158 appname: ip:::ffff:22.21.64.13) PARAMS(SEQNO, TYPE, DATA)={(0, VARCHAR2, 'SDS00000202203030000000000716723'), (1, CHAR, '20211119'), (2, VARCHAR2, 'SRS00000202203030000000000017837'), (3, VARCHAR2, 'GSRS00000202203030000000000017837'), (4, VARCHAR2, '601'), (5, CHAR, '0'), (6, CHAR, '0'), (7, VARCHAR2, '01'), (8, VARCHAR2, '03'), (9, VARCHAR2, 'SRS'), (10, CHAR, '1')}

...

2022-03-03 16:45:54.087 (EP[0] sess:0x7ef125232960 thrd:1746382 user:SDS trxid:738371179 stmt:0x7ef125266158 appname: ip:::ffff:22.21.64.13)  FREE STMT^M

2022-03-03 16:45:54.088 (EP[0] sess:0x7ef125232960 thrd:1746382 user:SDS trxid:738371179 stmt:NULL appname:)  MSG: COMMIT^M

2022-03-03 16:45:54.088 (EP[0] sess:0x7ef125232960 thrd:1746382 user:SDS trxid:738371179 stmt:NULL appname:)  TRX: COMMIT^M

问题总结：

1. Session 0x7ef125232960，发起了事务738371179。于2022-03-03 16:45:10.381往T_SDS_ACCT插入了一行数据后，直到2022-03-03 16:45:54.088才完成事务并提交。其中插入的数据的REQUEST_NO为SRS00000202203030000000000017837、SDS_DATA_SYS_SOURCE为 SRS。
2. Session 0x7ef114a1a680，发起了事务738371510。于2022-03-03 16:45:11.043往T_SDS_ACCT尝试插数，REQUEST_NO为SRS00000202203030000000000017837、SDS_DATA_SYS_SOURCE为 SRS，与事务738371179一致。这违反了UDX_SDS_ACCT_01唯一索引引入的唯一约束。
3. 事务738371179完成后，事务738371510才发生了回滚。这也导致insert T_SDS_ACCT执行时间达到了43秒。