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

什么是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 9999
col serial# for 9999
col program for a30
col event for a30
col state for a10
col sql_text for a50
col inst_id for 9
select 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_text
from gv$session a,gv$session_wait b,gv$sql c
where 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_textINST_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 VALID       auzjshzj54dut update test.test_1 set name1='1bbbbbbc' where id=12    47      17 sqlplus@rac2 (TNS V1-V3)  WAITING    enq: TX - row lock contention  1415053318 0000000054580006    655362 00000000000A0002   777 0000000000000309     1770          2         44 VALID       auzjshzj54dut update test.test_1 set name1='1bbbbbbc' where id=1
#将语句查询的p1,p2值带入v$lock，可以查询请求的锁信息
set linesize 400
select *
from v$lock
where id1=524288 and id2=1318ADDR                    KADDR       SID TY        ID1           ID2     LMODE    REQUEST   CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000081D912B0 0000000081D91308    50 TX     524288          1318         0      6        16954       0

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


col SESSION_ID for 9999
col SESSION_serial# for 9999
col program for a30
col event for a30
col session_state for a10
col sql_text for a50
col inst_id for 9
select 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_text
from gv$active_session_history a,gv$session_wait_history b,gv$sql c
where 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_id
and a.sample_time between timestamp'2022-05-17 00:00:00' and timestamp'2022-05-18 00:00:00';

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

#查看等待事件的信息
set linesize 400
set pagesize 400
col sample_time for 40
col sql_text for a60
col event for a40
select 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_text
from DBA_HIST_ACTIVE_SESS_HISTORY a,dba_hist_sqltext b                                                                                                  
where 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_TIMEFROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2022-05-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ANDTO_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 400
set pagesize 400
col object_name for a40
select a.inst_id,a.sid,a.type,a.id1,a.id2,b.owner,b.object_name,a.lmode,a.request
from gv$lock a,dba_objects b
where 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 TY       ID1     ID2  OWNER            OBJECT_NAME    LMODE    REQUEST
---------- ---------- -- ---------- ---------- ----------------------- ---------------------------------------- ---------- ----------1          54 TM      88060       0 TEST            TEST_1            3     01          54 TX     655362     777                                   0     62          44 TX     655362     777                                   6     02          44 TM      88060       0 TEST            TEST_1            3     02          47 TX     655362     777                                   0     62          47 TM      88060       0 TEST            TEST_1            3     0

4 查询堵塞链

单实例查询堵塞链源头。

---sql1
set lines 200 pages 100
col tree for a30
col event for a40
select *from (select a.sid, a.serial#,a.sql_id,a.event,a.status,connect_by_isleaf as isleaf,sys_connect_by_path(SID, '<-') tree,level as tree_levelfrom v$session astart with a.blocking_session is not nullconnect by nocycle a.sid = prior a.blocking_session)where isleaf = 1;---sql2
SELECT  s1.username || '@'|| s1.machine|| ' ( SID='|| s1.sid|| ' )  is blocking '|| s2.username|| '@'|| s2.machine|| ' ( SID='|| s2.sid|| ' ) 'AS blocking_status   
FROM   v$lock l1,v$session s1,v$lock l2,v$session s2  
WHERE s1.sid = l1.sid AND s2.sid = l2.sid AND l1.BLOCK = 1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
#54正堵着53
------------------------------------------------------
SYS@nrac1 ( SID=54 )  is blocking SYS@nrac1 ( SID=53 )---sql3
set linesize 200
col user_name for a20
col owner for a20
col object_name for a30SELECT /*+ 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_NAME       OWNER      OBJECT_NAME       OBJECT_TYPE        SID    SERIAL#
-------------------- -------------------- ------------------------------ ------------------- ---------- ----------
SYS   --堵塞   SYS      BLOCKING1       TABLE           54   37SYS--被堵塞 SYS      BLOCKING1       TABLE           53   27
SYS             SYS      BLOCKING2       TABLE           57   55SYS         SYS      BLOCKING2       TABLE           59   17SYS         SYS      BLOCKING2       TABLE           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_levelfrom gv$session astart with a.blocking_session is not nullconnect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))where isleaf = 1order 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           22                 17                  SQL*Net message from client        INACTIVE    1       <- 29@2 <- 55@2            2

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

#根据堵塞会话，查询被堵塞的object_id以及对应的file,block
SQL> 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#
---- ------------- -------------- --------------- -------------50       88060       4                 2868        0object_id:88060
file#:4
block#:2868
#根据object_id:88060查询等待发生的对象
col owner for a20
col object_name for a40
select 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查询等待发生行的rowid
col  object_name for a30
SELECT 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 v
WHERE  s.blocking_session = 55                          
AND    s.row_wait_obj# = do.object_id
AND    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               2868              0        AAAVf8AAEAAAAs0AAA#根据rowid查询等待的行
SQL> select * from test.test_1 where rowid='AAAVf8AAEAAAAs0AAA';ID          NAME1            NAME2           NAME3        ST
---------- ------------------------------ ------------------------------ ------------------------------ --1         1aaaaaa        1bbbbbbbbb       1ccccccccc      0

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

#查看SQL执行计划
set linesize 400
set pagesize 400
select * 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_type
from dba_ind_columns a,dba_indexes b
where 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 200
set long 99999
set pagesize 2000
select o.user_name,o.sid,o.sql_id,s.sql_fulltext,o.cursor_type,o.LAST_SQL_ACTIVE_TIME
from v$open_cursor o,v$sql s
where o.sql_id=s.sql_id and o.sid=476;
---查询事务信息
select b.* from gv$session_wait a,gv$transaction b
where  a.event='enq: TX - row lock contention' and trunc(a.p2/power(2,16)) = b.xidusn
and (bitand(a.p2,to_number('ffff','xxxx'))+0) = b.xidslot and a.p3 = b.xidsqn;

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

总结:

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