Oracle中TX锁(行锁)监控，抓TX锁的源头数据库落落的专栏 专注SQL调优 性能调优-

各位DBA，看到这篇文章是不是很开心，解决了你一个大麻烦，赶紧把它部署到实时监控程序吧

(咳咳，转载，抄袭不注明文章出处的人可耻哈)

session 1: update emp_bak set ename=’沙雕’ where empno=7369;

session 2: update emp_bak set ename=’大长腿’ where empno=7369;

session 3: update emp_bak set ename=’矮丑穷’ where empno=7369;

运行下面脚本可以抓到哪个SID,哪个SQL_ID,跑的SQL_TEXT锁住了哪个SID,哪个SQL_ID,哪个SQL语句

需要注意的是：如果V$SQLAREA没有保存SQL，可能抓不到，其次，如果系统并发很高，你可能需要再修改下脚本

 select sysdate,        source_sid,        source_sql_id,        source_sql_text,        blocking_sid,        blocking_sql_id,        blocking_sql_text   from (select b.sid source_sid,                d.sql_id source_sql_id,                d.sql_text source_sql_text,                a.sid blocking_sid,                a.sql_id blocking_sql_id,                e.sql_text blocking_sql_text,                (select object_name                   from dba_objects                  where object_id = a.row_wait_obj#) object_name           from v$session     a,                v$session     b,                v$transaction c,                v$sqlarea     d,                v$sqlarea     e          where a.event = 'enq: TX - row lock contention'            and a.blocking_session = b.sid            and b.taddr = c.addr            and to_date(c.start_time, 'mm/dd/yy hh24:mi:ss') =                d.last_active_time            and d.command_type in (2, 3, 6)            and b.user# = d.parsing_schema_id            and a.sql_id = e.sql_id)  where instr(upper(source_sql_text), object_name) > 0; 

脚本运行示例：

 SQL> select sysdate,   2         source_sid,   3         source_sql_id,   4         source_sql_text,   5         blocking_sid,   6         blocking_sql_id,   7         blocking_sql_text   8    from (select b.sid source_sid,   9                 d.sql_id source_sql_id,  10                 d.sql_text source_sql_text,  11                 a.sid blocking_sid,  12                 a.sql_id blocking_sql_id,  13                 e.sql_text blocking_sql_text,  14                 (select object_name  15                    from dba_objects  16                   where object_id = a.row_wait_obj#) object_name  17            from v$session     a,  18                 v$session     b,  19                 v$transaction c,  20                 v$sqlarea     d,  21                 v$sqlarea     e  22           where a.event = 'enq: TX - row lock contention'  23             and a.blocking_session = b.sid  24             and b.taddr = c.addr  25             and to_date(c.start_time, 'mm/dd/yy hh24:mi:ss') =  26                 d.last_active_time  27             and d.command_type in (2, 3, 6)  28             and b.user# = d.parsing_schema_id  29             and a.sql_id = e.sql_id)  30   where instr(upper(source_sql_text), object_name) > 0;   SYSDATE     SOURCE_SID SOURCE_SQL_ID SOURCE_SQL_TEXT                                  BLOCKING_SID BLOCKING_SQL_ID BLOCKING_SQL_TEXT ----------- ---------- ------------- ------------------------------------------------ ------------ --------------- ------------------------------------------------ 2020/5/19 1        192 201c4xcdsjaj0  update emp_bak set ename='沙雕' where empno=7369            4 2hpm4yjuut7cg    update emp_bak set ename='矮丑穷' where empno=7369   2020/5/19 1        192 201c4xcdsjaj0  update emp_bak set ename='沙雕' where empno=7369          221 36xb1pyv12k56    update emp_bak set ename='大长腿' where empno=7369