定位行锁源头

有时候因为应用程序逻辑问题，导致数据库中出现大量长时间行锁等待，这个时候开发需要DBA快速找到源头，在紧急情况下立即进行KILL来解决问题。行锁问题的分析不单是找到源头的SESSION ID，还需要获取XID、LOCK TABLES‘S ROWID、及SQL_TEXT 来定位具体交易，或许还需要通过LOGMINER进行挖掘分析找到问题根源。下面这个SQL 只是定位行锁源头,并记录事务XID

找到源头SID 和 XID：

with lk as (
select A.SID asid,A.ID1 aid1,A.ID2 aid2,B.SID bsid ,
upper(replace(to_char(trunc(A.ID1/power(2,16)),'xxx'),' ','0'))||
upper(replace(to_char(mod(A.ID1,power(2,16)),'xxx'),' ','0'))||
upper(replace(to_char(A.ID2,'xxxxxxx'),' ','0')) blocking_xid
from v$lock a, v$lock b
 where a.id1=b.id1 and a.id2=b.id2
   and a.block = 1 and b.request > 0)
SELECT  lpad(' |__',(level-1)*8,' ')||BSID LOCKTREE,   xid    from (
 SELECT ASID,BSID, blocking_xid xid FROM LK  
 UNION ALL
 SELECT DISTINCT 0,ASID,  blocking_xid xid FROM LK WHERE ASID NOT IN (SELECT BSID FROM LK) )    
         start with ASID = '0'
connect by PRIOR BSID = ASID   

下面的SQL会记录非常多的信息：

 
WITH LK AS ( select  DT,     
                a_sid||'. '||DT  ASID,B_SID||'. '||DT  BSID,
                 substr(B_SESS_INFO, 1,instr(B_SESS_INFO, '|', 1)-1 ) B_sql,
                 substr(A_SESS_INFO, 1,instr(A_SESS_INFO, '|', 1)-1 ) A_sql,
                 SUBSTR(LOCK_ROWID, 1, instr(lock_rowid, ' ', 1)) b_TM,
                 substr(lock_rowid, instr(lock_rowid, '-', 1) +1,instr(lock_rowid, '|', 1) -instr(lock_rowid, '-', 1)-1) tname,
                 substr(lock_rowid, instr(lock_rowid, '|', 1) + 1) ID
              -- RANK() OVER(PARTITION BY DT ORDER BY DT)
          from (SELECT TO_CHAR (TRUNC (SYSDATE, 'MI'), 'yyyymmddhh24mi') AS dt,
       (SELECT rpad(sql_id,13,' ')||'|'||rpad(machine,10,' ')||'|'||rpad(osuser,10,' ')
          FROM v$session
         WHERE SID = a.SID) a_sess_info,
       (SELECT EVENT
          FROM v$session
         WHERE SID = a.SID) a_sess_waitevent,                           
       (SELECT username
          FROM v$session
         WHERE SID = a.SID) blocker, a.SID a_sid, 'is blocking' aa,
       (SELECT username
          FROM v$session
         WHERE SID = b.SID) blockee, b.SID b_sid,
       (SELECT rpad(sql_id,13,' ')||'|'||rpad(machine,10,' ')||'|'||rpad(osuser,10,' ')
          FROM v$session
         WHERE SID = b.SID) b_sess_info,
       (SELECT rpad(b.CTIME,6,' ')||'-'||OBJECT_NAME||'|'||dbms_rowid.rowid_create(1, c.data_object_id, ROW_WAIT_FILE#,
                               ROW_WAIT_BLOCK#,ROW_WAIT_ROW#)  
          FROM v$session S ,DBA_OBJECTS C
         WHERE SID = B.SID   
         and   c.object_id = S.row_wait_obj# AND C.OBJECT_TYPE LIKE 'TABLE%'
         ) lock_rowid,           
        (SELECT EVENT
          FROM v$session
         WHERE SID = b.SID) b_sess_waitevent  
  FROM v$lock a, v$lock b   
 WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2
   --AND B.CTIME >= 1
    )
         where  LOCK_ROWID IS NOT NULL ORDER BY DT DESC)
SELECT DT, lpad(' |__',(level-1)*8,' ')||BSID LOCKTREE, sql_id,tab,tm time,id row_id   from (
 SELECT ASID,BSID,DT,B_sql sql_id,tname tab,b_tm tm,id FROM LK  
 UNION ALL
 SELECT DISTINCT '0',ASID,DT,A_sql sql_id,null tab,null tm,null id  FROM LK WHERE ASID NOT IN (SELECT BSID FROM LK) )    
         start with ASID = '0'
connect by PRIOR BSID = ASID   
order siblings by DT