文章內(nèi)容

一個(gè)常見(jiàn)的ORA-00060死鎖現(xiàn)象

發(fā)布時(shí)間: 2012/9/6 18:01:56

在Oracle數(shù)據(jù)庫(kù)中如果出現(xiàn)死鎖現(xiàn)象，數(shù)據(jù)庫(kù)就會(huì)報(bào)出ORA-00060的錯(cuò)誤代號(hào)，這種死鎖現(xiàn)象通常都是應(yīng)用邏輯設(shè)計(jì)出錯(cuò)導(dǎo)致的異常，和oracle數(shù)據(jù)庫(kù)本身的設(shè)計(jì)無(wú)關(guān)，現(xiàn)在通過(guò)實(shí)驗(yàn)?zāi)M一個(gè)死鎖現(xiàn)象

打開(kāi)兩個(gè)會(huì)話執(zhí)行下列更新順序

會(huì)話1：執(zhí)行對(duì)employee_id為198的字段更新
HR@prod>update employees set first_name = 'cj' where employee_id = 198;-

1 row updated.

會(huì)話2：執(zhí)行對(duì)employee_id為200的字段更新
HR@prod>update employees set first_name = 'hh' where employee_id = 200;

1 row updated.

會(huì)話1：再執(zhí)行對(duì)employee_id為200的字段更新，此時(shí)語(yǔ)句已經(jīng)hang住，需要等待會(huì)話2發(fā)出commit或rollback動(dòng)作。
HR@prod>update employees set first_name = 'cj' where employee_id = 200;

會(huì)話2：一旦執(zhí)行更新，會(huì)話1就會(huì)馬上報(bào)錯(cuò)。
HR@prod>update employees set first_name = 'sdf' where employee_id = 198;

update employees set first_name = 'cj' where employee_id = 200
         *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

會(huì)話2仍然hang住，查詢alert日志發(fā)現(xiàn)報(bào)錯(cuò)：
ORA-00060: Deadlock detected. More info in file /u01/app/Oracle/admin/prod/udump/prod_ora_4273.trc.

通過(guò)dba_blockers表中的HOLDING_SESSION字段可以查詢到hang住會(huì)話的ID
SYS@prod>select * from dba_blockers;

HOLDING_SESSION
---------------
            159

使用v$session視圖獲取hang住會(huì)話的sid和serial#
SYS@prod>select sid,serial#,username from v$session where sid in
2 (select blocking_session from v$session);

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       159          5 HR

找到hang住的會(huì)話后，執(zhí)行alter system命令kill掉相應(yīng)的session就可以了：
SYS@prod>alter system kill session '159,5' immediate;

System altered.

執(zhí)行后會(huì)話1中的會(huì)話會(huì)自動(dòng)被kill掉
會(huì)話1：
HR@prod>select employee_id,first_name from employees where rownum
select employee_id,first_name from employees where rownum
         *
ERROR at line 1:
ORA-03135: connection lost contact

會(huì)話2中執(zhí)行查詢發(fā)現(xiàn)會(huì)話2的更改生效。
HR@prod>select employee_id,first_name from employees where rownum

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        198 sdf
        199 Douglas
        200 hh
        201 Michael
        202 Pat
        203 Susan
        204 Hermann
        205 Shelley
        206 William
        100 Steven

10 rows selected.
實(shí)際上，當(dāng)出現(xiàn)死鎖的情況，Oracle也會(huì)在一段時(shí)間后解鎖。這種情況會(huì)在alert日志中記載下列信息：
ORA-00060: Deadlock detected. More info in file /u01/app/Oracle/admin/ORCL/udump/orcl_ora_3173.trc.