enq: TX – row lock contention的測試和案例分析

不一樣的天空w發表於2018-01-03
參考:%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

關於enq: TX – row lock contention的測試和案例分析

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為空說明為非活動會話,會話等該提交或者回滾。

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,阻塞會話。
可以看出,對於表存在主鍵或者 unique index 時,一個會話操作主鍵不提交時,其他會話如果也操作相同的主鍵時,那麼必須進行等待,而其持有的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時,多個會話同時進行更新,必然出現tx 等待。
此時waiter申請持有的tx 鎖mode=4,而blocker持有的mode=6,而且透過v$session試圖還無法查詢到blocker會話到sql_id.

3、資料位於同一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個會話執行100w次,也不會出現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>  

我們可以看到,不同會話更新同一block中到不同行,不會存在等待,假設更新同一行,那麼不提交到情況執行,必然存在等待,這裡不再累述。


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.......................子表操作會一直掛起

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  btxh51ngubrv8               74                     74

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

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

實際上我們可以發現,無論子表有沒有主鍵約束,都會存在這種情況,只有主表操作不提交.


1. 其原因一般有如下幾種:
1) 表上存在主鍵或唯一性約束,多個會話操作同一條記錄
2) 表存在主外來鍵讀情況,主表不提交,子表那麼必須進行等待.
3) 表上存在點陣圖Index,這跟uniqeue index中存在重複值是一樣的道理,其中一個會話操作,其他會話必須等待.
4) 表進行自我外來鍵關聯,前面的事務不提交,那麼會導致後面的會話一直等待.

2. 對於網上說的enq: TX – row lock contention也有可能是在等待index block分裂的情況,從理論上來講,如果是在等待index block分裂,那麼應該還伴有enq: TX – index contention等待事件產生.

3. 對於enq: TX – row lock contention,透過v$session檢視查詢時,等待會話帶lock mode通常為4,而blocker會話帶lock mode通常為6,並且一般查詢blocker會話的sql_id都為空。這是正常現象,v$session顯示是當前狀態,而非歷史資料.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2149637/,如需轉載,請註明出處,否則將追究法律責任。

相關文章