oracle自主事务造成的死锁

自主事务->single resource的死锁，可以通过查看trace看是否是一个资源会话造成的死锁，如果是，那就是自主事务的原因。

多数死锁都是两个资源争用：

自主事务的是一个TX-资源在争用导致的，基本上一个资源争用导致的死锁，就是自主事务引起的：

一般来说有自主事务的process会有多个session，自己一个，自主事务单独创建一个，然后去争夺资源，导致死锁。
实验过程：
创建一个自主事务的存储过程p2

create or replace procedure p2 is 
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  update t2 set object_name=object_name||'1' where owner='SCOTT';
  commit;
END;
/
1
2
3
4
5
6
7

SQL> update t2 set object_name=object_name||'1' where owner='XDB';

1168 rows updated.
1
2
3

SQL> select distinct object_name from t2 where owner='XDB' and rownum<10;

OBJECT_NAME
--------------------------------------------------------------------------------
XDB$IMPORT_TT_INFO1
SYS_LOB0000069049C00005$$1
SYS_IL0000069044C00001$$1
XDB$H_INDEX1
XDB$RCLIST_V1
XDB$ROOT_INFO1
XDB$XDB_READY1
SYS_IL0000069049C00005$$1
SYS_LOB0000069044C00001$$1

9 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL> exec p2;

PL/SQL procedure successfully completed.
1
2
3

按道理来说，在p2已经commit了，接下来的rollback是不生效的才对

SQL> rollback;

Rollback complete.
1
2
3

但是确实生效了，说明自主事务是自己单独一个会话。

SQL> select distinct object_name from t2 where owner='XDB' and rownum<10;

OBJECT_NAME
--------------------------------------------------------------------------------
XDB$IMPORT_TT_INFO
XDB$ROOT_INFO
XDB$XDB_READY
SYS_LOB0000069044C00001$$
XDB$RCLIST_V
XDB$H_INDEX
SYS_IL0000069044C00001$$
SYS_IL0000069049C00005$$
SYS_LOB0000069049C00005$$

9 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

下面开始演示自主事务造成的死锁情况(就是因为自主事务自己也生成了一个session，导致TX-争用，所以必须牺牲掉自主事务，让前面的语句可以执行成功，不然极端来说，没有死锁机制的话，就会一直hang在那里，
但其实前面的SQL做了commit的话，也不会产生死锁)：

SQL> update t2 set object_name=object_name||'1' where owner='SCOTT';

6 rows updated.
1
2
3

SQL> exec p2;
BEGIN p2; END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TEST.P2", line 4
ORA-06512: at line 1
1
2
3
4
5
6
7
8

查看trace文件：

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0005001d-00000369        19     191     X             19     191           X
1
2
3
4

这就很色，持有者和等待者都是一样的。但我们去看SO就会发现不同之处，一个process底下有俩session，两个SO的owner不一样，同样的TX-资源被两个SO会话持有，因此导致了资源征用
持有者mode: X的SO的owner是 0xf38ac1b0

SO: 0xef3ee108, type: 56, owner: 0xf38ac1b0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
       proc=0xf351da98, name=transaction, file=ktccts.h LINE:410, pg=0
      (trans) flg = 0x00000e03, flg2 = 0x00014000, flg3 = 0x00000000, prx = (nil), ros = 2147483647, crtses=0xf38ac1b0
      flg  = 0x00000e03: ALC TRN VUS VID CHG
      flg2 = 0x00014000: IMU IMP
      flg3 = 0x00000000:
      bsn = 0x26ba bndsn = 0x26bd spn = 0x26c8
      efd = 5 rfd = 0 DID:
      file:kta.c lineno:1670
      parent xid: 0x0000.000.00000000
      env [0xef3ee520]: (scn: 0x0000.000e4b24  xid: 0x0005.01d.00000369  uba: 0x00c0103e.00b6.06  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.000e4b24  hi-scn: 0x0000.00000000  ma-scn: 0x0000.00000000  flg: 0x00000000)
      cev: (spc = 7518  arsp = 0xef4639e0  ubkds (ubk:tsn: 2 rdba: 0x00c0103e flag:0x8 hdl:(nil) addr:(nil))  useg tsn: 2 rdba: 0x00c000c0
            hwm uba: 0x00c0103e.00b6.06  col uba: 0x00000000.0000.00
            num bl: 1 bk list: 0xef33b7f0)
            cr opc: 0x0 spc: 7518 uba: 0x00c0103e.00b6.06
      Begin scn:0x0000.000e4ae4 uba:0x00c0103e.00b6.01 ts:1658744536[07/25/2022 18:22:16]
      Undo blks: 1 recs: 6
      ccbstg: 0x00000000
      (enqueue) TX-0005001D-00000369    DID: 0001-0013-00000013
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
      mode: X, lock_flag: 0x0, lock: 0xef3ee180, res: 0xf3e01160
      own: 0xf38ac1b0, sess: 0xf38ac1b0, proc: 0xf351da98, prv: 0xf3e01170
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

请求者req: X的SO的owner是 0xf38ff2e8

SO: 0xf3902fc0, type: 3, owner: 0xf38ff2e8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
       proc=0xf351da98, name=call, file=ksu.h LINE:12725, pg=0
      (call) sess: cur f38ac1b0, rec 0, usr f38ac1b0; flg:0 fl2:1; depth:1
      svpt(xcb:(nil) sptn:0x26cb uba: 0x00000000.0000.00)
      ksudlc FALSE at location: 0
        ----------------------------------------
        SO: 0xf3d41cb8, type: 8, owner: 0xf3902fc0, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
         proc=0xf351da98, name=enqueue, file=ksq1.h LINE:380, pg=0
        (enqueue) TX-0005001D-00000369  DID: 0001-0013-00000013
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
        req: X, lock_flag: 0x10, lock: 0xf3d41d10, res: 0xf3e01160
        own: 0xf38ac1b0, sess: 0xf38ac1b0, proc: 0xf351da98, prv: 0xf3e01180
1
2
3
4
5
6
7
8
9
10
11
12

只要把自主事务的那个存储过程中自主事务给注释了，就不会造成死锁了，因为他们这样就算是一个事务了。