深入探討DB2 9.5中的鎖定超時分析新方法
回顧DB2 9.1中的鎖定超時分析
使用db2pd工具和db2cos腳本進行鎖定超時分析的方法包含以下幾步：
1.使用一個特殊的DB2腳本 — 名為 db2cos — 在每次調用db2cos腳本時執行一個db2pd調用。db2pd 調用收集與鎖定、事務、應用程序、語句緩存相關的信息，并將信息存儲在一個文本文件中以供分析。
2.要在鎖定超時發生時自動執行 db2cos 腳本（以及包含的 db2pd 命令，需要使用 db2pdcfg 命令注冊鎖定超時事件。
3.在鎖定超時事件中，DBA 可以檢查通過自動調用 db2cos 腳本生成的 db2pd 輸出。這使 DBA 能夠確定發生鎖定爭用的原因，從而設法在以后避免發生這類情形。
盡管其中介紹的方法提供了大量信息，使得鎖定超時分析比以前的 DB2 9 for Linux, Unix, and Windows 版本更加簡單，但它仍然存在一些不足：
◆使用此方法需要手動改寫 db2cos 腳本并通過調用 db2pdcfg 來注冊鎖定超時事件。兩個步驟都不復雜，但是對于新手來說可能有些困難。
◆解釋 db2pd 輸出以識別鎖定超時情形中涉及的應用程序和 SQL 語句，這個任務并不容易，而且需要一些嘗試。
◆如果鎖定超時是由包含多個 SQL 命令的事務引起的，那么 db2pd 收集的信息可能還不足以確定導致鎖定的 SQL 語句。
DB2 9.5 中全新的鎖定超時報告功能
在 DB2 9.5 的鎖定超時報告功能中，引入了一個新特性，使得鎖定超時分析變得非常簡單。要激活鎖定超時報告，只需將 DB2 注冊變量 DB2_CAPTURE_LOCKTIMEOUT 設置為 ON，并重新啟動您的 DB2 實例：
清單 1. 激活 DB2 9.5 中的鎖定超時報告
db2set DB2_CAPTURE_LOCKTIMEOUT=ON
db2stop
db2start
是的，就是這么簡單。當 DB2_CAPTURE_LOCKTIMEOUT 設置為 ON 時，DB2 為每個鎖定超時事件自動創建一個報告文件。報告文件保存在 DIAGPATH 數據庫管理員配置（DBM CFG）參數指向的目錄中，包含的信息有：鎖定超時的日期和時間、存在問題的鎖定、鎖定請求程序和鎖定擁有者。
那么 DB2 9.5 中就不使用 db2cos 腳本了嗎？事實并非如此。將 db2cos 腳本和 db2pd 工具結合具有非常廣泛的用途。這意味著，這些工具組合仍然可用于捕捉與 SQL 代碼和 DB2 內部返回代碼相關的任何 DB2 事件信息，而不僅僅是鎖定超時信息。
現在看看新的 DB2 9.5 注冊變量 DB2_CAPTURE_LOCKTIMEOUT 并查看一個使用 DB2 SAMPLE 數據庫的鎖定超時示例場景。如果 SAMPLE 數據庫不存在，可以調用下面的命令創建一個：
清單2. 創建 SAMPLE 數據庫
db2sampl
只有當數據庫配置（DB CFG）參數 LOCKTIMEOUT 未被設置為 -1 時，鎖定超時才會發生。-1 意味著一個應用程序可能在無限期地等待一個必需的鎖定，在一些情形中，這并不是期望發生的行為，但是 -1 是 LOCKTIMEOUT 的默認設置。對于此示例場景，假設 LOCKTIMEOUT 被更改為 10 秒（LOCKTIMEOUT 的值以秒為單位）：
清單3.更改LOCKTIMEOUT的值
db2 'UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT 10'
為了促使一個鎖定超時錯誤發生，我們首先建立一個數據庫連接，并執行一些 SQL 語句來模擬表 EMPLOYEE 上的真實事務：
清單4. 第一個事務將每個員工的工資提高2%
db2 'CONNECT TO SAMPLE'
db2 +c 'UPDATE EMPLOYEE SET SALARY = SALARY * 1.02'
db2 +c 'SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE ORDER BY LASTNAME ASC'
到目前為止，這個事務包含一個 UPDATE 命令，該命令將每個員工的工資提高 2%。然后，使用 SELECT 語句查詢新的工資。注意，通過為 DB2 命令行處理程序（CLP）調用指定 +c 選項，我們禁用了自動提交功能。UPDATE 語句會導致 EMPLOYEE 表中的每行上發生一個獨占（X）鎖。這些獨占鎖會一直持續下去，直到使用 COMMIT 或 ROLLBACK 語句結束事務。
不需要結束事務，在一個單獨的 shell 中建立第二個數據庫連接并開始另一個事務：
清單5. 第二個事務為每位經理提供 10%（取決于他們的工資)的獎金
db2 'CONNECT TO SAMPLE'
db2 +c 'UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER''
這個事務的用途是，根據每位經理的當前工資，為他們提供 10% 的獎金。由于 EMPLOYEE 表中的所有行都被第一個事務獨占鎖定，第二個應用程序進入了鎖等待模式。10 秒鐘之后（還記得 LOCKTIMEOUT 的設置吧），就會發生預期的鎖定超時。
目前為止還沒有出現什么新內容。但是由于 DB2 注冊變量 DB2_CAPTURE_LOCKTIMEOUT 被設置為 ON，鎖定超時報告處于激活狀態，DB2 會在 DIAGPATH 目錄中自動生成鎖定超時報告。注意，DB2 9.5 for Windows 中默認的 DIAGPATH 發生了改變。如果 DIAGPATH 參數未設置，DIAGPATH 會指向目錄 DB2INSTPROFDB2INSTANCE，其中 DB2INSTPROF 是實例目錄的位置，而 DB2INSTANCE 是 DB2 實例的名稱。要確定存儲在 DB2INSTPROF 中的路徑，可以通過執行 db2set -all 命令顯示 DB2 注冊內容。如果在默認實例中創建 SAMPLE 數據庫，那么 DB2INSTANCE 的值為 DB2。報告文件的名稱為 db2locktimeout.dbpartition.agentid.timestamp，對于單分區的數據庫，其中的 dbpartition 始終為 0。
DB2 生成的鎖定超時報告如下所示：
清單6. 鎖定超時報告
LOCK TIMEOUT REPORT
Date: 03/01/2008
Time: 07:34:31
Instance: DB2
Database: SAMPLE
Database Partition: 0
Lock Information:
Lock Name: 02000600040040010000000052
Lock Type: Row
Lock Specifics: Tablespace ID=2, Table ID=6, Row ID=x0400400100000000
Lock Requestor:
System Auth ID: FECHNER
Application Handle: [0-38]
Application ID: *LOCAL.DB2.080103063343
Application Name: db2bp.exe
Requesting Agent ID: 5232
Coordinator Agent ID: 5232
Coordinator Partition: 0
Lock timeout Value: 10000 milliseconds
Lock mode requested: ..U
Application Status: (SQLM_UOWEXEC)
Current Operation: (SQLM_EXECUTE_IMMEDIATE)
Lock Escalation: No
Context of Lock Request:
Identification: UOW ID (1); Activity ID (1)
Activity Information:
Package Schema: (NULLID )
Package Name: (SQLC2G13NULLID )
Package Version: ()
Section Entry Number: 203
SQL Type: Dynamic
Statement Type: DML, Insert/Update/Delete
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: UPDATE EMPLOYEE SET BONUS = SALARY * 0.1
WHERE JOB = 'MANAGER'
Lock Owner (Representative):
System Auth ID: FECHNER
Application Handle: [0-33]
Application ID: *LOCAL.DB2.080103063332
Application Name: db2bp.exe
Requesting Agent ID: 5488
Coordinator Agent ID: 5488
Coordinator Partition: 0
Lock mode held: ..X
List of Active SQL Statements: Not available
List of Inactive SQL Statements from current UOW: Not available
鎖定超時報告包括 4 部分：
◆第一部分提供與鎖定超時發生的日期和時間，以及相應的實例和數據庫相關的信息。
◆Lock Information 部分顯示導致鎖定超時的鎖。除了內部鎖名以外，還會顯示鎖的類型（行鎖或表鎖）和表信息。要確定表名稱，需要使用給定的表空間 ID 和表 ID 查詢編目視圖 SYSCAT.TABLES：
清單7.將表空間 ID/表 ID 映射到表名稱
SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE TBSPACEID = tbspaceid AND TABLEID = tableid
◆發生鎖定超時的應用程序在 Lock Requestor 部分中描述。這部分還包含一些更有趣的條目：用于連接到數據庫的身份驗證 ID、應用程序名稱、請求的鎖模式（以及該鎖是否由一個鎖升級引起）、需要鎖的語句的隔離級別，以及 SQL 語句文本本身。
◆最后一部分 Lock Owner (Representative) 列出持有有問題的鎖的應用程序。使用另一個應用程序，還可以查看身份驗證 ID、應用程序名稱和鎖模式等信息。在一些情形下，可能不止一個應用程序持有（共享）一個鎖，這會阻塞對鎖的請求。在這些情形下，鎖定超時報告中只會顯示一個鎖持有者。這就是這部分具有額外的 Representative 的原因。
在本文開始部分，我們提到了使用 db2cos 和 db2pd 進行鎖定超時分析的三點不足。第一點是可用性。結合使用 db2cos 和 db2pd 的方法需要執行一些步驟來設置鎖定超時監視。新的方法簡單得多，只需設置 DB2_CAPTURE_LOCKTIMEOUT=ON。第二點不足是復雜性，因為它需要進行一些嘗試來讀取 db2pd 輸出和關聯不同的 db2pd 部分。使用新的方法，DB2 會生成一個報告文件，其中包含所有必要的信息。但是如何解決最后一點不足：鎖定超時情形涉及的 SQL 語句的信息不夠充分？目前為止，您只知道被現有的鎖定阻塞的 SQL 語句，但是一點都不了解導致鎖定的語句。對于這一點，新的鎖定超時報告功能也提供了改進。現在看看它的工作原理。
收集SQL語句的歷史信息
為了獲得鎖持有者的應用程序執行的 SQL 語句的信息，我們使用 DETAILS HISTORY 選項創建一個死鎖事件監視器并激活它。例如，可以通過如下方法創建一個恰當的死鎖事件監視器并將其激活：
清單 8. 使用 DETAILS HISTORY 選項創建死鎖事件監視器
db2 'CREATE EVENT MONITOR evmondeadlock FOR DEADLOCKS WITH DETAILS HISTORY
WRITE TO FILE 'path''
db2 'SET EVENT MONITOR evmondeadlock STATE 1'
您可能會問：“為什么需要死鎖事件監視器來監視鎖定超時？”答案是構建鎖定超時報告需要用到死鎖事件監視器代碼交付的功能。使用 DETAILS HISTORY 選項創建死鎖事件監視器時，DB2 跟蹤已經在事務中執行的 SQL 語句。如果發生死鎖或鎖定超時，這個信息可用于提供 SQL 語句的歷史信息，這些 SQL 語句可能與死鎖或鎖定超時的發生有關。
激活了死鎖事件監視器之后，再次運行上面描述的鎖定超時場景。這次 DB2 編寫一個鎖定超時報告，如清單 9 所示：
清單9. 包含 SQL 語句歷史信息的鎖定超時報告
LOCK TIMEOUT REPORT
Date: 03/01/2008
Time: 15:10:13
Instance: DB2
Database: SAMPLE
Database Partition: 0
Lock Information:
Lock Name: 02000600040040010000000052
Lock Type: Row
Lock Specifics: Tablespace ID=2, Table ID=6, Row ID=x0400400100000000
Lock Requestor:
System Auth ID: FECHNER
Application Handle: [0-202]
Application ID: *LOCAL.DB2.080103140934
Application Name: db2bp.exe
Requesting Agent ID: 2356
Coordinator Agent ID: 2356
Coordinator Partition: 0
Lock timeout Value: 10000 milliseconds
Lock mode requested: ..U
Application Status: (SQLM_UOWEXEC)
Current Operation: (SQLM_EXECUTE_IMMEDIATE)
Lock Escalation: No
Context of Lock Request:
Identification: UOW ID (1); Activity ID (1)
Activity Information:
Package Schema: (NULLID )
Package Name: (SQLC2G13NULLID )
Package Version: ()
Section Entry Number: 203
SQL Type: Dynamic
Statement Type: DML, Insert/Update/Delete
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: UPDATE EMPLOYEE SET BONUS = SALARY * 0.1
WHERE JOB = 'MANAGER'
Lock Owner (Representative):
System Auth ID: FECHNER
Application Handle: [0-188]
Application ID: *LOCAL.DB2.080103140511
Application Name: db2bp.exe
Requesting Agent ID: 5488
Coordinator Agent ID: 5488
Coordinator Partition: 0
Lock mode held: ..X
List of Active SQL Statements: Not available
List of Inactive SQL Statements from current UOW:
Entry: #1
Identification: UOW ID (6); Activity ID (2)
Package Schema: (NULLID )
Package Name: (SQLC2G13)
Package Version: ()
Section Entry Number: 201
SQL Type: Dynamic
Statement Type: DML, Select (blockable)
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE
ORDER BY LASTNAME ASC
Entry: #2
Identification: UOW ID (6); Activity ID (1)
Package Schema: (NULLID )
Package Name: (SQLC2G13)
Package Version: ()
Section Entry Number: 203
SQL Type: Dynamic
Statement Type: DML, Insert/Update/Delete
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: UPDATE EMPLOYEE SET SALARY = SALARY * 1.02
這個鎖定超時報告的開始部分與前面看到的相同。但是，這次的 Lock Owner 部分包含額外的、有價值的信息。在標題 List of Inactive SQL Statements from current UOW 下邊，可以看到在發生鎖定超時之前鎖持有者的事務執行的所有 SQL 語句。從這組 SQL 語句中，可以找到導致問題鎖定的語句。在這個場景中，使用 UPDATE 語句增加每個員工的工資。
注意，這個功能是對結合使用 db2cos 和 db2pd 方法的一個重大改進。使用 db2cos 與 db2pd 相結合的方法，只能看到鎖持有者的應用程序執行的最后一條語句 — 在這個場景中是對 EMPLOYEE 表的查詢。但是由于查詢并沒有導致出現問題的獨占鎖，您仍然不知道是哪條語句導致了鎖定。使用新方法 — DB2_CAPTURE_LOCKTIMEOUT 和死鎖事件監視器 — 您擁有在鎖擁有者的事務中執行的所有 SQL 語句的歷史信息，這就可以將 UPDATE 確定為相關的語句。
使用鎖定超時報告的提示
帶有語句歷史功能的死鎖事件監視器適用于所有應用程序，會增加 DB2 數據庫管理程序對監視器堆的大量使用。所以應該謹慎使用。您應該始終首先設置 DB2_CAPTURE_LOCKTIMEOUT=ON，然后只在必要的時候使用 DETAILS HISTORY 選項激活死鎖事件監視器。
使用鎖定超時報告時，您可能會注意到，DIAGPATH 中的鎖定超時報告文件的數量在持續增加。DB2 不會刪除這些報告文件，所以 DBA 需要刪除它們或者將它們移動到不同的位置，以便在 DIAGPATH 的文件系統上始終有足夠的空間。
即使擁有了鎖定超時報告功能，也不是總能夠輕松確定出導致鎖定超時的原因。例如，如果鎖定超時由靜態 SQL 或 DB2 內部鎖定引起時，就沒有那么容易確定原因。DB2 9.5 文檔的 Lock timeout reporting 一章提供了這些局限性的一個簡短列表（參見下面的 參考資料）。但是，DB2 9.5 中的鎖定超時報告絕對是一個許多 DBA 期待已久的功能，而且將大大簡化對鎖定超時的分析。
網公網安備