死鎖的概念
什么是死鎖呢? 其實(shí)我們生活中也有很多類似死鎖的例子。 我先舉一個(gè)生活中的例子:過年回家,父親買了一把水彈槍,兒子和侄子爭(zhēng)搶著要先玩,誰也不讓誰,拆開包裝后,一個(gè)搶了槍, 一個(gè)逮住了子彈和彈夾。兩個(gè)都爭(zhēng)著要先玩,但是都互不相讓。結(jié)果兩個(gè)人都玩不了。如果兒子要先玩,就必須讓侄子把子彈和彈夾給他,如果侄子要先玩,就必須讓兒子把槍給侄子。他們就這樣對(duì)峙了十幾分鐘,互不相讓。 我出來調(diào)停,讓兒子把槍先給侄子玩,每個(gè)人玩十分鐘。然后兩個(gè)人開開心心一起玩起來。其實(shí)這就是一個(gè)活生生的死鎖(Dead Lock)的例子。
我們?cè)賮砜纯磾?shù)據(jù)庫死鎖的概念, 所謂死鎖,是指兩個(gè)會(huì)話,每個(gè)會(huì)話都持有另外一個(gè)會(huì)話想要的資源,因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象,此時(shí)就會(huì)出現(xiàn)死鎖,若無外力作用,它們都將無法推進(jìn)下去。此時(shí)稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖,這些永遠(yuǎn)在互相等待的進(jìn)程稱為死鎖進(jìn)程。Oracle對(duì)于“死鎖”采取的策略是回滾其中一個(gè)事務(wù),讓另外一個(gè)事務(wù)順利進(jìn)行。
英文關(guān)于deadlock的概念如下:
A deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved but the idea is the same.
死鎖的模擬
上面了解了死鎖的概念,接下來,我們先人工構(gòu)造一個(gè)簡(jiǎn)單的死鎖(Dead Lock)案例來加深理解一下死鎖(Dead Lock),如下所示,我們先準(zhǔn)備測(cè)試案例使用的表和數(shù)據(jù),測(cè)試環(huán)境為Oracle Database 10g Release 10.2.0.5.0
SQL> create table dead_lock_test( id number(10), name varchar2(32));
Table created.
SQL> insert into dead_lock_test values(101, 'kerry');
1 row created.
SQL> insert into dead_lock_test values(102, 'ken');
1 row created.
SQL> commit;
Commit complete.
SQL>
在會(huì)話1(SID為788)中執(zhí)行下面SQL語句:
SQL> show user;
USER is "TEST"
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
788 0 1
SQL> update dead_lock_test set name='kerry1_101' where id=101;
1 row updated.
SQL>
然后在會(huì)話2(SID為770)中執(zhí)行下面SQL語句:
SQL> show user;
USER 為 "TEST"
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
770 0 1
SQL> update dead_lock_test set name='kerry2_102' where id=102;
已更新 1 行。
SQL> update dead_lock_test set name='kerry2_101' where id=101;
如上所示,會(huì)話2(SID為770)更新id=101這條記錄時(shí),會(huì)話被阻塞了。然后我們?cè)跁?huì)話1(SID為788)中執(zhí)行下面SQL語句:
SQL> update dead_lock_test set name='kerry1_102' where id=102;
此時(shí)你會(huì)立馬看到會(huì)話2(SID為770)出現(xiàn)ORA-00060錯(cuò)誤,如下所示:
如果對(duì)上面的操作過程的流程有點(diǎn)不直觀,那么可以參下面表格:
當(dāng)然,如果你以下面這樣的順序更新,那么會(huì)話1就會(huì)出現(xiàn)ORA-0060的錯(cuò)誤,會(huì)話1會(huì)被當(dāng)做犧牲的會(huì)話進(jìn)行回滾。
此時(shí)在告警日志中就會(huì)出現(xiàn)trc文件。注意RAC環(huán)境和單機(jī)環(huán)境稍有不同。在RAC環(huán)境中,是由LMD(Lock Manager Daemon)進(jìn)程統(tǒng)一管理各個(gè)節(jié)點(diǎn)之間的鎖資源的,所以,RAC環(huán)境中trace文件是由LMD進(jìn)程來生成的。
Tue Mar 28 15:36:30 CST 2017
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/SCM2/bdump/scm2_s000_15815.trc
trace文件的部分內(nèi)容如下所示:
*** 2017-03-28 15:36:30.917
*** ACTION NAME:() 2017-03-28 15:36:30.917
*** MODULE NAME:(SQL*Plus) 2017-03-28 15:36:30.917
*** SERVICE NAME:(SCM2) 2017-03-28 15:36:30.917
*** SESSION ID:(770.8) 2017-03-28 15:36:30.917
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
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-0006002e-001e409f 15 770 X 16 788 X
TX-0007002c-001f6346 16 788 X 15 770 X
session 770: DID 0001-0010-00000002 session 788: DID 0001-000F-00000001
session 788: DID 0001-000F-00000001 session 770: DID 0001-0010-00000002
Rows waited on:
Session 788: obj - rowid = 00094900 - AACUkAABEAACLUeAAB
(dictionary objn - 608512, file - 68, block - 570654, slot - 1)
Session 770: obj - rowid = 00094900 - AACUkAABEAACLUeAAA
(dictionary objn - 608512, file - 68, block - 570654, slot - 0)
Information on the OTHER waiting sessions:
Session 788:
sid: 788 ser: 9 audsid: 201878652 user: 132/TEST
flags: (0xe1) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x8)
pid: 16 O/S info: user: oracle, term: UNKNOWN, ospid: 15817
image: oracle@getlnx14uat.xxxx.com (S001)
O/S info: user: oracle, term: pts/2, ospid: 23047, machine: DB-Server.localdomain
program: sqlplus@DB-Server.localdomain (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update dead_lock_test set name='kerry1_102' where id=102
End of information on OTHER waiting sessions.
Current SQL statement for this session:
update dead_lock_test set name='kerry2_101' where id=101
===================================================
死鎖的檢測(cè)
關(guān)于死鎖的檢測(cè),對(duì)于單實(shí)例來說,基本上秒級(jí)完成,對(duì)于RAC環(huán)境,Oracle 10g基本上是1分鐘, Oracle 11g是10秒,這個(gè)是通過隱含參數(shù)_lm_dd_interval控制的。這個(gè)參數(shù)可以修改,但是不建議修改。
COL NAME FOR A32;
COL KSPPDESC FOR A32;
COL KSPPSTVL FOR A32;
SELECT A.INDX,
A.KSPPINM NAME,
A.KSPPDESC,
B.KSPPSTVL
FROM X$KSPPI A,
X$KSPPCV B
WHERE A.INDX = B.INDX
AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%');
死鎖的分析(DeadLock Troubleshooting)
以上面的例子來說,數(shù)據(jù)庫一旦出現(xiàn)死鎖,立馬會(huì)在告警日志里面生成這樣一條記錄“ORA-00060: Deadlock detected. More info in file xxxxx”,那么從trc文件能分析出什么信息呢? 下面我們以上面的例子來簡(jiǎn)單分析一下
其實(shí)trc文件里面最重要、最有用的信息是Deadlock graph。從這部分,我們可以分析得到下面一些有用信息:
1: 產(chǎn)生死鎖的兩個(gè)會(huì)話信息
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0006002e-001e409f 15 770 X 16 788 X
TX-0007002c-001f6346 16 788 X 15 770 X
session 770: DID 0001-0010-00000002 session 788: DID 0001-000F-00000001
session 788: DID 0001-000F-00000001 session 770: DID 0001-0010-00000002
從上面可以看到Blocker(s)與Waiter(s)的相關(guān)信息
Resource Name : 被持有或等待的鎖資源名字
鎖資源名字由三部分組成 Type-ID1-ID2,ID1和ID2代表的意思由鎖類型決定。
具體可以參考v$lock_type
process : V$PROCESS.PID
session : V$SESSION.SID
holds : 鎖持有的模式(Mode the lock is held in)
waits : 鎖等待的模式(Mode the lock is requested in (waiting for))
解讀以上死鎖的案例:
SID 770 (Process 15) 以排它模式持有鎖:TX-0006002e-001e409f ,以排它模式請(qǐng)求鎖:TX-0007002c-001f6346。
SID 788 (Process 16) 以排它模式持有鎖:TX-0007002c-001f6346 ,以排它模式請(qǐng)求鎖:TX-0006002e-001e409f。
這一段可以看到,778 阻塞了770, 然后770又阻塞了778 剛好構(gòu)成了死鎖的條件。這里要看生成的記錄是兩行還是一行,是TX還是TM,如果只有一行那么說明是同一個(gè)SESSION,可能是自治事務(wù)引起的死鎖。
2:死鎖發(fā)生在那個(gè)對(duì)象?
Rows waited on:
Session 788: obj - rowid = 00094900 - AACUkAABEAACLUeAAB
(dictionary objn - 608512, file - 68, block - 570654, slot - 1)
Session 770: obj - rowid = 00094900 - AACUkAABEAACLUeAAA
(dictionary objn - 608512, file - 68, block - 570654, slot - 0)
3:會(huì)話的的機(jī)器、應(yīng)用程序等信息
Session 788:
sid: 788 ser: 9 audsid: 201878652 user: 132/TEST
flags: (0xe1) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x8)
pid: 16 O/S info: user: oracle, term: UNKNOWN, ospid: 15817
image: oracle@xxxxxx.xxxx.com (S001)
O/S info: user: oracle, term: pts/2, ospid: 23047, machine: DB-Server.localdomain
program: sqlplus@DB-Server.localdomain (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update dead_lock_test set name='kerry1_102' where id=102
End of information on OTHER waiting sessions.
Current SQL statement for this session:
update dead_lock_test set name='kerry2_101' where id=101
從上面我們可以看到會(huì)話788是從機(jī)器DB-Server.localdomain上的SQL*Plus應(yīng)用程序發(fā)出的SQL,如果是正式環(huán)境,你會(huì)看到相關(guān)的機(jī)器和應(yīng)用程序名稱。這個(gè)會(huì)話最后執(zhí)行的SQL語句為update dead_lock_test set name='kerry1_102' where id=102 。
另外一個(gè)會(huì)話執(zhí)行的最后語句為update dead_lock_test set name='kerry2_101' where id=101,但是如何找到對(duì)應(yīng)的機(jī)器、應(yīng)用程序信息呢?
如下截圖所示,我們?cè)?span style="line-height: 21px">PROCESS STATE部分,找到對(duì)應(yīng)的SID 770的事務(wù),可以看到user,term、machine、program信息。剩下的事情,就是你和開發(fā)人員分析腳本,縷清細(xì)節(jié),然后如何避免死鎖的問題。
死鎖的分類
死鎖如何分類呢?在Metalink上這篇文章中"How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (文檔 ID 1507093.1)"有關(guān)于死鎖的分類:如下所示:
"Key Signature" | Lock Type | Requested | Deadlock Graph | Likely | Comments |
Type TX Lock Requesting Mode X (6) | TX | X(6) | TX X X | Application | TX Lock Held in Mode X (6) Requesting Mode X (6) |
Type TM Lock Requesting Mode SSX (5) | TM | SSX (5) | TM SX SSX SX SSX | Missing Index on Foreign Key (FK) Constraint | TM Lock Held in Mode SX (3) Held SSX (5) Requested |
Type TX Lock Requesting Mode S(4) | TX | S(4) | TX X S | Insufficient Interested Transaction List (ITL) Provision | TX Lock Held in Mode X (6) Requesting Mode S (4) ITL, Bitmap Index and PK/UK Index Signatures are the Same. Further Investigation will be required to identify absolute cause |
Type TX Lock Requesting Mode X (6) | TX | X(6) | TX X X | Self Deadlock | This looks the same as a standard application deadlock except that there is only a single row in the deadlock graph. |
Type UL Lock in Deadlock Graph | UL | ANY | UL ? ? | Application Deadlock Featuring User Defined Locks | This is very similar to the standard application deadlock except that it features User Defined Locks |
李華榮這篇博客Oracle死鎖(DeadLock)的分類及其模擬里面對(duì)死鎖進(jìn)行了一個(gè)分類,個(gè)人覺得是一個(gè)通俗、很贊的一個(gè)死鎖分類。本文很多地方也是參考、借鑒他博客的內(nèi)容。
那么我們接下來看看這些死鎖產(chǎn)生的場(chǎng)景,并進(jìn)行一些分析,很多知識(shí)點(diǎn)都是參考Metalink上的一些知識(shí)點(diǎn)。
1:應(yīng)用程序死鎖(Application Deadlock)
其實(shí)最上面那個(gè)死鎖的例子,就屬于Application Deadlock,這個(gè)Application Deadlock是發(fā)生在同一個(gè)表,下面我們介紹一下Application Deadlock發(fā)生在兩個(gè)表之間不同順序相互更新操作引起的死鎖。下面開始我們的實(shí)驗(yàn)。創(chuàng)建兩個(gè)測(cè)試表,并初始化數(shù)據(jù)。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create table lock_test_one(name varchar(32));
Table created.
SQL> create table lock_test_two(name varchar(32));
Table created.
SQL> insert into lock_test_one values('aaaaaaaa');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into l
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
有位圖索引存在的表上面,非常容易就引發(fā)阻塞與死鎖。這個(gè)阻塞不是發(fā)生在表上面,而是發(fā)生在索引上。因?yàn)槲粓D索引鎖定的隱者出本文出處:http://www.cnblogs.com/kerrycode/
如果你真心覺得文章寫得不錯(cuò),而且對(duì)你有所幫助,那就不妨小小打賞一下吧,如果囊中羞澀,不妨幫忙“推薦"一下,您的“推薦”和”打賞“將是我最大的寫作動(dòng)力!
本文版權(quán)歸作者所有,歡迎轉(zhuǎn)載,但未經(jīng)作者同意必須保留此段聲明,且在文章頁面明顯位置給出原文連接.
http://www.cnblogs.com/kerrycode/p/6928263.html