如何定位TX锁阻塞会话执行的SQL

发生TX - row lock contention等待，我们往往找到blocking_session 然后kill掉会话，但是有时候客户需要知道阻塞会话当时正在执行什么操作，需要确定SQL语句，今天我们测试这个过程，但是由于共享池中SQL的游标可能被LRU算法清除，事务内部DML的数量，所以不保证生产系统一定会捕获阻塞会话在执行的准确的SQL，甚至无法捕获。下面是定位TX锁阻塞会话执行的SQL测试

会话22；

SYS@orcl1>  delete from scott.emp1 where empno=7788;

1 row deleted.

会话1912：

SCOTT@orcl1> update emp1 set ename='' where empno=7788; <<<<此时卡主

查询阻塞信息:

SYS@orcl1>select a.sid,a.blocking_session,a.last_call_et,a.event, object_name,dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "rowid" ,  c.sql_text from v$session a,v$sqlarea c ,dba_objects,v$datafile where a.blocking_session is not null and a.sql_hash_value = c.hash_value and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#

       SID BLOCKING_SESSION LAST_CALL_ET EVENT                          OBJECT_NAME          rowid              SQL_TEXT

---------- ---------------- ------------ ------------------------------ -------------------- ------------------ --------------------------------------------------

      1912               22          729 enq: TX - row lock contention  EMP1                 AAAD3vAAFAAAADDAAH  update emp1 set ename='' where empno=7788

说明：1912号会话被22号会话阻塞，1912号会话申请行级排他锁TX锁，1912号会话正在执行SQL：update emp1 set ename='' where empno=7788，此时要修改的rowID为： AAAD3vAAFAAAADDAAH

涉及的表对象为emp1;

下面继续查询22号会话的事务信息：

SYS@orcl1>  select s.sid,s.status,program,(select substr(sql_text,1,80) from v$open_cursor where sql_id=s.sql_id and s.status='ACTIVE') sql_text,t.start_time,t.used_ublk from gv$session s,gv$transaction t  where s.inst_id=t.inst_id and s.taddr=t.addr and s.sid=22;

       SID STATUS   PROGRAM                        SQL_TEXT                                           START_TIME            USED_UBLK

---------- -------- ------------------------------ -------------------------------------------------- -------------------- ----------

        22 INACTIVE sqlplus@rac1 (TNS V1-V3)                                                          08/27/20 22:59:47             1

Elapsed: 00:00:00.08

说明： 22号会话目前处于INACTIVE也就是没有SQL语句正在执行，事务起始时间为08/27/20 22:59:47，使用了一个undo block;

下面我们继续查询22号会话，这个没有结束的事务正在执行的SQL。

SYS@orcl1>select s.sid,s.program,s.event,TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') as sess_LOGON_TIME,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS') as trans_start_date,s.blocking_session,s.status,(select q.sql_text from v$sql q where q.last_active_time=t.start_date and rownum<=1) as sql_text from v$session s,v$transaction t where s.saddr=t.ses_addr

       SID PROGRAM                        EVENT                          SESS_LOGON_TIME     TRANS_START_DATE    BLOCKING_SESSION STATUS   SQL_TEXT

---------- ------------------------------ ------------------------------ ------------------- ------------------- ---------------- -------- --------------------------------------------------

        22 sqlplus@rac1 (TNS V1-V3)       SQL*Net message from client    2020-08-27 22:59:43 2020-08-27 22:59:47                  INACTIVE delete from scott.emp1 where empno=7788

Elapsed: 00:00:00.00

此时，我们知道阻塞会话正在执行delete操作，这个操作阻塞了1912会话的update操作，而阻塞会话持有的TX锁不释放，造成后续相同row的DML操作全部排队。

注意：如果共享池的特点以及事务自身DML数量，阻塞会话也就是22号会话的未结束事务的SQL很可能无法准确抓到，测试环境相对单一，而在生成环境很可能无法捕获这个SQL。

关于行锁等待，大家可以进一步参考MOS文档  :Waits for 'Enq: TX - ...' Type Events - Transaction (TX) Lock Example Scenarios (Doc ID 62354.1)

或者使用hanganalze，systemdump等进一步确认阻塞关系和每一个进程的状态信息。