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);