Oracle 索引合并清理等待enq: TX行锁争用

嗨，汤姆斯!我注意到我们的12.1.0.2异步全局索引维护作业 (PMO_DEFERRED_GIDX_MAINT_JOB) 会不时挂起，并且我将其跟踪到长时间运行的插入，导致作业等待enq: TX行锁定争用。重度分区表有一个主键，但是，新插入的行永远不会匹配正在清理的任何孤立条目 (我想索引会知道这一点)，所以我的问题是: 这是预期的行为？我的猜测是TX锁阻止了coalesce leaf shuffle部分，但希望您能以这种方式解释它吗？非常感谢!

我已经将一个简化的测试用例链接到LiveSQL，但是由于LiveSQL不提供对v $ 视图的访问，因此将结果粘贴在这里。

SQL> @index_contention_1row_unique.sql
SQL> 
SQL> drop table mylist;

Table dropped.

SQL> 
SQL> create table mylist (id number, n number)
  2  partition by list (n)
  3  (
  4    partition one values (1),
  5    partition two values (2)
  6  );

Table created.

SQL> 
SQL> REM alter table mylist add constraint mylist_pk primary key(id);
SQL> create unique index mylist_uniq on mylist(id);

Index created.

SQL> 
SQL> begin
  2  
  3    insert into mylist values (1,1);
  4    insert into mylist values (2,2);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index mylist_uniq validate structure;

Index analyzed.

SQL> 
SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,
  2  pct_used FROM index_stats;

    HEIGHT     BLOCKS  LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
  1     8        1   0       0        7996      1

1 row selected.

SQL> 
SQL> select * from mylist;

 ID     N
---------- ----------
  1     1
  2     2

2 rows selected.

SQL> 
SQL> alter table mylist drop partition two update indexes;

Table altered.

SQL> 
SQL> analyze index mylist_uniq validate structure;

Index analyzed.

SQL> 
SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,
  2  pct_used FROM index_stats;

    HEIGHT     BLOCKS  LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
  1     8        1   0       1        7996      1

1 row selected.

SQL> 
SQL> alter table mylist add partition two values (2);

Table altered.

SQL> 
SQL> begin
  2  
  3    insert into mylist values (3,2);
  4  
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from mylist;

 ID     N
---------- ----------
  1     1
  3     2

2 rows selected.

SQL> 
SQL> select * from v$lock where sid = sys_context('USERENV','SID');

ADDR   KADDR    SID TY        ID1   ID2    LMODE    REQUEST  CTIME     BLOCK     CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000090218188 0000000090218200  121 TO      86562     1        3   0  96423  0    3
000000009021B118 000000009021B190  121 AE        133     0        4   0  97510  0    3
00007FDD82F7E0F8 00007FDD82F7E160  121 TM      99654     0        3   0      0  0    3
00007FDD82F7E0F8 00007FDD82F7E160  121 TM      99658     0        3   0      0  0    3
000000008DF7C648 000000008DF7C6C8  121 TX    1310745   330        6   0      0  0    0

5 rows selected.

SQL> 
SQL> prompt "Run:  alter index mylist_uniq coalesce cleanup; from another session"
"Run:  alter index mylist_uniq coalesce cleanup; from another session"
SQL> 
SQL> prompt "Then hit enter..."
"Then hit enter..."
SQL> pause

SQL> 
SQL> set lines 200
SQL> set pages 200
SQL> 
SQL> prompt "WAITEVENT: "enq: TX - row lock contention" Reference Note (Doc ID 1966048.1)"
"WAITEVENT: "enq: TX - row lock contention" Reference Note (Doc ID 1966048.1)"
SQL> 
SQL> SELECT
  2   sid, seq#, state, seconds_in_wait,
  3   'TX-'||lpad(ltrim(p2raw,'0'),8,'0')||'-'||lpad(ltrim(p3raw,'0'),8,'0') TX,
  4   trunc(p2/65536)      XIDUSN,
  5   trunc(mod(p2,65536)) XIDSLOT,
  6   p3       XIDSQN
  7    FROM v$session_wait
  8   WHERE event='enq: TX - row lock contention'
  9  ;

       SID  SEQ# STATE    SECONDS_IN_WAIT TX            XIDUSN    XIDSLOT  XIDSQN
