一聚教程网:一个值得你收藏的教程网站

最新下载

热门教程

enq: TX

时间:2022-06-29 09:41:24 编辑:袖梨 来源:一聚教程网

、主键或唯一index


---session 1
SQL> select sid from v$mystat where rownum=1;
 
       SID
----------
       130
SQL> create table t1_tx(id number primary key,name varchar2(20));
 
Table created.
 
SQL> insert into t1_tx values(1,'roger');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into t1_tx values(2,'xxoo');
 
1 row created.
 
---session 2
SQL> conn roger/roger
Connected.
SQL> select sid from v$mystat where rownum=1;
 
       SID
----------
        69
 
SQL> insert into t1_tx values(2,'xxoo'); ---一直处于等待状态
 
---session 3
 
SQL> select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",event,sql_id,FINAL_BLOCKING_SESSION
  5           from v$session
  6   where event like 'enq%';
 
       SID Name  Mode EVENT                          SQL_ID        FINAL_BLOCKING_SESSION
---------- ---- ----- ------------------------------ ------------- ----------------------
        69 TX       4 enq: TX - row lock contention  b775wqk86zc6k                    130
 
SQL> select sid,serial#,username,sql_id from v$session where sid=130;
 
       SID    SERIAL# USERNAME                       SQL_ID
---------- ---------- ------------------------------ -------------
       130        185 ROGER
 
SQL> select * from v$Lock where block=1;
 
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2A4F2AAC 2A4F2AEC        130 TX     196612        895          6          0        736          1
可以看出,对于表存在主键或者 unique index 时,一个会话操作主键不提交时,其他会话如果也操作相同的主键时,那么必须进行等待,而其持有的mode=4;而阻塞blocker的持有mode=6.
2、Bitmap INDEX


--session 1
 
SQL> select * from t1_tx;
 
        ID NAME
---------- --------------------
         1 roger
         2 roger
         3 aa
         4 aa
 
SQL> create bitmap index idx_bitmap_name on t1_tx(name);
 
Index created.
 
SQL> select sid from v$mystat where rownum=1;
 
       SID
----------
       130                                  
 
SQL> update t1_tx set name='tx' where id=3; 
 
1 row updated.                              
 
SQL>
 
---session 2
SQL> select sid from v$mystat where rownum=1;                                          
 
       SID
----------
        69                                    
 
SQL> update t1_tx set name='bitmap' where id=4;  ---一直处于等待状态
 
---session 3
 
SQL>  select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",event,sql_id,FINAL_BLOCKING_SESSION
  5           from v$session
  6   where event like 'enq%';                                                                                                  
 
       SID Name       Mode EVENT                           SQL_ID        FINAL_BLOCKING_SESSION
---------- ---- ---------- ------------------------------- ------------- ----------------------
        69 TX            4 enq: TX - row lock contention   7wanaturqndn1                    130
 
