enq: TX – row lock contention的測(cè)試和案例分析

發(fā)布時(shí)間：2020-08-11 18:51:51 來源：ITPUB博客閱讀：197 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫

參考：http://www.killdb.com/2015/07/13/%E5%85%B3%E4%BA%8Eenq-tx-row-lock-contention%E7%9A%84%E6%B5%8B%E8%AF%95%E5%92%8C%E6%A1%88%E4%BE%8B%E5%88%86%E6%9E%90.html

關(guān)于enq: TX – row lock contention的測(cè)試和案例分析

1、主鍵或唯一index
session 1：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL> create table t1_tx(id number primary key,name varchar2(20));

Table created.

SQL> insert into t1_tx values(1,'wang');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1_tx values(2,'xxoo');

1 row created.

未提交。。。。。。。。。。。。

session 2：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        45

SQL> insert into t1_tx values(2,'xxoo');

hang。。。。。。。。。。。。。。。

session 3:
SQL> set lines 200
SQL> col event for a30
SQL> select inst_id,
2         sid,
3         chr(bitand(p1, -16777216) / 16777215) ||
4         chr(bitand(p1, 16711680) / 65535) "Name",
5         (bitand(p1, 65535)) "Mode",
6         event,
7         sql_id,
8         blocking_session,
9         FINAL_BLOCKING_SESSION
10    from gv$session
11   where event like 'enq%';

   INST_ID        SID Name       Mode EVENT                          SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
         1         45 TX            4 enq: TX - row lock contention 4s99cmp3khb1b               74                     74

SQL>
SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;

       SID    SERIAL# USERNAME                       SQL_ID        STATUS
---------- ---------- ------------------------------ ------------- --------
        74         23 HR                                           INACTIVE

sql_id為空說明為非活動(dòng)會(huì)話，會(huì)話等該提交或者回滾。

SQL> select * from v$Lock where block=1;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138         74 TX     524298       9978          6          0        221          1

block為1,阻塞會(huì)話。
可以看出,對(duì)于表存在主鍵或者 unique index 時(shí),一個(gè)會(huì)話操作主鍵不提交時(shí),其他會(huì)話如果也操作相同的主鍵時(shí)，那么必須進(jìn)行等待，而其持有的mode＝4；而阻塞blocker的持有mode＝6.

2、Bitmap INDEX
session 1：
SQL> select * from t1_tx;

        ID NAME
---------- --------------------
         1 wang
         2 wang
         3 xxoo
         4 xxoo

SQL>
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL> create bitmap index idx_bitmap_name on t1_tx(name);

Index created.

SQL> update t1_tx set name='tx' where id=3;

1 row updated.

未提交。。。。。。。。。。。

session 2:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        45

SQL> update t1_tx set name='bitmap' where id=4;

hang。。。。。。。。。。。。。

session 3：
SQL> col event for a30
SQL> select inst_id,
2         sid,
3         chr(bitand(p1, -16777216) / 16777215) ||
4         chr(bitand(p1, 16711680) / 65535) "Name",
5         (bitand(p1, 65535)) "Mode",
6         event,
7         sql_id,
8         blocking_session,
9         FINAL_BLOCKING_SESSION
10    from gv$session
11   where event like 'enq%';

   INST_ID        SID Name       Mode EVENT                          SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
         1         45 TX            4 enq: TX - row lock contention 7wanaturqndn1               74                     74

