在Oracle數(shù)據(jù)庫中如果出現(xiàn)死鎖現(xiàn)象,數(shù)據(jù)庫就會報出ORA-00060的錯誤代號,這種死鎖現(xiàn)象通常都是應(yīng)用邏輯設(shè)計出錯導致的異常,和oracle數(shù)據(jù)庫本身的設(shè)計無關(guān),現(xiàn)在通過實驗?zāi)M一個死鎖現(xiàn)象
打開兩個會話執(zhí)行下列更新順序
會話1:執(zhí)行對employee_id為198的字段更新
HR@prod>update employees set first_name = 'cj' where employee_id = 198;-
1 row updated.
會話2:執(zhí)行對employee_id為200的字段更新
HR@prod>update employees set first_name = 'hh' where employee_id = 200;
1 row updated.
會話1:再執(zhí)行對employee_id為200的字段更新,此時語句已經(jīng)hang住,需要等待會話2發(fā)出commit或rollback動作。
HR@prod>update employees set first_name = 'cj' where employee_id = 200;
會話2:一旦執(zhí)行更新,會話1就會馬上報錯。
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
會話2仍然hang住,查詢alert日志發(fā)現(xiàn)報錯:
ORA-00060: Deadlock detected. More info in file /u01/app/Oracle/admin/prod/udump/prod_ora_4273.trc.
通過dba_blockers表中的HOLDING_SESSION字段可以查詢到hang住會話的ID
SYS@prod>select * from dba_blockers;
HOLDING_SESSION
---------------
159
使用v$session視圖獲取hang住會話的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住的會話后,執(zhí)行alter system命令kill掉相應(yīng)的session就可以了:
SYS@prod>alter system kill session '159,5' immediate;
System altered.
執(zhí)行后會話1中的會話會自動被kill掉
會話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
會話2中執(zhí)行查詢發(fā)現(xiàn)會話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.
實際上,當出現(xiàn)死鎖的情況,Oracle也會在一段時間后解鎖。這種情況會在alert日志中記載下列信息:
ORA-00060: Deadlock detected. More info in file /u01/app/Oracle/admin/ORCL/udump/orcl_ora_3173.trc. 本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國五強!虛擬主機域名注冊頂級提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|