ANBOB™

enq是一种保护共享资源的锁定机制，一个排队机制, 排它机制从一个事务的第一次改变直到rollback or commit 结束这个事务.
P1 = name|mode
P2 = usn<<16 | slot
P3 = sequence

TX等待mode是6，当一个session 在一个表的行级锁定时另一个会话总是等待，一般发生在一些用户insert or update,而另一个用户同样也在insert or update 这同一批数据时发生。这种类型的等待通常就是eventenq:TX-rowlockcontention.解决方法是让第一个会话commit or rollback 结束这个事务

tx等待mode如果是4，这种一般发生在同时更新一个数据块上的数据，但不一定是同一条记录，这就是传说种的热块，这里有一个概念就是ITL(interested transaction list),有一个建表参数maxtrans ，也就是说允许一个块上有多少个active transaction, 不过到了oracle 10G,这个参数被放弃，默认最大为255,这种类型的等待通常叫eventenq:TX-allocateITLentry

tx mode is 4 也是有可能导致那个等待，就是多次更新位图索引时，TX原因还有很多，如下：
1.不同的session更新或删除同一个记录。
2.唯一索引有重复索引
3.位图索引多次更新
4.同时对同一个数据块更新
5.等待索引块分裂

简单可以通过以下查询

#对于当前时间下
select sid,username,event,wait_class,command from v$session where state in('WAITING') and wait_class<>'Idle';

确认对象, 块号
select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';

确认mode
select ROW_WAIT_OBJ#,p1,count(*) from v$session where event='enq: TX - row lock contention' group by ROW_WAIT_OBJ#,p1;

select 'mode '||bitand(P1,power(2,14)-1) from dual;

# 确认blocker
v$session.blocker_session_instance, blocker_session
or 

P2 and P3 correspond to the ID1 and ID2 columns of [G]V$LOCK for rows with TYPE='TX'.

SELECT distinct w.tx, l.inst_id, l.sid, l.lmode, l.request
 FROM 
  ( SELECT p2,p3,
     'TX-'||lpad(ltrim(p2raw,'0'),8,'0')||'-'||lpad(ltrim(p3raw,'0'),8,'0') TX
      FROM v$session_wait 
     WHERE event='enq: TX - row lock contention'
       and state='WAITING'
  ) W, 
  gv$lock L
 WHERE l.type(+)='TX'
   and l.id1(+)=w.p2
   and l.id2(+)=w.p3
 ORDER BY tx, lmode desc, request desc
;

# 确认transaction
select trunc(P2/power(2,16)) usn, mod(P2,power(2,16)) slot  from dual;

select ses_addr from v$transaction where XIDUSN =usn and XIDSLOT = slot;

select sid, sql_hash_value,USERNAME,MACHINE,PROGRAM,LAST_CALL_ET,event,blocking_session from v$session where saddr = ses_addr;

-- or 

SELECT 
   sid, seq#, state, seconds_in_wait,
   'TX-'||lpad(ltrim(p2raw,'0'),8,'0')||'-'||lpad(ltrim(p3raw,'0'),8,'0') TX,
   trunc(p2/65536)      XIDUSN,
   trunc(mod(p2,65536)) XIDSLOT,
   p3                   XIDSQN
  FROM v$session_wait 
 WHERE event='enq: TX - row lock contention'

# 对于过去时间的
/*
col file# for 99999
col block# for 999999
col obj for a18
col type for a9
col lm for 99
col bsid for 9999
*/

select count(*) cnt,
session_id sid,
substr(event,1,30) event,
mod(p1,16) as lm,
sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
, blocking_session bsid
from v$active_session_history ash,
all_objects o
where
event like 'enq: T%'
-- and SAMPLE_TIME between xx and xx
and o.object_id (+)= ash.current_obj#
group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
order by count(*)
/
#Show which segments have undergone the most row lock waits:

SELECT owner, object_name, subobject_name, value
FROM v$segment_statistics
WHERE statistic_name='row lock waits'
AND value > 0
ORDER BY 4 DESC;

或查dba_hist_active_sess_history, 确认MODE ,确认blocker_session,  确认blocker_session在做什么，
结合sql_id,seq#,sample_time， 看看blocker 是否存在IDEL时依然blocker other session，事务未及时提交。

注：
有些情况v$session 中的blocking_session 为空，可以做oradebug dump hanganalyze 3 生成trace查看，或者查询V$WAIT_CHAINS(NOT SURE)

Summary:

A TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.

TX = Transaction = Wait on UNDO
Mode 6 (exclusive)

modification of a row lock

Mode 4 (share)

Index block spilt
Unique Index Key enforcement
Foreign key enforcement
ITL space waits
Bitmap chunk conflicts
Alter tablespace … read only;
Free Lists slot waits
Possible with two phase commit

TX mode 6 is straight forward, just two sessions trying to change the same data

TX mode 4 has many reasons and historically has been hard to diagnose, but now in 10g Oracle had broken down TX locks in to these sub types:

    
    enq: TX - row lock contention
       pk or unique index violation
       fk violation
       bitmap chunk wait
       others ?
    enq: TX - allocate ITL entry
       Wait on an ITL slot
    enq: TX - index contention
       Index block split
    enq: TX - contention
       Wait for a data file extension
       Alter tbs read only
    others?

(NOTE: that “enq: TX – row lock contention” can happen in both mode 6, the typical case, or mode 4 which is shown above)

References MOS and some DBAs notes

打赏

微信扫一扫，打赏作者吧～