SQL>
SQL> set lines 200 pagesize 200
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
Enter value for amp: 7wanaturqndn1
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'))
new   1: select * from table(dbms_xplan.display_cursor('7wanaturqndn1;sql_id', NULL, 'ALL'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 7wanaturqndn1, child number 0
-------------------------------------
update t1_tx set name='bitmap' where id=4

Plan hash value: 1842098942

-----------------------------------------------------------------------------------
| Id | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |              |       |       |     1 (100)|          |
|   1 | UPDATE            | T1_TX        |       |       |            |          |
|* 2 |   INDEX UNIQUE SCAN| SYS_C0010951 |     1 |    25 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

SQL> select * from v$Lock where block=1;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138         74 TX     262171       2920          6          0        264          1

SQL>

SQL> select sid,serial#,username,sql_id,event from v$session where sid=74;

       SID    SERIAL# USERNAME                       SQL_ID        EVENT
---------- ---------- ------------------------------ ------------- ----------------------------------------------------------------
        74         23 HR                                           SQL*Net message from client

SQL> select owner,index_name,index_type from dba_indexes where table_name='T1_TX';

OWNER                          INDEX_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
HR                             IDX_BITMAP_NAME                BITMAP
HR                             SYS_C0012427                   NORMAL

我們可以看到，如果表上存在位圖index，那么在update時(shí),多個(gè)會(huì)話同時(shí)進(jìn)行更新，必然出現(xiàn)tx 等待。
此時(shí)waiter申請(qǐng)持有的tx 鎖mode＝4，而blocker持有的mode＝6,而且通過v$session試圖還無法查詢到blocker會(huì)話到sql_id.

3、數(shù)據(jù)位于同一block
session 3：
SQL> conn hr/hr;
Connected.
SQL> select dbms_rowid.rowid_object(rowid) obj#,
2         dbms_rowid.rowid_relative_fno(rowid) rfile#,
3         dbms_rowid.rowid_block_number(rowid) block#,
4         dbms_rowid.rowid_row_number(rowid) row#
5    from t1_tx
6   order by 4;

      OBJ#     RFILE#     BLOCK#       ROW#
---------- ---------- ---------- ----------
     90536          4       4087          0
     90536          4       4087          1
     90536          4       4087          2
     90536          4       4087          3

SQL>

session 1：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        45

SQL> select * from t1_tx;

        ID NAME
---------- --------------------
         1 wang
         2 wang
         3 tx
         4 bitmap

SQL> update t1_tx set name='enmotech' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL>

session 2：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL> update t1_tx set name='xyz'where id=4;

1 row updated.

SQL> commit;

Commit complete.

SQL>

即使我分別開2個(gè)會(huì)話執(zhí)行100w次，也不會(huì)出現(xiàn)tx鎖
session 1:
SQL> declare
2    c number;
3 begin
4    for i in 1 .. 1000000 loop
5      update t1_tx set name = 'shit1' where id = 2;
6    end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>

session 2:
SQL> declare c number;
2 begin
3    for i in 1 .. 1000000 loop
4      update t1_tx set name = 't-shit' where id = 3;
5    end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>

session 3:
SQL> set lines 200 pages 999
SQL> col event for a60
SQL> select inst_id,event,count(*) from gv$session where status='ACTIVE' and (wait_class<>'Idle' or event not like 'SQL*Net%') group by inst_id,event order by 1,3;

   INST_ID EVENT                                                          COUNT(*)
---------- ------------------------------------------------------------ ----------
         1 smon timer                                                            1
         1 Streams AQ: waiting for time management or cleanup tasks              1
         1 Streams AQ: qmn slave idle wait                                       1
         1 Space Manager: slave idle wait                                        1
         1 SQL*Net message to client                                             1
         1 VKTM Logical Idle Wait                                                1
         1 pmon timer                                                            1
         1 Streams AQ: qmn coordinator idle wait                                 1
         1 DIAG idle wait                                                        2
         1 rdbms ipc message                                                    17

10 rows selected.

SQL> /

   INST_ID EVENT                                                          COUNT(*)
---------- ------------------------------------------------------------ ----------
         1 log file parallel write                                               1
         1 smon timer                                                            1
         1 Streams AQ: waiting for time management or cleanup tasks              1
         1 Streams AQ: qmn slave idle wait                                       1
         1 buffer busy waits                                                     1
         1 Streams AQ: qmn coordinator idle wait                                 1
         1 SQL*Net message to client                                             1
         1 VKTM Logical Idle Wait                                                1
         1 pmon timer                                                            1
         1 log buffer space                                                      1
         1 Disk file operations I/O                                              1
         1 Space Manager: slave idle wait                                        1
         1 DIAG idle wait                                                        2
         1 rdbms ipc message                                                    15

14 rows selected.

SQL>

我們可以看到,不同會(huì)話更新同一block中到不同行，不會(huì)存在等待，假設(shè)更新同一行，那么不提交到情況執(zhí)行，必然存在等待,這里不再累述。

4、外鍵
session 1:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL>
SQL> create table t1 (id number ,name varchar2(20),product_id number);

Table created.

SQL> create table t2 (id number primary key,name varchar2(20));

Table created.

SQL> alter table t1 add constraint FK_PRODUCTID foreign key (PRODUCT_id) references t2 (ID);

Table altered.

SQL> select index_name,table_name from user_indexes where table_name='T1';

no rows selected

SQL> insert into t2 values(1,'aa');

1 row created.

SQL> insert into t2 values(2,'dd');

1 row created.

SQL> insert into t2 values(3,'cc');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t2 values(5,'cc');

1 row created.

未提交。。。。。。。。。。。

session 2：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        45

SQL>
SQL> insert into t1 values(1,'xx',5);

hang.......................子表操作會(huì)一直掛起

session 3:
SQL> l
1 select inst_id,
2         sid,
3         chr(bitand(p1, -16777216) / 16777215) ||
4         chr(bitand(p1, 16711680) / 65535) "Name",
5         (bitand(p1, 65535)) "Mode",
6         event,
7         sql_id,
8         blocking_session,
9         FINAL_BLOCKING_SESSION
10    from gv$session
11* where event like 'enq%'
SQL> /

   INST_ID        SID Name       Mode EVENT                          SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
         1         45 TX            4 enq: TX - row lock contention btxh61ngubrv8               74                     74

SQL> select sql_text from v$sql where sql_id='btxh61ngubrv8';

SQL_TEXT
--------------------------------------------------------------------------------------------------
insert into t1 values(1,'xx',5)

SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;

       SID    SERIAL# USERNAME                       SQL_ID        STATUS
---------- ---------- ------------------------------ ------------- --------
        74         23 HR                                           INACTIVE

實(shí)際上我們可以發(fā)現(xiàn)，無論子表有沒有主鍵約束，都會(huì)存在這種情況，只有主表操作不提交.

1. 其原因一般有如下幾種:
1) 表上存在主鍵或唯一性約束,多個(gè)會(huì)話操作同一條記錄
2) 表存在主外鍵讀情況,主表不提交,子表那么必須進(jìn)行等待.
3) 表上存在位圖Index,這跟uniqeue index中存在重復(fù)值是一樣的道理,其中一個(gè)會(huì)話操作，其他會(huì)話必須等待.
4) 表進(jìn)行自我外鍵關(guān)聯(lián),前面的事務(wù)不提交，那么會(huì)導(dǎo)致后面的會(huì)話一直等待.

2. 對(duì)于網(wǎng)上說的enq: TX – row lock contention也有可能是在等待index block分裂的情況,從理論上來講,如果是在等待index block分裂，那么應(yīng)該還伴有enq: TX – index contention等待事件產(chǎn)生.

3. 對(duì)于enq: TX – row lock contention,通過v$session視圖查詢時(shí)，等待會(huì)話帶lock mode通常為4，而blocker會(huì)話帶lock mode通常為6，并且一般查詢blocker會(huì)話的sql_id都為空。這是正?，F(xiàn)象，v$session顯示是當(dāng)前狀態(tài),而非歷史數(shù)據(jù).

向AI問一下細(xì)節(jié)

enq: TX – row lock contention的測(cè)試和案例分析

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