网站导航
> 文档中心 > Oracle-enq: TX - row lock contention 等待事件分析

Oracle-enq: TX - row lock contention 等待事件分析

网友: 文档中心 2022-05-19 09:16:52

什么是enq:TX - row lock contention等待:

等待事件enq:TX - row lock contention 是Oracle常见的几大等待事件之一,在开启的事务中，为了维护事务数据的一致性，会在事务所涉及的修改行中添加TX锁以防止其他会话同时修改数据，当其他会话等待该TX锁的释放时，就会产生enq:TX -row lock contention等待事件。

事件p1,p2,p3含义:

• P1 = name|mode

• P2 = usn<<16 | slot

• P3 = sequence

• name|mode

The lock name and requested mode are encoded into P1 as "name<<16 | mode". This is best seen in P1RAW (or you can convert P1 to hexadecimal).

For this wait:

§ name is always the ASCII for "TX" = 0x5458.

§ mode is the mode that the TX lock is being requested in.

In most cases mode will be 6 . Occassionally it may be 4:

□ "6" = eXclusive mode TX wait.

□ "4" = Shared mode TX wait.

eg: P1RAW of 54580006 = eXclusive mode TX wait, P1RAW of 54580004 = Shared mode TX wait.

• usn<<16 | slot

P2RAW indicates the ID1 part of the TX lock being waited on, which is an encoding of the undo segment (usn) and the undo slot.

• sequence

P3RAW indicates the ID2 part of the TX lock being waited on, which is the undo slot sequence number.

常见的TX锁等待原因:

1 应用代码逻辑层有问题，导致同时修改相同数据引发锁等待。

2 应用代码逻辑层有问题，导致事务不提交引发锁等待。

3 主键或者唯一键冲突引发锁等待。

4 位图索引维护引发锁等待。

5 事务回滚导致的锁等待。

6 慢SQL导致的锁等待。

如何分析收集锁等待信息：

1 通过awr,ash或者addm去获取。

@?/rdbms/admin/awrrpt@?/rdbms/admin/ashrpt@?/rdbms/admin/addmrpt

AWR报告解读:

从Top Event等待查看enq:TX的DBTIME 占比以及平均等待wait avg

从Row Lock Waits去确认发生锁等待的对象。

ASH报告解读:

从Top SQL with Top Events确认产生锁等待的SQL_TEXT。

从Top Blocking Sessions确认主要的堵塞会话信息。

addm报告解读:

可以从里面发现等待的对象，堵塞源以及等待的SQL。

2 查询enq:TX - row lock contention等待事件信息。

col sid for 9999col serial# for 9999col program for a30col event for a30col state for a10col sql_text for a50col inst_id for 9select a.inst_id,a.sid,a.serial#,a.program,a.state,b.event,b.p1,b.p1raw,b.p2,b.p2raw,b.p3,b.p3raw,b.SECONDS_IN_WAIT,a.BLOCKING_INSTANCE,a.BLOCKING_SESSION,a.BLOCKING_SESSION_STATUS,a.sql_id,c.sql_textfrom gv$session a,gv$session_wait b,gv$sql cwhere a.sid=b.sid and a.inst_id=b.inst_id and a.event=b.event and b.event like '%TX%' and a.sql_id=c.sql_id  and a.inst_id=c.inst_id;

主要获取的TX等待事件涉及的当前会话信息，等待事件信息，堵塞会话信息以及执行的SQL。

#从当前的查询信息，我们可以确认会话(sid=54,47) 被会话(blocking_session=44)堵塞，当前的请求锁信息为TX-6级锁(P1raw=0000000054580006)，执行的sql_text INST_ID   SID SERIAL# PROGRAM      STATE     EVENT   P1 P1RAW   P2 P2RAW P3 P3RAW SECONDS_IN_WAIT BLOCKING_INSTANCE BLOCKING_SESSION BLOCKING_SE SQL_ID     SQL_TEXT---------- ----- ------- ------------------------------ ---------- ------------------------------ ---------- ---------------- ---------- ---------------- ---------- ---------------- --------------- ----------------- ---------------- ----------- ------------- --------------------------------------------------   1    54      19 sqlplus@rac1 (TNS V1-V3)  WAITING    enq: TX - row lock contention  1415053318 0000000054580006    655362 00000000000A0002   777 0000000000000309     1758   2  44 VALIDauzjshzj54dut update test.test_1 set name1='1bbbbbbc' where id=1   2    47      17 sqlplus@rac2 (TNS V1-V3)  WAITING    enq: TX - row lock contention  1415053318 0000000054580006    655362 00000000000A0002   777 0000000000000309     1770   2  44 VALIDauzjshzj54dut update test.test_1 set name1='1bbbbbbc' where id=1#将语句查询的p1,p2值带入v$lock，可以查询请求的锁信息set linesize 400select *from v$lockwhere id1=524288 and id2=1318ADDR      KADDRSID TY ID1    ID2     LMODE    REQUEST   CTIME      BLOCK---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------0000000081D912B0 0000000081D91308    50 TX     524288   1318  0      6 169540

