sqlserver数据库死锁解决大全.doc

查询处理死锁会话的sql语句 系统管理技术 2009-11-17 15:38:49 阅读123 评论0 字号：大中小 一直以来自己都想写一个能够查询系统中有死锁的会话的语句，查到后杀掉会话， select(select username from v$session where sid=a.sid) blocker,??? a.sid, is blocking,?(select username from v$session where sid=b.sid) blockee,b.sid??? from v$lock a,v$lock b??? where a.block=1 and b.request0?and a.id1=b.id1 and a.id2=b.id2 查询哪些session阻塞了哪些其他进程. BLOCKER?????????? SID ISBLOCKING BLOCKEE?????????????????????????????? SID SYS??????????????? 46 is blocking SYS??????????????????????????????????? 36SYS??????????????? 46 is blocking SYS??????????????????????????????????? 49 这个查的不是死锁,,只是查询哪些session阻塞了哪些其他进程 select * from v$sqltext? ????? where address in (????? select sql_address from v$session where sid in (????? select session_id from v$locked_object)) 这个只查询什么语句阻塞 然后自己写了个语句，该语句可以查询哪个用户的哪个对象，会话的sid，serial以及阻塞的语句，还是很全面的。 col owner format a10;col oracle_username format a10;col object_type format a10;select a.owner,a.object_name,a.object_type,b.oracle_username,cess,c.sid,c.serial#,d.sql_text from dba_objects a,v$locked_object b,v$session c,v$sqltext dwhere a.object_id=b.object_id and b.session_id=c.sid and c.sql_address=d.address; OWNER????? OBJECT_NAM OBJECT_TYP ORACLE_USE PROCESS???????????? SID??? SERIAL# SQL_TEXTSYS??????? T1???????? TABLE????? SYS??????? 3768:3772??????????? 36???????? 11update t1 set id =15 where id=1 SYS??????? T1???????? TABLE????? SYS??????? 4008:2660??????????? 49???????? 21update t1 set id =30 where id=2 ? 另外一篇文章处理死锁会话的sql 翻了一下以前的一些文档, 发现保存着查询oracle 死锁的sql 语句, 这是以前的一个前辈教给我的, 拿出来贴贴--查询死锁SQLSELECT /*+ rule */ s.username, decode(l.type,TM,TABLE LOCK, TX,ROW LOCK, NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,gram,s.osuser from v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL ;?--杀死死锁的session, 释放死锁alter system kill session 1055,621;? -- 第一个参数是 sid, 第二个参数是 serial#可能有很多的死锁, 但是释放了前面的几个, 可能后面的锁