Oracle查询当前enq: TX – row lock contention(二)

阻塞与锁

1. 查询阻塞

SELECT '节点 ' || a_s.INST_ID || ' session ' || a_s.sid || ',' || a_s.SERIAL# ||
       ' 阻塞了 节点 ' || b_s.INST_ID || ' session ' || b_s.SID || ',' ||
       b_s.SERIAL# blockinfo,
       a_s.INST_ID,
       a_s.SID,
       a_s.SCHEMANAME,
       a_s.MODULE,
       a_s.MACHINE,
       a_s.PROGRAM,
       a_s.STATUS,
       '后为被阻塞信息',
       b_s.INST_ID blocked_inst_id,
       b_s.SID blocked_sid,
       b_s.SCHEMANAME blocked_SCHEMANAME,
       b_s.EVENT blocked_event,
       b_s.MODULE blocked_module,
       b_s.STATUS blocked_status,
       b_s.SQL_ID blocked_sql_id,
       obj.owner blocked_owner,
       obj.object_name blocked_object_name,
       obj.OBJECT_TYPE blocked_OBJECT_TYPE,
       case
         when b_s.ROW_WAIT_OBJ# <> -1 then
          dbms_rowid.rowid_create(1,
                                  obj.DATA_OBJECT_ID,
                                  b_s.ROW_WAIT_FILE#,
                                  b_s.ROW_WAIT_BLOCK#,
                                  b_s.ROW_WAIT_ROW#)
         else
          '-1'
       end blocked_rowid, --被阻塞数据的rowid
       decode(obj.object_type,
              'TABLE',
              'select * from ' || obj.owner || '.' || obj.object_name ||
              ' where rowid=''' ||
              dbms_rowid.rowid_create(1,
                                      obj.DATA_OBJECT_ID,
                                      b_s.ROW_WAIT_FILE#,
                                      b_s.ROW_WAIT_BLOCK#,
                                      b_s.ROW_WAIT_ROW#) || '''',
              NULL) blocked_data_querysql
  from gv$session a_s, gv$session b_s, dba_objects obj
 where b_s.BLOCKING_INSTANCE is not null
   and b_s.BLOCKING_SESSION is not null
   and a_s.INST_ID = b_s.BLOCKING_INSTANCE
   and a_s.SID = b_s.BLOCKING_SESSION
   and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
 order by a_s.inst_id, a_s.sid;

2. 查询阻塞后sql语句

SELECT *
  FROM v$sql
WHERE sql_id = '39c75p5w6h3tg';

找到了这个会话：

3. 查询未提交的事务

SELECT s.sid,
       s.serial#,
       s.username,
       s.osuser,
       s.program,
       s.event,
       to_char(s.logon_time, 'yyyy-MM-dd hh24:mi:ss'),
       to_char(t.start_date, 'yyyy-MM-dd hh24:mi:ss'),
       s.last_call_et,
       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号正好是 126。

4. 查询锁

SELECT A.OWNER 方案名,
       A.OBJECT_NAME 表名,
       B.XIDUSN 回滚段号,
       B.XIDSLOT 槽号,
       B.XIDSQN 序列号,
       B.SESSION_ID 锁表SESSION_ID,
       B.ORACLE_USERNAME 锁表用户名,
       D.TYPE,
       decode(D.type,
              'XR',
              'NULL',
              'RS',
              'SS(Row-S)',
              'CF',
              'SS(Row-S)',
              'TM',
              'TABLE LOCK',
              'PW',
              'TABLE LOCK',
              'TO',
              'TABLE LOCK',
              'TS',
              'TABLE LOCK',
              'RT',
              'ROW LOCK',
              'TX',
              'ROW LOCK',
              'MR',
              'S(Share)',
              NULL) 锁定方式,
       C.MACHINE 用户组,
       C.TERMINAL 机器名,
       B.OS_USER_NAME 系统用户名,
       B.PROCESS 系统进程id,
       DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,
       C.SERVER,
       C.SID,
       C.SERIAL#,
       C.PROGRAM 连接方式,
       C.LOGON_TIME
  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d
 WHERE (A.OBJECT_ID = B.OBJECT_ID)
   AND (B.PROCESS = C.PROCESS)
   and C.sid = d.sid
   and B.LOCKED_MODE = D.LMODE
 ORDER BY 1, 2;

[

5. 数据库追踪日志

在数据库全局做追踪

-- 开启数据库级追踪:
 alter system set events  '10046 trace name context forever, level 12';

-- 查询日志位置
SELECT s.sid,s.serial#, p.tracefile, s.sql_id
  FROM v$session s
  LEFT JOIN v$process p ON s.paddr = p.addr
 WHERE s.username = 'EASYTONG';

-- 关闭数据库级跟踪：
alter system set events '10046 trace name context off';

被阻塞的会话日志。阻塞的会话是inactive，没有产生日志

总结

A会话对am_account表产生了一个锁，交易的时候，B会话先锁了最大值表，然后等待A会话锁的释放。