如果查询的会话信息已经断开，这可以从内存历史视图去获取。

col SESSION_ID for 9999col SESSION_serial# for 9999col program for a30col event for a30col session_state for a10col sql_text for a50col inst_id for 9select a.SAMPLE_TIME,a.inst_id,a.SESSION_ID,a.SESSION_serial#,a.program,a.session_state,b.event,b.p1,b.p2,b.p3,b.wait_time,a.BLOCKING_INST_ID,a.BLOCKING_SESSION,a.BLOCKING_SESSION_STATUS,a.sql_id,c.sql_textfrom gv$active_session_history a,gv$session_wait_history b,gv$sql cwhere a.SESSION_ID=b.sid and a.inst_id=b.inst_id and a.event=b.event and b.event like '%TX%' and a.sql_id=c.sql_id  and a.inst_id=c.inst_idand a.sample_time between timestamp'2022-05-17 00:00:00' and timestamp'2022-05-18 00:00:00';

如果想追溯时间更久远的，可以查询历史视图去获取。

#查看等待事件的信息set linesize 400set pagesize 400col sample_time for 40col sql_text for a60col event for a40select to_char(a.SAMPLE_TIME,'yyyymmdd hh24:mi:ss'),a.SESSION_ID,a.SESSION_SERIAL#,a.event,a.p1,a.p2,a.p3,a.sql_id,a.BLOCKING_SESSION,a.BLOCKING_SESSION_SERIAL#,b.sql_textfrom DBA_HIST_ACTIVE_SESS_HISTORY a,dba_hist_sqltext bwhere a.SAMPLE_TIME between to_date('2022-05-16 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2022-05-17 00:00:00','yyyy-mm-dd hh24:mi:ss')  and a.sql_id=b.sql_id      AND D.EVENT = 'enq: TX - row lock contention'order by to_char(a.SAMPLE_TIME,'yyyymmdd hh24:mi:ss') ;#查看争用对象SELECT  D.SESSION_ID,      D.SESSION_SERIAL#,      D.current_obj#,      D.current_file#,      D.current_block#,      D.current_row#,D.EVENT,      D.P1TEXT,      D.P1,      D.P2TEXT,      D.P2,      CHR(BITAND(P1, -16777216) / 16777215) ||      CHR(BITAND(P1, 16711680) / 65535) "Lock",      BITAND(P1, 65535) "Mode",      D.BLOCKING_SESSION,      D.BLOCKING_SESSION_STATUS,      D.BLOCKING_SESSION_SERIAL#,      D.SQL_ID,      TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDDHH24MISS') SAMPLE_TIME FROM DBA_HIST_ACTIVE_SESS_HISTORY DWHERE D.SAMPLE_TIME BETWEEN TO_DATE('2022-05-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND     TO_DATE('2022-05-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss')  AND D.EVENT = 'enq: TX - row lock contention'order by D.BLOCKING_SESSION;  SELECT * FROM dba_objects D WHERE D.object_id=87620;

3 查询锁信息

set linesize 400set pagesize 400col object_name for a40select a.inst_id,a.sid,a.type,a.id1,a.id2,b.owner,b.object_name,a.lmode,a.requestfrom gv$lock a,dba_objects bwhere a.id1=b.object_id(+) and a.type in ('TM','TX')order by a.inst_id,a.sid;

可以查看当前哪些会话持有了哪些对象的TX,TM锁，哪些会话请求对象的TX,TM锁。

#SID-44 持有了TEST.TEST_1的TM-3以及TX-6锁，SID-47，SID-54持有TEST.TEST_1的TM-3锁，请求TX-6锁  INST_ID    SID TYID1     ID2  OWNER     OBJECT_NAME    LMODE    REQUEST---------- ---------- -- ---------- ---------- ----------------------- ---------------------------------------- ---------- ----------   1   54 TM      880600 TEST     TEST_1     3     0   1   54 TX     655362     7770     6   2   44 TX     655362     7776     0   2   44 TM      880600 TEST     TEST_1     3     0   2   47 TX     655362     7770     6   2   47 TM      880600 TEST     TEST_1     3     0

4 查询堵塞链

单实例查询堵塞链源头。

