在 DB2®9.5中，分析鎖定超時的方法得到了極大改進(jìn)，鎖定超時分析變得更加簡單。本文探索這些全新的鎖定超時報(bào)告功能，并檢查收集的附加信息以確定發(fā)生鎖定超時的原因。

回顧DB2 9.1中的鎖定超時分析

使用db2pd工具和db2cos腳本進(jìn)行鎖定超時分析的方法包含以下幾步：

1.使用一個特殊的DB2腳本 — 名為 db2cos — 在每次調(diào)用db2cos腳本時執(zhí)行一個db2pd調(diào)用。db2pd 調(diào)用收集與鎖定、事務(wù)、應(yīng)用程序、語句緩存相關(guān)的信息，并將信息存儲在一個文本文件中以供分析。

2.要在鎖定超時發(fā)生時自動執(zhí)行 db2cos 腳本（以及包含的 db2pd 命令，需要使用 db2pdcfg 命令注冊鎖定超時事件。

3.在鎖定超時事件中，DBA 可以檢查通過自動調(diào)用 db2cos 腳本生成的 db2pd 輸出。這使 DBA 能夠確定發(fā)生鎖定爭用的原因，從而設(shè)法在以后避免發(fā)生這類情形。

盡管其中介紹的方法提供了大量信息，使得鎖定超時分析比以前的 DB2 9 for Linux, Unix, and Windows 版本更加簡單，但它仍然存在一些不足：

◆使用此方法需要手動改寫 db2cos 腳本并通過調(diào)用 db2pdcfg 來注冊鎖定超時事件。兩個步驟都不復(fù)雜，但是對于新手來說可能有些困難。

◆解釋 db2pd 輸出以識別鎖定超時情形中涉及的應(yīng)用程序和 SQL 語句，這個任務(wù)并不容易，而且需要一些嘗試。

◆如果鎖定超時是由包含多個 SQL 命令的事務(wù)引起的，那么 db2pd 收集的信息可能還不足以確定導(dǎo)致鎖定的 SQL 語句。

DB2 9.5 中全新的鎖定超時報(bào)告功能

在 DB2 9.5 的鎖定超時報(bào)告功能中，引入了一個新特性，使得鎖定超時分析變得非常簡單。要激活鎖定超時報(bào)告，只需將 DB2 注冊變量 DB2_CAPTURE_LOCKTIMEOUT 設(shè)置為 ON，并重新啟動您的 DB2 實(shí)例：

清單 1. 激活 DB2 9.5 中的鎖定超時報(bào)告

db2set DB2_CAPTURE_LOCKTIMEOUT=ON

db2stop

db2start

是的，就是這么簡單。當(dāng) DB2_CAPTURE_LOCKTIMEOUT 設(shè)置為 ON 時，DB2 為每個鎖定超時事件自動創(chuàng)建一個報(bào)告文件。報(bào)告文件保存在 DIAGPATH 數(shù)據(jù)庫管理員配置（DBM CFG）參數(shù)指向的目錄中，包含的信息有：鎖定超時的日期和時間、存在問題的鎖定、鎖定請求程序和鎖定擁有者。

那么 DB2 9.5 中就不使用 db2cos 腳本了嗎？事實(shí)并非如此。將 db2cos 腳本和 db2pd 工具結(jié)合具有非常廣泛的用途。這意味著，這些工具組合仍然可用于捕捉與 SQL 代碼和 DB2 內(nèi)部返回代碼相關(guān)的任何 DB2 事件信息，而不僅僅是鎖定超時信息。

現(xiàn)在看看新的 DB2 9.5 注冊變量 DB2_CAPTURE_LOCKTIMEOUT 并查看一個使用 DB2 SAMPLE 數(shù)據(jù)庫的鎖定超時示例場景。如果 SAMPLE 數(shù)據(jù)庫不存在，可以調(diào)用下面的命令創(chuàng)建一個：

清單2. 創(chuàng)建 SAMPLE 數(shù)據(jù)庫

db2sampl

只有當(dāng)數(shù)據(jù)庫配置（DB CFG）參數(shù) LOCKTIMEOUT 未被設(shè)置為 -1 時，鎖定超時才會發(fā)生。-1 意味著一個應(yīng)用程序可能在無限期地等待一個必需的鎖定，在一些情形中，這并不是期望發(fā)生的行為，但是 -1 是 LOCKTIMEOUT 的默認(rèn)設(shè)置。對于此示例場景，假設(shè) LOCKTIMEOUT 被更改為 10 秒（LOCKTIMEOUT 的值以秒為單位）：

清單3.更改LOCKTIMEOUT的值

db2 'UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT 10'

為了促使一個鎖定超時錯誤發(fā)生，我們首先建立一個數(shù)據(jù)庫連接，并執(zhí)行一些 SQL 語句來模擬表 EMPLOYEE 上的真實(shí)事務(wù)：

清單4. 第一個事務(wù)將每個員工的工資提高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'

