Oracle 死锁的检测查询及处理

浏览: 2624
死锁是数据库经常发生的问题,数据库一般不会无缘无故产生死锁,死锁通常都是由于我们应用程序的设计本身造成的。
关于数据库死锁的检查方法
一、数据库死锁的现象
程序在执行的过程中,点击确定或保存按钮,程序没有响应,也没有出现报错。

二、死锁的原理
当对于数据库某个表的某一列做更新或删除等操作,执行完毕后该条语句不提交,另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态,此时的现象是这条语句一直在执行,但一直没有执行成功,也没有报错。

三、死锁的定位方法
通过检查数据库表,能够检查出是哪一条语句被死锁,产生死锁的机器是哪一台。
1)用dba用户执行以下语句
select username,lockwait,status,machine,program from v$session where sid
in (select session_id from v$locked_object)
如果有输出的结果,则说明有死锁,且能看到死锁的机器是哪一台。字段说明:
Username:死锁语句所用的数据库用户;
Lockwait:死锁的状态,如果有内容表示被死锁。
Status: 状态,active表示被死锁
Machine: 死锁语句所在的机器。
Program: 产生死锁的语句主要来自哪个应用程序。

2)用dba用户执行以下语句,可以查看到被死锁的语句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

四、死锁的解决方法
一般情况下,只要将产生死锁的语句提交就可以了,但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
 经常在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.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S
WHERE l.SESSION_ID=S.SID;
2)kill掉这个死锁的进程:
 alter system kill session ‘sid,serial#’; (其中sid=l.session_id)
3)如果还不能解决:
select pro.spid from v$session ses, v$process pro where ses.sid=XX and ses.paddr=pro.addr;
其中sid用死锁的sid替换:
exit ps -ef|grep spid
其中spid是这个进程的进程号,kill掉这个Oracle进程。



死锁查询语句及查询发生死锁的select语句
-- 死锁查询语句
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.sql_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', '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',
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.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /*[i]//[/i] Not blocking any other processes */
1, 'Blocking', /*[i]//[/i] This lock blocks other processes */
2, 'Global', /*[i]//[/i] This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
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;

查询发生死锁的select语句
select sql_text from v$sql where hash_value in (
select sql_hash_value from v$session where sid in (select session_id from v$locked_object)
)



其他死锁常规的解决办法:
1)执行下面SQL,先查看哪些表被锁住了: 
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;

2)查处引起死锁的会话
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
这里会列出SID

3) 查出SID和SERIAL#:
查V$SESSION视图:
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID';
这一步将得到PADDR

4)查V$PROCESS视图:
SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
这一步得到SPID

5)杀死进程
(1)在数据库中,杀掉ORACLE进程:
ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';

(2)如果在ORACLE中不能杀死进程,我们只能到操作系统中,使用操作系统命令杀死进程
KILL -9 “刚才查出的SPID”
在WINDOWS平台,可以是那个orakill。

推荐 1
本文由 梁勇 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

2 个评论

梁勇

梁勇 天道酬勤、上善若水。爱好商业智能

模型死锁语句:1。模拟死锁

1。1。主表

-- Create table
create table WDZ1
(
WDZ1ID NUMBER not null,
MEMO VARCHAR2(20)
)
;
alter table WDZ1
add constraint XXXXXX primary key (WDZ1ID);

1。2。从表(没有外健的索引)


-- Create table
create table WDZ2
(
WDZ2ID NUMBER not null,
WDZ1ID NUMBER,
MEMO VARCHAR2(20)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table WDZ2
add constraint XXXXX primary key (WDZ2ID)
;
alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID);
1。3。插入数据表到住表

begin
insert into wdz1 values (1,'aa');
insert into wdz1 values(2,'aa2');
insert into wdz1 values (3,'aa3');

insert into wdz2 values(10,3,'wdz3--1');
commit;
end;

1。4。在一个数据库seeesion里面插入数到从表,但是不提交事务

begin
update wdz2 set memo='update wdz2 momo'

where wdz2id=10;

insert into wdz2 values(20,2,'wdz2--1');

end;


对从表进行插入/修改记录,施加的锁也就是行级锁

1。5。在另外一个数据库seeesion里面删除 主表数据

delete from wdz1 where wdz1id=1

这时候 程序会死锁,除非 上面的 对从表的 数据操作提交事务或者回滚事务。
梁勇

梁勇 天道酬勤、上善若水。爱好商业智能

oracle 死锁处理 http://www.cnblogs.com/BetterWF/archive/2012/09/19/2693888.html

Oracle死锁查询及处理 http://blog.csdn.net/rznice/article/details/6683905/

要回复文章请先登录注册