小脚本-检查deadlock

最新推荐文章于 2025-06-06 05:30:00 发布

weixin_34403693

最新推荐文章于 2025-06-06 05:30:00 发布

阅读量95

点赞数

CC 4.0 BY-SA版权

原文链接：http://blog.51cto.com/qhd2004/238644

本文介绍了一种通过自定义Shell脚本的方式查找Oracle数据库中出现的死锁情况，并提供了具体的SQL语句来定位造成死锁的会话及SQL文本。此外，还展示了如何找出被锁定的对象以及如何终止引起死锁的会话。

锁大家都有自己的认识了，我不敢多说什么，自己水平也不高，弄了点小东东。

首先我们先找到deadlock

[oracle@xn-test ~]$ cat find-lock.sh
#!/bin/bash
output=`sqlplus -s "/ as sysdba" << EOF
spool find-lock.alert
SELECT /*+ ORDERED USE_HASH(H,R) */
   H.SID HOLD_SID,
   R.SID WAIT_SID,
   decode(H.type,'MR', 'Media Recovery',
           'RT', 'Redo Thread',
           'UN', 'User Name',
           'TX', 'Transaction',
           'TM', 'DML',
           'UL', 'PL/SQL User Lock',
           'DX', 'Distributed Xaction',
           'CF', 'Control File',
           'IS', 'Instance State',
           'FS', 'File Set',
           'IR', 'Instance Recovery',
           'ST', 'Disk Space Transaction',
           'TS', 'Temp Segment',
           'IV', 'Library Cache Invalidation',
           'LS', 'Log Start or Switch',
           'RW', 'Row Wait',
           'SQ', 'Sequence Number',
           'TE', 'Extend Table',
           'TT', 'Temp Table',H.type) Type,
   decode(H.lmode,0, 'None', 1, 'Null',2, 'Row-S (SS)',   3, 'Row-X (SX)',
           4, 'Share',        5, 'S/Row-X (SSX)',
           6, 'Exclusive',    to_char(H.lmode)) hold,
   decode(r.request,         0, 'None',
           1, 'Null',         2, 'Row-S (SS)',
           3, 'Row-X (SX)',   4, 'Share',
           5, 'S/Row-X (SSX)',6, 'Exclusive',to_char(R.request)) request,R.ID1,R.ID2,R.CTIME
FROM V\\$LOCK H,V\\$LOCK R
WHERE H.BLOCK = 1 AND R.BLOCK=0
and H.TYPE <> 'MR' AND R.TYPE <> 'MR'
AND H.ID1 = R.ID1 AND H.ID2 = R.ID2;
spool off
exit
EOF
`
echo $output

[oracle@xn-test ~]$ cat find-lock.alert

HOLD_SID   WAIT_SID TYPE
---------- ---------- --------------------------
HOLD
----------------------------------------
REQUEST                                         ID1        ID2      CTIME
---------------------------------------- ---------- ---------- ----------
       158        138 Transaction
Exclusive
Exclusive                                    327699       1324        620

现在我们找到了锁的sid：158是现在占用，138是在等。

好了，下面我去找下发生锁的sql语句文本，记住这个WAIT_SID 。

SQL> select a.username,a.sid,a.serial#,c.sql_text from v$session a,v$sqltext c where c.hash_value=a.sql_hash_value and sid=138;

USERNAME SID SERIAL#
------------------------------ ---------- ----------
SQL_TEXT
----------------------------------------------------------------
SCOTT 138 2399
update test set sal=1234 where empno=7499

下面我们去找下发生锁的表

[oracle@xn-test ~]$ cat find-lock-table.sh
#!/bin/bash
output=`sqlplus -s "/ as sysdba" << EOF
spool find-lock-table.alert
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v\\$locked_object l,dba_objects o,v\\$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC;
spool off
exit
`
echo $output

[oracle@xn-test ~]$ cat find-lock-table.alert

USER_NAME
--------------------------------------------------------------------------------
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE                SID    SERIAL#
------------------- ---------- ----------
SCOTT
SCOTT
TEST
TABLE                      158      13483

USER_NAME
--------------------------------------------------------------------------------
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE                SID    SERIAL#
------------------- ---------- ----------
   SCOTT
SCOTT
TEST
TABLE                      138       2399

下面我们去找下xidusn。

[oracle@xn-test ~]$ cat find-lock-xidusn.
cat: find-lock-xidusn.: No such file or directory
[oracle@xn-test ~]$ cat find-lock-xidusn.sh
#!/bin/bash
output=`sqlplus -s "/ as sysdba" << EOF
spool find-lock-xidusn.alert
SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",
t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",
t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName
FROM v\\$session s,v\\$transaction t,v\\$rollname r
WHERE s.SADDR=t.SES_ADDR
AND t.XIDUSN=r.usn;
spool off
exit
`
echo $output

[oracle@xn-test ~]$ cat find-lock-xidusn.alert

USERNAME                              SID    SERIAL# UBA filenum
------------------------------ ---------- ---------- -----------
UBA Block number Number os undo Blocks Used START_TIME
---------------- -------------------------- --------------------
STATUS           START_SCNB     ROLLID ROLLNAME
---------------- ---------- ---------- ------------------------------
SCOTT                                 158      13483           2
              80                          1 12/04/09 14:30:27
ACTIVE              2575429          5 _SYSSMU5$

好了先到这吧。

欢迎大家补充。

找了后我们得解决：

select t2.username||'   '||t2.sid||'   '||t2.serial#||'   '||t2.logon_time||'   '||t3.sql_text
      from v$locked_object t1,v$session t2,v$sqltext t3
3        where t1.session_id=t2.sid
4        and t2.sql_address=t3.address
5        order by t2.logon_time;

T2.USERNAME||''||T2.SID||''||T2.SERIAL#||''||T2.LOGON_TIME||''||T3.SQL_TEXT
--------------------------------------------------------------------------------
SCOTT 138 2399 04-DEC-09 update test set sal=1234 where empno=7499

SQL> alter system kill session '138,2399';

System altered.

我们再看还有没有锁呢？没了吧

SQL> update test set sal=1234 where empno=7499;
update test set sal=1234 where empno=7499
*
ERROR at line 1:
ORA-00028: your session has been killed

转载于:https://blog.51cto.com/qhd2004/238644