本文不对死锁做理论性的解释,只从实验角度阐述死锁的现象并提供死锁的一种解决方案
实验环境为
[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);
实验环境为
[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/