oracle死锁的处理剖析.docx

1、oracle死锁的处理剖析查看有哪些表被锁住select b.owner,b.object_name,a.session_id,a.locked_modefrom v$locked_object a,dba_objects bwhere b.object_id = a.object_idselect b.username,b.sid,b.serial#,logon_timefrom v$locked_object a,v$session bwhere a.session_id = b.sid order by b.logon_time杀进程中的会话alter system kill sessi

2、on sid,serial#;e.galter system kill session 29,5497; 如果有ora-00031错误，则在后面加immediate;alter system kill session 29,5497 immediate；如何杀死oracle死锁进程.查哪个过程被锁:查V$DB_OBJECT_CACHE视图:SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER=过程的所属用户 AND CLOCKS!=0;2. 查是哪一个SID,通过SID可知道是哪个SESSION:查V$ACCESS视图:SELECT * FROM V$ACCE

3、SS WHERE OWNER=过程的所属用户 AND NAME=刚才查到的过程名;3. 查出SID和SERIAL#：查V$SESSION视图:SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID=刚才查到的SID;查V$PROCESS视图:SELECT SPID FROM V$PROCESS WHERE ADDR=刚才查到的PADDR;4. 杀进程：(1)先杀ORACLE进程:ALTER SYSTEM KILL SESSION 查出的SID,查出的SERIAL#;(2)再杀操作系统进程:KILL -9 刚才查出的SPID或ORAKILL 刚才查出的

4、SID 刚才查出的SPID。Oracle的死锁查询数据库死锁：select t2.username| |t2.sid|t2.serial#| |t2.logon_time|t3.sql_textfrom v$locked_object t1,v$session t2,v$sqltext t3where t1.session_id=t2.sidand t2.sql_address=t3.addressorder by t2.logon_time;查询出来的结果就是有死锁的session了，下面就是杀掉，拿到上面查询出来的SID和SERIAL#，填入到下面的语句中：alter system kil

5、l session sid,serial#;一般情况可以解决数据库存在的死锁了，或通过session id 查到对应的操作系统进程，在Unix中杀掉操作系统的进程。SELECT a.username,c.spid AS os_process_id,c.pidAS oracle_process_id FROM v$session a,v$process cWHERE c.addr=a.paddr and a.sid= and a.serial#= ;然后采用kill （unix） 或 orakill（windows ）。在Unix中：ps -ef|grep os_process_idkill -

6、9 os_process_idps -ef|grep os_process_id经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。1）查找死锁的进程：sqlplus /as sysdba (sys/change_on_install)SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESSFROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;2）kill掉这个死锁的进程：alt

7、er system kill session sid,serial#; （其中sid=l.session_id）3）如果还不能解决：select pro.spid from v$session ses,v$process pro where ses.sid=XX andses.paddr=pro.addr;其中sid用死锁的sid替换:exitps -ef|grep spid其中spid是这个进程的进程号，kill掉这个Oracle进程。 kill -9 10720（spid）本篇文章来源于：开发学院 原文链接：查找锁定对象的会话信息SELECT dob.OBJECT_NAME Table_N

8、ame,lo.SESSION_ID|, |vss.SERIAL# 删除号,lo.locked_mode,lo.SESSION_ID, vss.SERIAL#,vss.action Action,vss.osuser OSUSER, vss.LOGON_TIME,vss.process AP_PID, VPS.SPID DB_PID ,vss.*From v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPSWhere lo.OBJECT_ID = dob.OBJECT_IDand lo.SESSION_ID = vss

9、.SIDAND VSS.paddr = VPS.addrorder by 2,3,DOB.object_name;如有需要删除会话：ALTER system kill session 删除号;如：ALTER system kill session 294,1747 ;另一个查找死锁的脚本：SELECT bs.username Blocking User, bs.username DB User, ws.username Waiting User, bs.SID SID, ws.SID WSID, bs.serial# Serial#, bs.sql_address address, bs.sq

10、l_hash_value Sql hash, bs.program Blocking App, ws.program Waiting App, bs.machine Blocking Machine, ws.machine Waiting Machine, bs.osuser Blocking OS User, ws.osuser Waiting OS User, bs.serial# Serial#, ws.serial# WSerial#, DECODE (wk.TYPE, MR, Media Recovery, RT, Redo Thread, UN, USER Name, TX, Tr

11、ansaction, 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

12、, Temp TABLE, wk.TYPE ) lock_type, DECODE (hk.lmode, 0, None, 1, NULL, 2, ROW-S (SS), 3, ROW-X (SX), 4, SHARE, 5, S/ROW-X (SSX), 6, EXCLUSIVE, TO_CHAR (hk.lmode) ) mode_held, DECODE (wk.request, 0, None, 1, NULL, 2, ROW-S (SS), 3, ROW-X (SX), 4, SHARE, 5, S/ROW-X (SSX), 6, EXCLUSIVE, TO_CHAR (wk.req

13、uest) ) mode_requested, TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2, DECODE (hk.BLOCK, 0, NOT Blocking, /*/* Not blocking any other processes */ 1, Blocking, /*/* This lock blocks other processes */ 2, Global, /*/* This lock is global, so we cant tell */ TO_CHAR (hk.BLOCK) ) blocking_others

14、 FROM v$lock hk, v$session bs, v$lock wk, v$session ws WHERE hk.BLOCK = 1 AND hk.lmode != 0 AND hk.lmode != 1 AND wk.request != 0 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 AND wk.id2(+) = hk.id2 AND hk.SID = bs.SID(+) AND wk.SID = ws.SID(+) AND (bs.username IS NOT NULL) AND (bs.username SYSTEM) AND (bs.username SYS) ORDER BY 1;查询锁等待的内容：wait_lock.sqlselect (select username from v$session where sid = a.sid) username, a.sid,