到目前為止，這個事務(wù)包含一個 UPDATE 命令，該命令將每個員工的工資提高 2%。然后，使用 SELECT 語句查詢新的工資。注意，通過為 DB2 命令行處理程序（CLP）調(diào)用指定 +c 選項(xiàng)，我們禁用了自動提交功能。UPDATE 語句會導(dǎo)致 EMPLOYEE 表中的每行上發(fā)生一個獨(dú)占（X）鎖。這些獨(dú)占鎖會一直持續(xù)下去，直到使用 COMMIT 或 ROLLBACK 語句結(jié)束事務(wù)。

不需要結(jié)束事務(wù)，在一個單獨(dú)的 shell 中建立第二個數(shù)據(jù)庫連接并開始另一個事務(wù)：

清單5. 第二個事務(wù)為每位經(jīng)理提供 10%（取決于他們的工資)的獎金

db2 'CONNECT TO SAMPLE'

db2 +c 'UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER''

這個事務(wù)的用途是，根據(jù)每位經(jīng)理的當(dāng)前工資，為他們提供 10% 的獎金。由于 EMPLOYEE 表中的所有行都被第一個事務(wù)獨(dú)占鎖定，第二個應(yīng)用程序進(jìn)入了鎖等待模式。10 秒鐘之后（還記得 LOCKTIMEOUT 的設(shè)置吧），就會發(fā)生預(yù)期的鎖定超時。

目前為止還沒有出現(xiàn)什么新內(nèi)容。但是由于 DB2 注冊變量 DB2_CAPTURE_LOCKTIMEOUT 被設(shè)置為 ON，鎖定超時報(bào)告處于激活狀態(tài)，DB2 會在 DIAGPATH 目錄中自動生成鎖定超時報(bào)告。注意，DB2 9.5 for Windows 中默認(rèn)的 DIAGPATH 發(fā)生了改變。如果 DIAGPATH 參數(shù)未設(shè)置，DIAGPATH 會指向目錄 DB2INSTPROFDB2INSTANCE，其中 DB2INSTPROF 是實(shí)例目錄的位置，而 DB2INSTANCE 是 DB2 實(shí)例的名稱。要確定存儲在 DB2INSTPROF 中的路徑，可以通過執(zhí)行 db2set -all 命令顯示 DB2 注冊內(nèi)容。如果在默認(rèn)實(shí)例中創(chuàng)建 SAMPLE 數(shù)據(jù)庫，那么 DB2INSTANCE 的值為 DB2。報(bào)告文件的名稱為 db2locktimeout.dbpartition.agentid.timestamp，對于單分區(qū)的數(shù)據(jù)庫，其中的 dbpartition 始終為 0。

DB2 生成的鎖定超時報(bào)告如下所示：

清單6. 鎖定超時報(bào)告

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

鎖定超時報(bào)告包括 4 部分：

◆第一部分提供與鎖定超時發(fā)生的日期和時間，以及相應(yīng)的實(shí)例和數(shù)據(jù)庫相關(guān)的信息。

◆Lock Information 部分顯示導(dǎo)致鎖定超時的鎖。除了內(nèi)部鎖名以外，還會顯示鎖的類型（行鎖或表鎖）和表信息。要確定表名稱，需要使用給定的表空間 ID 和表 ID 查詢編目視圖 SYSCAT.TABLES：

清單7.將表空間 ID/表 ID 映射到表名稱

SELECT TABSCHEMA, TABNAME

FROM SYSCAT.TABLES

WHERE TBSPACEID = tbspaceid AND TABLEID = tableid

◆發(fā)生鎖定超時的應(yīng)用程序在 Lock Requestor 部分中描述。這部分還包含一些更有趣的條目：用于連接到數(shù)據(jù)庫的身份驗(yàn)證 ID、應(yīng)用程序名稱、請求的鎖模式（以及該鎖是否由一個鎖升級引起）、需要鎖的語句的隔離級別，以及 SQL 語句文本本身。

◆最后一部分 Lock Owner (Representative) 列出持有有問題的鎖的應(yīng)用程序。使用另一個應(yīng)用程序，還可以查看身份驗(yàn)證 ID、應(yīng)用程序名稱和鎖模式等信息。在一些情形下，可能不止一個應(yīng)用程序持有（共享）一個鎖，這會阻塞對鎖的請求。在這些情形下，鎖定超時報(bào)告中只會顯示一個鎖持有者。這就是這部分具有額外的 Representative 的原因。

在本文開始部分，我們提到了使用 db2cos 和 db2pd 進(jìn)行鎖定超時分析的三點(diǎn)不足。第一點(diǎn)是可用性。結(jié)合使用 db2cos 和 db2pd 的方法需要執(zhí)行一些步驟來設(shè)置鎖定超時監(jiān)視。新的方法簡單得多，只需設(shè)置 DB2_CAPTURE_LOCKTIMEOUT=ON。第二點(diǎn)不足是復(fù)雜性，因?yàn)樗枰M(jìn)行一些嘗試來讀取 db2pd 輸出和關(guān)聯(lián)不同的 db2pd 部分。使用新的方法，DB2 會生成一個報(bào)告文件，其中包含所有必要的信息。但是如何解決最后一點(diǎn)不足：鎖定超時情形涉及的 SQL 語句的信息不夠充分？目前為止，您只知道被現(xiàn)有的鎖定阻塞的 SQL 語句，但是一點(diǎn)都不了解導(dǎo)致鎖定的語句。對于這一點(diǎn)，新的鎖定超時報(bào)告功能也提供了改進(jìn)。現(xiàn)在看看它的工作原理。

