有时候因为应用程序逻辑问题,导致数据库中出现大量长时间行锁等待,这个时候开发需要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