---sql1set lines 200 pages 100col tree for a30col event for a40select *  from (select a.sid, a.serial#, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(SID, ' 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;#54正堵着53------------------------------------------------------SYS@nrac1 ( SID=54 )  is blocking SYS@nrac1 ( SID=53 )---sql3set linesize 200col user_name for a20col owner for a20col object_name for a30 SELECT /*+ rule */ LPAD (' ', DECODE (l.xidusn, 0, 3, 0))||l.oracle_username User_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#      FROM   v$locked_object l, dba_objects o, v$session s     WHERE   l.object_id = o.object_id AND l.session_id = s.sid  ORDER BY   o.object_id, xidusn DESC   USER_NAMEOWNER      OBJECT_NAMEOBJECT_TYPE SID    SERIAL#-------------------- -------------------- ------------------------------ ------------------- ---------- ----------SYS   --堵塞   SYS      BLOCKING1TABLE    54   37   SYS--被堵塞 SYS      BLOCKING1TABLE    53   27SYS      SYS      BLOCKING2TABLE    57   55   SYS  SYS      BLOCKING2TABLE    59   17   SYS  SYS      BLOCKING2TABLE    58   53

集群查询堵塞链源头。

select *  from (select a.inst_id, a.sid, a.serial#, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree, level as tree_level   from gv$session a  start with a.blocking_session is not null connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance)) where isleaf = 1 order by tree_level asc;

<- 50@1 <- 55@2：表示实例一SID:50被实例二SID:55所堵塞，堵塞源头为实例二SID:55。

INST_ID    SID SERIAL# SQL_ID EVENT   STATUS      ISLEAF   TREE TREE_LEVEL---------- ---- ------- ------------- ------------------------------- -------------- -------- --------------    -----------------   2  55      17    SQL*Net message from client INACTIVE    1<- 50@1 <- 55@2    2   2   17    SQL*Net message from client INACTIVE    1<- 29@2 <- 55@2     2

5 查询等待的对象，行数据

#根据堵塞会话，查询被堵塞的object_id以及对应的file,blockSQL> select sid,row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#  from v$session where blocking_session=55 and blocking_instance=2; SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#---- ------------- -------------- --------------- -------------  50880604   2868 0object_id:88060file#:4block#:2868#根据object_id:88060查询等待发生的对象col owner for a20col object_name for a40select owner,object_name from dba_objects where object_id=88060;SQL> SQL> OWNER  OBJECT_NAME-------------------- ----------------------------------------TEST  TEST_1#根据file=4查询等待发生对象所在的文件SELECT rfile#, file# , name FROM   v$datafile WHERE  file# = 4;RFILE#  FILE#---------- ----------NAME----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   4      4+DATA/dbocs/datafile/users.269.1036073665#根据blocking_session 55查询等待发生行的rowidcol  object_name for a30SELECT do.object_name ,s.row_wait_obj#  ,s.row_wait_file# ,s.row_wait_block#,s.row_wait_row#  ,dbms_rowid.rowid_create( 1, data_object_id, rfile#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )FROM   v$session s,dba_objects do,v$datafile vWHERE  s.blocking_session = 55     AND    s.row_wait_obj# = do.object_idAND    s.row_wait_file# = v.file#;OBJECT_NAME   ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C------------- ------------- -------------- --------------- ------------- ------------------TEST_1      88060      4 28680 AAAVf8AAEAAAAs0AAA#根据rowid查询等待的行SQL> select * from test.test_1 where rowid='AAAVf8AAEAAAAs0AAA';  ID   NAME1     NAME2    NAME3 ST---------- ------------------------------ ------------------------------ ------------------------------ --   1  1aaaaaa 1bbbbbbbbb1ccccccccc      0

6 获取引发TX锁等待的SQL的执行计划，索引信息

#查看SQL执行计划set linesize 400set pagesize 400select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced -PROJECTION allstats last'));#查看索引信息select a.index_owner,a.table_name,a.column_name,a.index_name,b.index_typefrom dba_ind_columns a,dba_indexes bwhere a.index_owner=b.owner and a.index_name=b.index_name and a.table_name='';

7 查询堵塞源会话未提交语句

有时候查询到的堵塞源会话当前的执行sql是select查询或者空闲无执行sql状态，这通常发生在一个事务包含多条sql或者空闲事务不提交，在这种情形下，我们需要去查询堵塞源会话的打开游标信息以及堵塞源的事务信息，从里面获取当前未提交的执行SQL以及事务信息。

---查询游标信息#需要注意的是查询的结果不一定就是当前会话已经执行过的全部sql，因为有些游标不一定有缓存，可能执行完就直接关闭结束了。set linesize 200set long 99999set pagesize 2000select o.user_name,o.sid,o.sql_id,s.sql_fulltext,o.cursor_type,o.LAST_SQL_ACTIVE_TIMEfrom v$open_cursor o,v$sql swhere o.sql_id=s.sql_id and o.sid=476;---查询事务信息select b.* from gv$session_wait a,gv$transaction bwhere  a.event='enq: TX - row lock contention' and trunc(a.p2/power(2,16)) = b.xidusnand (bitand(a.p2,to_number('ffff','xxxx'))+0) = b.xidslot and a.p3 = b.xidsqn;

如果查询不到SQL，这应该从应用逻辑代码入手，分析sql的执行情况。

总结:

产生TX锁等待的原因一般都是应用程序的逻辑问题，表，索引设计问题以及性能问题导致，在分析时，要收集等待的对象，操作的SQL，堵塞的上下游，等待对象TX锁的持有级别，请求级别以及SQL的性能来确认TX锁等待的原因。

网络标签：

相关问题