oracle tx等待,oracle等待事件实验--enq: TX - row lock contention

commit;

--在session1中删除

delete from tx_lock_tab where a=100;

--在session2中删除delete from tx_lock_tab where a=100;

--查看锁队列

select * from v$enqueue_lock where type='TX';

--等待事件

select s.sid,s.event from v$session_event s,v$enqueue_lock l

where s.sid =l.SID

and s.EVENT like 'enq: TX%';

--查看对应的sql

select distinct w.waiting_session,

s2.USERNAME waiting_user,

q2.SQL_TEXT waiting_sql

from dba_waiters w, v$session s2, v$sqlarea q2

where w.waiting_session = s2.SID

and s2.SQL_ADDRESS = q2.ADDRESS;

--通过等待事件找到具体的数据

select s.sid,

o.object_name wait_object,

s.row_wait_obj#,

s.ROW_WAIT_FILE#,

s.ROW_WAIT_BLOCK#,

s.ROW_WAIT_ROW#,

dbms_rowid.rowid_create(1,

s.row_wait_obj#,

s.ROW_WAIT_FILE#,

s.ROW_WAIT_BLOCK#,

s.ROW_WAIT_ROW#) l_rowid

from v$session s, v$enqueue_lock l,dba_objects o

where s.sid = l.SID

and s.ROW_WAIT_OBJ# = o.object_id(+)

and s.SID = 156;

select * from tx_lock_tab where rowid = 'AAAT+FAAGAAAcy+AAD';

--等待事件，等待sql及耗时时长

select s.sid, s.event, s.seconds_in_wait, q.sql_text

from v$session s, v$sqlarea q

where s.SQL_ADDRESS = q.ADDRESS(+)

and s.SQL_HASH_VALUE = q.HASH_VALUE(+)

and s.SID = 156;

生成ASH报告，可以在Top Events中看到，这种问题往往是业务逻辑代码不合理。