oracle死锁测试与解决

本文不对死锁做理论性的解释，只从实验角度阐述死锁的现象并提供死锁的一种解决方案
实验环境为
[root@192 ~]# uname -a
Linux 192.168.151.141 2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:02 EDT 2007 i686 i686 i386 GNU/Linux
[root@192 ~]# su - oracle
[oracle@192 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 6 11:24:36 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS>
切换到test用户
SYS>conn test
Enter password:
Connected.
TEST>
建表students
create table students (id number,name varchar2(10));
插入数据
TEST>insert into students values(1,'andy');

1 row created.

TEST>insert into students values(2,'wang');

1 row created.
TEST>commit;

Commit complete.
查看数据
TEST>select * from students;

    ID NAME
---------- ----------
     1 andy
     2 wang

session 1
TEST>update students set name='a' where id=1;

1 row updated.
另开一窗口
session 2
查看数据
TEST>select * from students;

    ID NAME
---------- ----------
     1 andy
     2 wang
TEST>update students set name='b' where id=2;

1 row updated.
session 1
TEST>update students set name='c' where id=2;
挂起，hange住了
session 2
TEST>update students set name='d' where id=1;
挂起，hange住了
这时看看session 1
update students set name='c' where id=2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
说明检测到死锁。
如果不做处理，session 2会一直挂起，直到拿到资源为止。
此时，可以将session 1 commit
TEST>commit;

Commit complete.
从session 1的窗口查看

TEST>select * from students;

    ID NAME
---------- ----------
     1 a
     2 wang

TEST>
结果显示，session 1 commit后，只提交了对id=1的update，对id=2的update，oracle自动回滚。
看session 2的窗口，id=2的update已经执行了
TEST>update students set name='d' where id=1;

1 row updated.
查看结果
TEST>select * from students;

    ID NAME
---------- ----------
     1 d
     2 b
session 2的两条数据都成功执行。因为session 2没有commit,所以session 1只能读到undo段中的数据
session 1
TEST>select * from students;

    ID NAME
---------- ----------
     1 a
     2 wang

TEST>
将session 2 commit
session 2
TEST>commit;

Commit complete.
这时查看session 1，已经和session 2同步了
session 1
TEST>select * from students;

    ID NAME
---------- ----------
     1 d
     2 b
接下来，将说说死锁的解决
当出现死锁处理无限等待的时候，DBA要人工进行干预，这时只有付出一点代价了，牺牲一个session来解除死锁。
1 查看死锁状态
select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object);
处于ACTIVE状态，说明有死锁
2 查看死锁语句
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));
3 查看死锁session_id,serial#
 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;
4 kill掉死锁进程
alter system kill session 'sid,serial#'; (sid=l.session_id)
如果还不行，只有用os命令kill掉spid
查看死锁spid
select pro.spid from v$session se,v$process pro where se.sid=l.session_id and se.paddr=pro.addr;
kill -9 spid

通过下面的语句可以准确定位出现死锁的session id
select username,status,sid,serial# from v$session where sid in (select session_id from v$locked_object);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27181165/viewspace-1062508/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27181165/viewspace-1062508/