SQL>
SQL> set lines 200 pagesize 200
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
Enter value for sql_id: 7wanaturqndn1
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'))
new   1: select * from table(dbms_xplan.display_cursor('7wanaturqndn1', 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
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2A4F3100 2A4F3140        130 TX     262144        563          6          0        209          1
 
SQL> l
  1* select * from v$Lock where block=1
SQL> /
 
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2A4F3100 2A4F3140        130 TX     262144        563          6          0        215          1
 
SQL> select sid,serial#,username,sql_id,event from v$session where sid=130;
 
       SID    SERIAL# USERNAME                       SQL_ID        EVENT
---------- ---------- ------------------------------ ------------- -----------------------------------------------------------------
       130        185 ROGER                                        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
------------------------------ ------------------------------ ---------------------------
ROGER                          IDX_BITMAP_NAME                BITMAP
ROGER                          SYS_C0010951                   NORMAL
我们可以看到,如果表上存在位图index,那么在update时,多个会话同时进行更新,必然出现tx 等待。
此时waiter申请持有的tx 锁mode=4,而blocker持有的mode=6,而且通过v$session试图还无法查询到blocker会话到sql_id.
3、数据位于同一block


---session 1
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 order by 4;
 
      OBJ#     RFILE#     BLOCK#       ROW#
---------- ---------- ---------- ----------
     74762          4      30141          0
     74762          4      30141          1
     74762          4      30141          2
     74762          4      30141          3
 
SQL> update t1_tx set name='enmotech' where id=2;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
---session 2
 
SQL>  update t1_tx set name='zhenxu' 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.
 
Elapsed: 00:00:26.58
SQL>
 
---session 2
SQL>  declare
  2     c number;
  3   begin
  4     for i in 1 .. 1000000 loop
  5       update  t1_tx set name='t-shit' where id=3;
  6     end loop;
  7   end;
  8   /
 
PL/SQL procedure successfully completed.
 
--session 3
SQL> select inst_id,event,count(1) from gv$session where wait_class#<>6 group by inst_id,event order by 1,3;
 
   INST_ID EVENT                                                               COUNT(1)
---------- ----------------------------------------------------------------- ----------
         1 asynch descriptor resize                                                   1
         1 Log archive I/O                                                            1
         1 buffer busy waits                                                          2
我们可以看到,不同会话更新同一block中到不同行,不会存在等待,假设更新同一行,那么不提交到情况执行,必然存在等待,这里不再累述。
4、外键


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>
SQL> select index_name,table_name from user_indexes where table_name='T1';
 
no rows selected
 
SQL>
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.
 
SQL>
 
---session 2
SQL> insert into t1 values(1,'xx',5);   --子表操作会一直挂起
 
实际上我们可以发现,无论子表有没有主键约束,都会存在这种情况,只有主表操作不提交.
实际上还有一种更特殊到情况,也会出现,当然原理上来讲,也上主外键的问题,如下测试:


---session 1
SQL> conn roger/roger
Connected.
SQL> create table t3_ref (id number primary key,name varchar2(20),obj_id NUMBER);
 
Table created.
 
SQL> alter table  t3_ref  add constraint fk_id foreign key (obj_id)  references t3_ref (id);
 
Table altered.
 
SQL> insert into t3_ref values(1,'roger',1);
 
1 row created.
 
SQL> insert into t3_ref values(2,'roger',1);
 
1 row created.
 
---session 2
 
SQL> conn roger/roger
Connected.
SQL> insert into t3_ref values(3,'roger',2); ---一直处于等待
 
---session 3
SQL> l
  1   select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",event,sql_id,blocking_session,FINAL_BLOCKING_SESSION
  5           from v$session
  6*  where event like 'enq%'
SQL> /
 
       SID Name       Mode EVENT                            SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---- ---------- -------------------------------- ------------- ---------------- ----------------------
       199 TX            4 enq: TX - row lock contention    8cj5awv9djrby              139                    139
 
所以,对于enq: TX – row lock contention 我们可以进行如下简单总结:

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显示是当前状态,   而非历史数据.
如下是某客户的真实例子的分析过程,如下:


SQL> select inst_id,event,count(1) from gv$session where wait_class#<>6 group by inst_id,event order by 1,3;
 
   INST_ID EVENT                                                               COUNT(1)
---------- ----------------------------------------------------------------- ----------
         1 SQL*Net message to client                                                  1
         1 SQL*Net message from dblink                                                2
         1 db file sequential read                                                    4
         1 library cache: mutex X                                                     4
         1 enq: TX - row lock contention                                             18
         2 library cache: mutex X                                                     1
         2 db file sequential read                                                    1                     
 
7 rows selected.                
 
SQL>  select sid,
       chr(bitand(p1, -16777216) / 16777215) ||
  2    3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",event,sql_id,FINAL_BLOCKING_SESSION
  5           from v$session
  6   where event like 'enq%';                                                                                                                                                                         
 
       SID Name       Mode EVENT                               SQL_ID        FINAL_BLOCKING_SESSION
---------- ---- ---------- ----------------------------------- ------------- ----------------------
       207 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      1008 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      1168 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      1451 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   5286
      1652 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   5286
      2129 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      2207 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   5286
      2723 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   5286
      3095 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      4807 TX            6 enq: TX - row lock contention       djbvcr351s0mh                   1690
      5015 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      5047 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      5213 TX            6 enq: TX - row lock contention       djbvcr351s0mh                   1690
      5372 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   5286
      5374 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      5732 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      6721 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      7608 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810
      7609 TX            4 enq: TX - row lock contention       4fpb7rfm3fb3b                   2810                                                                      
 
19 rows selected.

这里通过dump 这几个process,然后过滤insert into 并没有发现针对party表的insert 操作。
于是尝试换一种思路,通过logminer 来分析进程的操作进程,如下:


SQL> select member from v$logfile where group#=1;
 
MEMBER
--------------------------------------------------
/crm/oradata01/redo01a.log
/crm/oradata02/redo01b.log
 
SQL>  select sid, username,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",event,sql_id,blocking_session,FINAL_BLOCKING_SESSION
  5           from v$session
  6   where event like 'enq%';
 
       SID USERNAME   Name       Mode EVENT                            SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- -------------------------------- ------------- ---------------- ----------------------
       123 CRM_APP    TX            4 enq: TX - row lock contention    4fpb7rfm3fb3b             2802                   2802
       689 CRM_APP    TX            4 enq: TX - row lock contention    4fpb7rfm3fb3b             2802                   2802
       934 CRM_APP    TX            4 enq: TX - row lock contention    4fpb7rfm3fb3b             3128                   3128
      4128 CRM_APP    TX            4 enq: TX - row lock contention    4fpb7rfm3fb3b             2802                   2802
      4449 CRM_APP    TX            4 enq: TX - row lock contention    4fpb7rfm3fb3b             2802                   2802
      6324 CRM_APP    TX            4 enq: TX - row lock contention    4fpb7rfm3fb3b             2802                   2802
 
6 rows selected.
当前6个waiter 会话的事务信息如下:


SQL> SELECT t.xidusn, t.xidslot, t.xidsqn, t.start_time, t.start_scn
  2  FROM v$transaction t JOIN v$session s ON t.addr = s.taddr
  3  WHERE s.sid  in (123,689,934,4128,4449,6324)
  4  /
 
    XIDUSN    XIDSLOT     XIDSQN START_TIME            START_SCN
---------- ---------- ---------- -------------------- ----------
       743         28      61461 07/11/15 22:09:12    1.4484E+13
       828         26      61559 07/11/15 22:07:22    1.4484E+13
       918          5      57068 07/11/15 22:08:12    1.4484E+13
       820          1      64176 07/11/15 22:07:11    1.4484E+13
      1060         16      54417 07/11/15 22:08:12    1.4484E+13
       816          3      62830 07/11/15 22:07:11    1.4484E+13
 
6 rows selected.
当前2个blocker会话的事务信息如下:


SQL> SELECT t.xidusn, t.xidslot, t.xidsqn, t.start_time, t.start_scn
  2  FROM v$transaction t JOIN v$session s ON t.addr = s.taddr
  3  WHERE s.sid  in (2802,3128)
  4  /
 
    XIDUSN    XIDSLOT     XIDSQN START_TIME            START_SCN
---------- ---------- ---------- -------------------- ----------
       949         31      79938 07/11/15 22:06:22    1.4484E+13
      1061         20      57965 07/11/15 22:06:11    1.4484E+13
利用logminer 来分析waiter和blocker 会话的操作信息:


SQL> EXECUTE dbms_logmnr.add_logfile(logfilename=>'/crm/oradata01/redo01a.log');
 
PL/SQL procedure successfully completed.
 
SQL> EXECUTE dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
 
PL/SQL procedure successfully completed.
 
SQL> create table tmp_logmnr_contents as select * from v$logmnr_contents;
 
Table created.
 
SQL> EXECUTE dbms_logmnr.end_logmnr ;
 
PL/SQL procedure successfully completed.
最后查询发现blocker和waiter执行的SQL都类似,因此这就很容易说明问题了. 由于logminer抓取的SQL涉及到客户信息,因此这里不便贴出来。这里只是给大家提供一种思路,对于TX锁的分析,也是可以利用logminer来做的。
最后分析发现,本质上来讲,就是因为前后会话操作相同的数据导致,而表上有存在主键,这必然导致出现TX锁等待。
 
PS:或许有人会说,为什么不直接查v$试图抓取sql的绑定变量,实际上我这里已经查过,没有查到,而且通过dump processstate也没有发现,因此才想到利用logminer来分析问题,找到根本原因。

热门栏目