死鎖(ora-00060)以及死鎖相關的知識點
最近碰到一個死鎖的問題:ora-00060 deadlock detected while waiting for resource (ora-00060 等待資源時檢測到死鎖)
檢視udump(SQL> show parameter USER_DUMP_DEST; 檢視該目錄)下面的trace,發現如下日誌:
*** 2009-08-13 10:53:11.656
*** SERVICE NAME:(his3) 2009-08-13 10:53:11.593
*** SESSION ID:(130.3437) 2009-08-13 10:53:11.593
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
UPDATE MY_KUCUN1 SET KUCUNSL = KUCUNSL - :B3 WHERE YINGYONGID = :B2 AND JIAGEID = :B1
----- PL/SQL Call Stack -----
object line object
handle number name
33DF6A44 6002 package body HIS3KS.PKG_MY_JINXIAOCUN
2FD11B48 1 anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000b001c-00005019 38 130 X 19 106 X
TX-00010025-000100ee 19 106 X 38 130 X
session 130: DID 0001-0026-00008297 session 106: DID 0001-0013-00004EB8
session 106: DID 0001-0013-00004EB8 session 130: DID 0001-0026-00008297
Rows waited on:
Session 106: obj - rowid = 0000E1AB - AAAOGrAAXAAAJHQAAT
(dictionary objn - 57771, file - 23, block - 37328, slot - 19)
Session 130: obj - rowid = 0000E1AB - AAAOGrAAXAAAJHQAAZ
(dictionary objn - 57771, file - 23, block - 37328, slot - 25)
Information on the OTHER waiting sessions:
Session 106:
pid=19 serial=671 audsid=309881 user: 64/HIS3KS
O/S info: user: NT AUTHORITY/ANONYMOUS LOGON, term: MEDIINFO-QA2, ospid: 6708:6612, machine: WORKGROUP/MEDIINFO-QA2
program: cicsas.exe
application name: cicsas.exe, hash value=0
Current SQL Statement:
UPDATE MY_KUCUN1 SET KUCUNSL = KUCUNSL - :B3 WHERE YINGYONGID = :B2 AND JIAGEID = :B1
End of information on OTHER waiting sessions.
===================================================
幾個重要的資訊就是鎖的型別是X,說明是DML語句導致的行級排他鎖,涉及到兩個Session:130和106。130就是報錯ora-00060的Session。
下面是兩個Session涉及到的物件ID,三種顏色其實代表的都是同樣的意思。0000E1AB的10進位制就是57771
SQL> select dbms_rowid.rowid_object( 'AAAOGrAAXAAAJHQAAT' ) from dual;
DBMS_ROWID.ROWID_OBJECT('AAAOGrAAXAAAJHQAAT')
------------------------------
57771
SQL> select dbms_rowid.rowid_object( 'AAAOGrAAXAAAJHQAAZ' ) from dual;
DBMS_ROWID.ROWID_OBJECT('AAAOGrAAXAAAJHQAAZ')
------------------------------
57771
查詢物件:
SQL> SELECT t.owner,t.object_name,t.object_type FROM all_objects t WHERE t.data_object_id = 57771
以上就是該日誌檔案能夠提供的內容,可以從中分析在那個程式碼裡面產生的問題,下面是關於死鎖的知識:
1. 產生死鎖的典型原理:
session A locks row X
sesssion B locks row Y
session A tries to lock row Y
session B tries to lock row X
Session A fails after roughly 3 seconds with a deadlock ORA-00060
2.死鎖產生的二大原因
a. unindexed foreign keys in a system that issues a delete against the parent table OR updates the
parent primary key.
b. use of bitmap indexes on tables that are modifed "in real time".
the next big one:
c. application design flaw
the last, mostly rare - but can happen
d. itl deadlocks, undersized initrans
3. 死鎖產生後,其中一個佔用資源會被Oracle自動釋放,例如:
just set up a table with three rows (x=1,2,3)
in 3 sessions - update 1 and then 2 and then 3 (one in each session)
have session 3 update 1
have session 2 update 3
have session 1 update 2
one of them will be chosen as the deadlock victim and their STATEMENT (but not transaction) will be
rolled back, they have to figure out whether to go forward and eventually commit or rollback the
transaction, releasing one of the other sessions.
That means multiple way deadlocks are resolved by killing the transactions by oracle as if
deadlocks are between two transactions until all the deadlocks are cleared.
but in a distributed environment, things are different (the locks time out instead).
4. session deadlock itself 單個session也可以產生死鎖
create table t ( x int primary key );
insert into t values ( 1 );
declare
pragma autonomous_transaction;
begin
insert into t values ( 1 );
commit;
end;
/
that'll do it. all you need is two transactions and you have that ability.
參考資料:
http://**/viewthread.php?tid=140645
http://it.toolbox.com/blogs/confessions/how-to-use-oracle-25-understanding-deadlocks-17029
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4144238660486
http://asktom.oracle.com/pls/asktom/f?p=100:11:8973480827671230::::P11_QUESTION_ID:1068032649872
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24558279/viewspace-745916/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL死鎖相關SQL
- 關於處理死鎖的一點小知識
- Oracle死鎖一例(ORA-00060),鎖表導致的業務死鎖問題Oracle
- 剖析6個MySQL死鎖案例的原因以及死鎖預防策略MySql
- ORA-00060死鎖的產生及解決
- SQ死鎖及死鎖的解決
- Java鎖——死鎖Java
- 作業系統(5) 死鎖的概念 死鎖產生的必要條件 死鎖的處理策略 預防死鎖 避免死鎖 死鎖的檢測和解除 銀行家演算法作業系統演算法
- 面試官:什麼是死鎖?怎麼排查死鎖?怎麼避免死鎖?面試
- 什麼是死鎖?如何解決死鎖?
- 殺死Oracle死鎖程式Oracle
- 作業系統知識回顧(4)-死鎖作業系統
- MySQL 行級鎖的使用以及死鎖的預防MySql
- 鎖相關基礎知識
- 死鎖分析
- oracle 死鎖Oracle
- Java高階知識點:平行計算(外部排序) 及 死鎖分析Java排序
- 檢視oracle死鎖程式並結束死鎖Oracle
- 例項詳解 Java 死鎖與破解死鎖Java
- MySQL死鎖系列-線上死鎖問題排查思路MySql
- MySQL 死鎖和鎖等待MySql
- MySQL:一個死鎖分析 (未分析出來的死鎖)MySql
- 關於oracle死鎖的模擬Oracle
- 【死鎖】ORA-00060: deadlock detected while waiting for resourceWhileAI
- 測試庫死鎖診斷DEADLOCK DETECTED ( ORA-00060 )
- SQLServer的死鎖分析(1):頁鎖SQLServer
- Java 中的死鎖Java
- 遭遇ITL死鎖
- GCD 死鎖原因GC
- 死鎖案例分析
- HashMap死鎖分析HashMap
- SQL Server死鎖SQLServer
- 關於Oracle死鎖處理方法Oracle
- Oracle死鎖的檢視以及解決辦法Oracle
- 殺掉鎖死的程式
- mysql行鎖和死鎖檢測MySql
- Mysql 兩階段鎖和死鎖MySql
- 和外來鍵相關的阻塞和死鎖問題總結