收集SQL語句的歷史信息

為了獲得鎖持有者的應(yīng)用程序執(zhí)行的 SQL 語句的信息，我們使用 DETAILS HISTORY 選項(xiàng)創(chuàng)建一個死鎖事件監(jiān)視器并激活它。例如，可以通過如下方法創(chuàng)建一個恰當(dāng)?shù)乃梨i事件監(jiān)視器并將其激活：

清單 8. 使用 DETAILS HISTORY 選項(xiàng)創(chuàng)建死鎖事件監(jiān)視器

db2 'CREATE EVENT MONITOR evmondeadlock FOR DEADLOCKS WITH DETAILS HISTORY

WRITE TO FILE 'path''

db2 'SET EVENT MONITOR evmondeadlock STATE 1'

您可能會問：“為什么需要死鎖事件監(jiān)視器來監(jiān)視鎖定超時？”答案是構(gòu)建鎖定超時報(bào)告需要用到死鎖事件監(jiān)視器代碼交付的功能。使用 DETAILS HISTORY 選項(xiàng)創(chuàng)建死鎖事件監(jiān)視器時，DB2 跟蹤已經(jīng)在事務(wù)中執(zhí)行的 SQL 語句。如果發(fā)生死鎖或鎖定超時，這個信息可用于提供 SQL 語句的歷史信息，這些 SQL 語句可能與死鎖或鎖定超時的發(fā)生有關(guān)。

激活了死鎖事件監(jiān)視器之后，再次運(yùn)行上面描述的鎖定超時場景。這次 DB2 編寫一個鎖定超時報(bào)告，如清單 9 所示：

清單9. 包含 SQL 語句歷史信息的鎖定超時報(bào)告

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

這個鎖定超時報(bào)告的開始部分與前面看到的相同。但是，這次的 Lock Owner 部分包含額外的、有價值的信息。在標(biāo)題 List of Inactive SQL Statements from current UOW 下邊，可以看到在發(fā)生鎖定超時之前鎖持有者的事務(wù)執(zhí)行的所有 SQL 語句。從這組 SQL 語句中，可以找到導(dǎo)致問題鎖定的語句。在這個場景中，使用 UPDATE 語句增加每個員工的工資。

注意，這個功能是對結(jié)合使用 db2cos 和 db2pd 方法的一個重大改進(jìn)。使用 db2cos 與 db2pd 相結(jié)合的方法，只能看到鎖持有者的應(yīng)用程序執(zhí)行的最后一條語句 — 在這個場景中是對 EMPLOYEE 表的查詢。但是由于查詢并沒有導(dǎo)致出現(xiàn)問題的獨(dú)占鎖，您仍然不知道是哪條語句導(dǎo)致了鎖定。使用新方法 — DB2_CAPTURE_LOCKTIMEOUT 和死鎖事件監(jiān)視器 — 您擁有在鎖擁有者的事務(wù)中執(zhí)行的所有 SQL 語句的歷史信息，這就可以將 UPDATE 確定為相關(guān)的語句。

使用鎖定超時報(bào)告的提示

帶有語句歷史功能的死鎖事件監(jiān)視器適用于所有應(yīng)用程序，會增加 DB2 數(shù)據(jù)庫管理程序?qū)ΡO(jiān)視器堆的大量使用。所以應(yīng)該謹(jǐn)慎使用。您應(yīng)該始終首先設(shè)置 DB2_CAPTURE_LOCKTIMEOUT=ON，然后只在必要的時候使用 DETAILS HISTORY 選項(xiàng)激活死鎖事件監(jiān)視器。

使用鎖定超時報(bào)告時，您可能會注意到，DIAGPATH 中的鎖定超時報(bào)告文件的數(shù)量在持續(xù)增加。DB2 不會刪除這些報(bào)告文件，所以 DBA 需要刪除它們或者將它們移動到不同的位置，以便在 DIAGPATH 的文件系統(tǒng)上始終有足夠的空間。

即使擁有了鎖定超時報(bào)告功能，也不是總能夠輕松確定出導(dǎo)致鎖定超時的原因。例如，如果鎖定超時由靜態(tài) SQL 或 DB2 內(nèi)部鎖定引起時，就沒有那么容易確定原因。DB2 9.5 文檔的 Lock timeout reporting 一章提供了這些局限性的一個簡短列表（參見下面的 參考資料）。但是，DB2 9.5 中的鎖定超時報(bào)告絕對是一個許多 DBA 期待已久的功能，而且將大大簡化對鎖定超時的分析。