http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0602jiangt/
序言
本文的目的就是通過詳細的實例來闡述如何在DB2 UDB 中監控死鎖的發生。在DB2 UDB中有兩種類型的監控器：快照監控器和事件監控器。快照顧名思義就是數據庫連續狀態下的一個切面，通過快照監控器，你可以很方便地查看當前連接的應用程序，當前等待的鎖，當前的死鎖，以及正在執行的SQL語句，同時你可以查看緩沖區，表和表空間的用法。如果保存歷史數據，并且能夠做出比較，對于分析數據庫的并發性能有很大的幫助。
但是我們并不能預測什么時候發生死鎖，所以如果有一個后臺程序能夠一直監控數據庫的活動，記錄下所有的死鎖事件，這對于數據庫管理員來說是非常重要的。DB2 UDB提供了事件監控器。通過不遺漏地獲得一段時間內所有的數據庫事件（在本文中只關心其中的死鎖事件），事件監控器提供了一種可以分析歷史數據（本文的重點），預測將來趨勢的可能。DB2 UDB同時提供了DB2 Performance Expert (DB2/PE) 或者類似的程序用來生成分析報表，不過這已經超出了本文的范疇。
回頁首
常用術語
鎖是控制應用程序并發的數據庫軟件機制，鎖用來防止以下情況的發生：
1． 丟失以前更新
2． 不可重復讀取
3． 訪問未提交數據
鎖的模式包括共享鎖和排他鎖，共享鎖允許其他程序讀取已經被其他共享鎖占用的資源，所以也叫讀鎖，排他鎖意味著在釋放資源以前其他的應用程序無法訪問同一資源，所以也叫寫鎖。此外，DB2 UDB 還提供了不同的鎖級別，不同的應用程序可能會要求訪問不同范圍的數據，鎖級別有利于充分利用系統資源，提高系統性能。若一個應用程序請求一個鎖，而該鎖被另外一個應用程序所使用且不能共享，DB2 UDB 就會掛起前一個應用程序。鎖升級就是當LOCKLIST (LOCKLIST代表鎖能夠占用的內存空間) 耗盡或者一個應用程序所擁有的鎖大于MAXLOCKS*LOCKLIST的時候（MAXLOCKS 代表應用程序所擁有的鎖占所空間的百分比），DB2 UDB 就試圖把幾個行級別的鎖合并為一個表級別的鎖，從而釋放鎖空間。雖然鎖升級本身并不耗費多少時間，但是鎖住整個表通常會大大地降低并發性能。
當應用程序處于掛起狀態超過了一段規定的時間后，DB2 UDB就會自動中止這個應用程序，同時會向SQLCA發送描述性的錯誤信息。當兩個或者更多的應用程序都持有另外一個應用程序所需資源上的鎖，沒有這些資源，那些應用程序都無法繼續完成其工作的時候，就會發生死鎖。
在DLCHKTIME超時之后，DB2 UDB會中止發生死鎖的某個應用程序（通常為所做工作最少的那個應用程序），這會釋放這個應用程序所持有的所有的鎖，并允許別的應用程序繼續工作，DB2 UDB 將向被終止的應用程序的SQLCA發送描述性的錯誤信息。LOCKTIMEOUT 指定一個應用程序被允許的鎖等待的時間，這將避免全局的死鎖從而導致整個應用崩潰。如果LOCKTIMEOUT 的值為-1，應用程序會等待直到該鎖被釋放或者發生一個死鎖。
回頁首
事件監控器
事件監控器用來收集當一個數據庫事件發生時所關聯的應用程序的信息。這里的事件指，連接，死鎖，聲明和事務。你可以定義你想監控的事件類型的監控器。比如說，一個死鎖監控器就是用來監控死鎖的發生。
在DB2 UDB 中存在兩種和死鎖有關的事件類型：
- DEADLOCKS
記錄簡單的應用程序信息。 - DEADLOCKS WITH DETAILS
記錄所有復雜的信息，包括應用程序、執行語句聲明以及死鎖的詳細信息。但是使用這種事件監控器會因為需要得到大量額外的信息而降低系統的性能。
回頁首
如何監控死鎖
為了詳細說明事件監控器在死鎖監控中的用途，我引入了一個簡單的死鎖場景來觸發一個死鎖，在隨后的章節，我會告訴讀者如何分析監控結果以及根據結果來避免死鎖的發生。
這里我們需要至少三個應用程序來調用DB2 CLI，一個用來監控死鎖的發生，另外兩個用來產生死鎖。我們可以使用DB2 UDB 安裝時附帶的SAMPLE數據庫。
1． 首先建立一個死鎖事件監控器
Session Monitor
db2 connect to sample
db2 "create event monitor dlmon for tables, deadlocks with
details write to file 'C:\dlmon'"
mkdir C:\dlmon
db2 "set event monitor dlmon state 1"
|
2． 用另外兩個應用程序來產生一個死鎖
Session A
db2 connect to sample
db2 +c "insert into employee values('000350', 'Truman',
'I', 'Jiang', 'B00', '5892',
'1999-02-21', 'Engineer', 19, 'M',
'1978-06-17', 60000, 2000, 6000)"
|
現在應用程序A就擁有了一個EMPLOYEE表的行級別的排他鎖
（注: +c 代表不自動提交SQL語句，DB2 中 autocommit 是缺省設置，也可以通過 db2 update command options using c off 關閉該缺省選項。）
Session B
db2 connect to sample
db2 +c "insert into project values('AD3300', 'Dead Lock Monitor', 'B00', '000350', 7.00,
'1982-07-21', '1983-02-03', 'AD3111')"
|
現在應用程序B就擁有了一個PROJECT表的行級別的排他鎖
Session A
db2 +c "select projname from project"
|
應用程序A需要PROJECT表上所有行的共享鎖，但是因為PROJECT表正在被應用程序B以排他鎖的形式獨占，這時候應用程序1就進入一個鎖等待的狀態。
Session B
db2 +c "select firstnme from employee"
|
應用程序B也進入一個鎖等待的狀態。此時就出現了一個死鎖狀態。
3． 兩個本身處于鎖等待并且占有資源的應用程序互相等待另外一方所持有的資源，這時候Session A和Session B就出現了死鎖狀態，這種狀態一直會延續直到死鎖檢查器（超出DLCHKTIME時間以后）檢查出一個死鎖并且回滾其中的一個事務。
Session B
SQLN0991N 因為死鎖或者超時，當前事務已經被回滾。原因碼為 "2". SQLSTATE=40001
這時候死鎖事件監控器就會記錄這個死鎖，同時應用程序A可以完成他的工作。
|
Session A
PROJNAME
----------------------------------------
……
20 條記錄已選擇
|
Session A
Session B
4． 通過 db2evmon 工具可以獲得死鎖信息的日志，并且把日志文件導入到本地機器的文件系統當中。在下面一節，我們將詳細分析導出的日志文件。
Session Monitor
db2 connect reset
db2evmon -path c:\dlmon > c:\dlmon\dllog1.txt
|
回頁首
分析監控結果
本節我們開始詳細分析上一節產生的監控結果，從監控導出的日志文件中，我們可以分析出死鎖發生的時間，級別，模式以及產生死鎖的SQL語句，從而據此來進一步地修正可能由程序并發設計或者數據庫設計所導致的缺陷。
--------------------------------------------------------------------------
EVENT LOG HEADER
Event Monitor name: DLMON
Server Product ID: SQL08022
……
Server instance name: DB2
--------------------------------------------------------------------------
--------------------------------------------------------------------------
Database Name: SAMPLE
Database Path: C:\DB2\NODE0000\SQL00001\
……
--------------------------------------------------------------------------
3) Deadlock Event ...
Deadlock ID: 1
……
4) Connection Header Event ...
Appl Handle: 949
……
5) Deadlocked Connection ...
Deadlock ID: 1
Participant no.: 2
Participant no. holding the lock: 1
Appl Id: G9B56A72.HE13.01B406083205
Appl Seq number: 0001
Appl Id of connection holding the lock: G9B56A72.HD13.02CE06083152
……
Deadlock detection time: 2006-01-06 16:34:27.327582
Table of lock waited on : EMPLOYEE
(A鎖發生的表)
Schema of lock waited on : JT
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
（A鎖級別為行鎖）
Mode of lock: X - Exclusive
（A鎖模式為排他鎖）
Mode application requested on lock: NS - Share (and Next Key Share)
（在A排他鎖上要求B共享鎖，發生死鎖）
……
Text : select name from employee
（產生B共享鎖的SQL語句）
List of Locks:
（當前所有鎖的列表）
……
Lock Name : 0x020005000D0000000000000052
Lock Attributes : 0x00000008
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 13
Object Type : Row
Tablespace Name : USERSPACE1
Table Schema : JT
Table Name : PROJECT
Mode : X - Exclusive
（在PROJECT表上有一個排他鎖）
……
Lock Name : 0x02000300000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x00000001
Lock Count : 1
Hold Count : 0
Lock Object Name : 3
Object Type : Table
Tablespace Name : USERSPACE1
Table Schema : JT
Table Name : EMPLOYEE
Mode : IS - Intent Share
（在EMPLOYEE表上有一個共享鎖）
Locks Held: 6
Locks in List: 6
……
9) Table Event ...
Table schema: JT
Table name: EMPLOYEE
Record is the result of a flush: FALSE
Table type: User
Data object pages: 1
……
Rows read: 35
Rows written: 1
……
Tablespace id: 2
Table event timestamp: 2006-01-06 16:37:28.972501
（記錄EMPLOYEE表上發生的事件）
|
我們可以分析一下dllog1.txt 文件，來準確定位死鎖發生的原因，看看5）Deadlocked Connection: 我們可以看出死鎖發生的表是EMPLOYEE，同時我們也可以判斷出這是一個對于已被排他鎖占有的資源申請共享鎖所導致的死鎖。更加重要的是我們得到了產生死鎖的SQL語句，從上面我們可以推斷出一定存在別的應用程序在以獨占鎖的方式占用EMPLOYEE表，這很有可能就是對于EMPLOYEE表的插入或者更新動作造成的。
而這最有可能就是插入或者更新事務時間過長所導致的，導致事務時間過長的原因大體有兩種，一是來自于并發程序的設計和編寫，二是來自于數據庫的設計和數據庫參數的調整。
本節我們通過仔細地分析事件監控器的結果來推斷出導致死鎖發生的原因，從而采取有效的措施去避免死鎖的發生。這些措施包括調整數據庫參數，或者修改應用程序的代碼，或者修改SQL語句甚至是數據庫的設計來提高代碼和SQL語句執行的效率。
回頁首
避免死鎖的方法
越早地考慮數據庫設計中的并發性問題，就越可以提高代碼執行的效率，降低程序開發和維護的成本，這里我們提出了一些避免死鎖，提高應用程序并發性的方法。
- 設置隔離級別，根據應用程序的業務邏輯和數據完整性需求來決定合適的隔離級別，包括：RR，RS，CS，UR。該決定需要對應用程序需求和相關的業務規則具有基本理解
- 盡量避免鎖升級，正確調整參數LOCKLIST, MAXLOCKS
- SQL0911N返回碼68（LOCKTIMEOUT參數）的原因是鎖等待超時，而SQL0911返回碼2（DLCHKTIME參數）的原因則是因為死鎖被強制回滾，避免這兩種錯誤的方法就是合理設計數據庫和建立合理的索引
- 盡快提交事務，不要在事務中加入不必要的執行時間過長的代碼，比如大的代碼循環和遠程調用，或者一些沒有用處的SELECT語句
- 應用程序的框架實現保證一旦發現SQL錯誤，立刻執行回滾事務，釋放鎖。
- 如果多個應用程序訪問同一資源，最好以相同的次序訪問。這樣，即使前一個訪問資源的應用程序會延遲其他應用程序的訪問，也不會導致死鎖的發生
- 設定外鍵索引，如果想刪除父表中的行，就需要掃描多個子表中的多行數據，這樣就需要占用多個子表的鎖，我們可以通過在外鍵上建立索引來減少掃描子表的行數，否則若不建立索引，如果從父表中刪除一行的時候，就需要掃描整個子表