---------- ---------- ------------------- --------------- -------------------------------------------------------------------- ---------- ---------- ----------
       100   167 WAITING    3 TX-00140019-0000014A              20   25     330

1 row selected.

SQL> 
SQL> SELECT distinct w.tx, l.inst_id, l.sid, l.lmode, l.request
  2   FROM
  3    ( SELECT p2,p3,
  4     'TX-'||lpad(ltrim(p2raw,'0'),8,'0')||'-'||lpad(ltrim(p3raw,'0'),8,'0') TX
  5      FROM v$session_wait
  6     WHERE event='enq: TX - row lock contention'
  7       and state='WAITING'
  8    ) W,
  9    gv$lock L
 10   WHERE l.type(+)='TX'
 11   and l.id1(+)=w.p2
 12   and l.id2(+)=w.p3
 13   ORDER BY tx, lmode desc, request desc
 14  ;

TX         INST_ID        SID LMODE  REQUEST
-------------------------------------------------------------------- ---------- ---------- ---------- ----------
TX-00140019-0000014A             1        121     6        0
TX-00140019-0000014A             1        100     0        4

2 rows selected.

SQL> 
SQL> SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
  2    FROM v$session
  3   WHERE event='enq: TX - row lock contention'
  4   AND state='WAITING'
  5  ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
    -1     0     0   0

1 row selected.

SQL> 
SQL> prompt "What Does obj#=-1 Mean for Wait Events 'Enq: TX - Row Lock Contention' / 'enq: TX - allocate ITL entry' ? (Doc ID 1945821.1)"
"What Does obj#=-1 Mean for Wait Events 'Enq: TX - Row Lock Contention' / 'enq: TX - allocate ITL entry' ? (Doc ID 1945821.1)"
SQL> 

感谢伟大的测试用例。尽管您不需要重新创建分区即可看到此效果。只是删除它会产生类似的结果:

create table mylist (id number, n number)
partition by list (n)
(
  partition one values (1),
  partition two values (2)
);

create unique index mylist_uniq on mylist(id);

insert into mylist values (1,1);
insert into mylist values (2,2);
commit;

select * from mylist;

alter table mylist drop partition two update indexes;
insert into mylist values (3,1);

-- in a different session, run:
alter index mylist_uniq coalesce cleanup;
-- This will be blocked by the insert above until you commit

这是预期的行为。它的出现是因为唯一索引可以重用孤立条目。

正如我们的开发人员之一George Eadon解释的那样:

“在唯一索引中，coalesce cleanup将始终等待索引中的行锁。我们非常小心地以无死锁的方式执行此操作。这是必需的，因为无需等待，我们无法确定我们已经清除了所有孤立的行。所有孤立的行，我们不能删除孤立的条目元数据。

在您的示例中，因为我们使用异步索引维护，所以在DROP分区之后，索引仍将包含两行:

-分区一的键 <1> w/ rowid
-键 <2> w/ rowid用于分区2，该分区已不存在

现在，假设我们插入 (2,1) (而不是您的示例中的 (3,1))。这意味着密钥 <2> 将使用分区1的rowid更新。

当合并清理通过时，我们会发现这个未提交的密钥 <2>。如果我们不等待这个未提交的密钥，我们不知道更改是否会提交或回滚 (或回滚到中间保存点，然后提交)。因此，如果我们不等待就跳过此键，然后将 (2,1) 的插入回滚，则在合并完成后，我们将剩下键 <2> 指向第二分区。”

当在我的例子中插入一个新值，如 (3,1)，数据库不知道这是一个真正的新条目还是一个重用的孤立条目。因此，合并必须等到提交或回滚。

如果您想了解更多信息，可以阅读Richard Foote关于唯一索引的异步维护的文章:

https://richardfoote.wordpress.com/2013/08/07/12c-asynchronous-global-index-maintenance-part-iii-re-makere-model/