簡介
在大型 DB2 環境中,存在多個應用程序相互競爭數據資源的情況,為保證數據完整性,DB2 采用了鎖機制來保護用戶的修改不丟失。隨著應用程序數量的增多、復雜性的提高,診斷和排除應用程序鎖定爭用情況的工作可能相當復雜而且耗費 DBA 很多時間。在 V9.7 中,DB2 新增了鎖定事件監視器和其他工具通過收集鎖定數據來幫助 DBA 簡化鎖引起應用程序性能問題。
在 V9 以前版本中,如果希望診斷鎖問題,我們可以采用表函數 SNAP_GET_LOCKWAIT 查看當前鎖等待情況,或者使用 GET SNAPSHOT FOR LOCKS 命令獲取相關信息。在 V9.1 版本中,DB2 增加了管理視圖 SYSIBMADM. SNAPLOCKWAIT 用於返回鎖等待信息。無論是表函數 SNAP_GET_LOCKWAIT 或者管理視圖 SYSIBMADM. SNAPLOCKWAIT,我們必須在鎖定發生的瞬間執行查詢才能獲得當前的鎖定信息。而應用程序的鎖發生具有隨機性,對於已經超時回滾或者死鎖回滾的應用,則上面的方法不能看到鎖定模式、鎖定對象等信息,也看不到哪些應用程序因為執行哪些 SQL 語句引起鎖問題。
DB2V9.7 提供了鎖定事件監視器用於在發生鎖定事件時自動捕獲關於事件的描述性信息。鎖定事件監視器捕獲的信息可以標識引起的鎖定爭用所涉及的關鍵應用程序,同時還可以捕獲這些應用程序因為執行何種 SQL 語句而引起鎖。與 DB2 以前版本中的死鎖事件監視器不同,鎖定事件監視器同時捕獲關於鎖定請求者(接收到死鎖或鎖定超時錯誤或者等待鎖定時的耗用時間超出指定時間長度的應用程序)和當前鎖定所有者的信息。
DB2V9.7 提供的鎖定事件監視器可以捕獲其鎖定數據的活動的類型包括:
SQL 語句,例如:DML 、DDL 、CALL
LOAD 命令
REORG 命令
BACKUP DATABASE 命令
實用程序請求
在使用了 DB2V9.7 鎖定事件監視器後,IBM 不推薦繼續使用早期的死鎖事件監視器(CREATE EVENT MONITOR FOR DEADLOCKS 語句和 DB2DETAILDEADLOCK),同時不推薦使用鎖定超時報告功能(DB2_CAPTURE_LOCKTIMEOUT 注冊表變量)。不過在新創建的數據中,默認還是創建了 DB2DETAILDEADLOCK 事件,因此如果我們希望使用鎖定事件監視器,最好執行下面語句予以刪除。
清單 1. 刪除默認死鎖事件監視
SET EVENT MONITOR DB2DETAILDEADLOCK state 0
DROP EVENT MONITOR DB2DETAILDEADLOCK
非格式化事件表
DB2 V9.7 對鎖、事務事件監控引入了一種新的寫入目標即非格式化事件表。使用非格式化事件表的事件監控器具有比較好的性能。在事件捕獲過程中,信息以二進制形式寫入到非格式化事件表中。一個事件可能會在非格式化事件表中插入多行記錄,每一行具有不同的類型。
我們在創建事件時可以指定非格式化事件表的名稱、表所在的表空間,默認情況下非格式化表名與事件名稱相同。非格式化表具有以下列:
appl_id
appl_name
clIEnt_acctng
clIEnt_applname
clIEnt_userid
clIEnt_wrkstnname
event_correlation_id
event_data
event_id
event_type
member
partitioning_key
record_seq_num
record_type
service_subclass_name
service_superclass_name
workload_name
其中為 Blob 類型的 event_data 列最為關鍵,其他列是用於幫助用戶識別感興趣事件的輔助信息。在完成捕獲後,我們可以使用 Java 程序 db2evmonfmt、表函數 EVMON_FORMAT_UE_TO_XML 或存儲過程 EVMON_FORMAT_UE_TO_TABLES 從非格式化事件表中抽取數據用以分析。本文將主要使用存儲過程 VMON_FORMAT_UE_TO_TABLES 進行數據分析。
與鎖事件監控相關的配置參數
為配合鎖事件監控,DB2 V9.7 增加了數據庫配置參數 MON_LOCKWAIT、MON_LOCKTIMEOUT、MON_DEADLOCK、MON_LW_THRESH。
MON_LOCKWAIT 用於控制鎖事件監控在捕獲鎖等待時搜集數據的詳細程度,可以設置成以下值:
NONE,不會在任何分區中收集工作負載的鎖定超時數據。
WITHOUT_HIST,發生鎖定事件時,會將關於鎖定事件的數據發送到任何活動的鎖定事件監視器。不會將過去的活動歷史記錄以及輸入值發送到事件監視器。
WITH_HIST,對於所有此類鎖定事件,都可以收集當前工作單元中的過去活動歷史記錄。活動歷史緩沖區將在達到最大大小限制後回繞。這意味著,要保留的過去活動數的缺省限制是 250。如果過去活動的數目高於此限制,那麼將只報告最新的活動。
HIST_AND_VALUES,對於那些包含輸入數據值的活動,會將那些輸入數據值發送到任何活動的鎖定事件監視器。這些數據值不包括 LOB 數據、更改開始 LONG VARCHAR 數據、LONG VARGRAPHIC 數據、更改結束結構化類型數據或 XML 數據。
MON_LW_THRESH 控制在生成 mon_lockwait 的事件之前等待鎖定時花費的時間,以微秒為單位。
MON_LOCKTIMEOUT 和 MON_DEADLOCK 取值范圍與 MON_LOCKWAIT 相同,含義類似。
為收集詳細的鎖鎖定信息,我們將數據庫的這些參數都調整為 HIST_AND_VALUES。
清單 2. 修改鎖事件監控配置參數
db2 update db cfg using MON_LOCKTIMEOUT HIST_AND_VALUES
MON_DEADLOCK HIST_AND_VALUES MON_LOCKWAIT HIST_AND_VALUES
MON_LW_THRESH 5000000
DB20000I UPDATE DATABASE CONFIGURATION 命令成功完成。
db2 get db cfg
------------------------------- 省略 -------------------------
鎖定超時事件數 (MON_LOCKTIMEOUT) = HIST_AND_VALUES
死鎖事件數 (MON_DEADLOCK) = HIST_AND_VALUES
鎖定等待事件數 (MON_LOCKWAIT) = HIST_AND_VALUES
鎖定等待事件阈值 (MON_LW_THRESH) = 5000000
鎖等待事件
當兩個應用程序競爭資源時,第一個應用程序在資源上加鎖後,第二個應用程序企圖加鎖時發現希望增加的鎖與資源現有的鎖不兼容,就出現鎖等待,第二個應用程序掛起。為捕獲鎖等待事件,MON_LOCKWAIT 不能設置為 NONE,當鎖等待時間超過 MON_LW_THRESH 指定的值時則事件監視器捕獲該事件。
清單 3. 創建鎖等待監視器
db2 create event monitor lockwaitevm for locking
write to UNFORMATTED EVENT TABLE
db2 set event monitor lockwaitevm state 1
db2 list tables for all|find /i "lockwaitevm"
LOCKWAITEVM DB2ADMIN T 2009-11-18-14.47.43.281002
清單 3 代碼中,我們首先創建了一個鎖事件監視器,並把捕獲的數據寫入到非格式化事件表中。我們發現在系統除成功創建一個事件外,DB2 還在系統中創建了一張同名的表用於保存捕獲數據。
我們另外打開兩個命令窗口,用於模擬兩個應用,用戶名稱依次為 app1、app2。我們把測試表 stock 所有權限授予給 public 組,以允許 app1、app2 用戶修改數據。
清單 4. 創建測試表
db2 "import from stock.exp of ixf replace_create into db2admin.stock"
db2 "grant all on db2admin.stock to public"
db2 "create index itemno on db2admin.stock(itemno)"
db2 "runstats on table db2admin.stock and indexes all"
清單 5. 應用程序 1 連接
db2 connect to test user app1 using app1
數據庫連接信息
數據庫服務器 = DB2/NT 9.7.0
SQL 授權標識 = APP1
本地數據庫別名 = TEST
清單 6. 應用程序 2 連接
db2 connect to test user app2 using app2
數據庫連接信息
數據庫服務器 = DB2/NT 9.7.0
SQL 授權標識 = APP2
本地數據庫別名 = TEST
清單 7. 查看連接信息
db2 list applications
授權標識程序 應用程序名 應用程序句柄 應用程序標識 數據庫名稱 代理序號
-------- ----------- ---------- -------------------
APP2 db2bp.exe 14 *LOCAL.DB2.091118070502 TEST 1
APP1 db2bp.exe 7 *LOCAL.DB2.091118070426 TEST 1
我們看到 app1 對應的應用程序句柄為 7,app2 對應的應用程序句柄為 14,這兩個句柄將在後面用來識別應用。
我們在應用程序 1 中修改 stock 表的數據:
清單 8. 應用 1 修改 stock 表數據
db2 +c "update db2admin.stock set qty=qty+1 where itemno=300"
清單 9. 應用 2 修改 stock 表數據
db2 +c "update db2admin.stock set qty=qty+1 where itemno=300"
我們的應用 1 和應用 2 同時修改 stock 表的相同行,由於應用 1 先發出 SQL,返回成功後並未發出 commit 或 rollback。因此應用 1 在“300”行上持有 X 鎖,當應用 2 在同一行上也希望持有 X 鎖時,應用 2 必須等待。
當等待時間超過 MON_LW_THRESH 參數限定的 5 秒鐘後,鎖等待事件發生。我們使用 EVMON_FORMAT_UE_TO_TABLES 格式化捕獲的數據。存儲過程 EVMON_FORMAT_UE_TO_TABLES 將首先調用表函數 EVMON_FORMAT_UE_TO_XML 將非格式化事件表數據轉換為 XML,然後再將 XML 數據轉換成關系表形式。
清單 10. 獲取鎖等待事件數據
db2 set event monitor lockwaitevm state 0
db2 "call EVMON_FORMAT_UE_TO_TABLES ( 'LOCKING', NULL, NULL, N
ULL, NULL, NULL, 'RECREATE_FORCE', -1,
'SELECT * FROM lockwaitevm ORDER BY event_timestamp')"
返回狀態 = 0
db2 list tables for all |find /i "lock"
表 / 視圖 模式 類型 創建時間
LOCKWAITEVM DB2ADMIN T 2009-11-18-14.47.43.281002
LOCK_ACTIVITY_VALUES DB2ADMIN T 2009-11-18-15.35.24.375003
LOCK_EVENT DB2ADMIN T 2009-11-18-15.35.24.078004
LOCK_PARTICIPANTS DB2ADMIN T 2009-11-18-15.35.24.296003
LOCK_PARTICIPANT_ACTIVITIES DB2ADMIN T 2009-11-18-15.35.24.343003
我們看到經過格式化後,系統中多了幾張表,名稱為 LOCK_ACTIVITY_VALUES、LOCK_EVENT、LOCK_PARTICIPANTS、 LOCK_PARTICIPANT_ACTIVITIES。表 LOCK_EVENT 對應發生的鎖定事件,每個事件對應一條記錄。LOCK_PARTICIPANTS 標識事件的參與者,每個參與的應用程序一行。LOCK_PARTICIPANT_ACTIVITIES 中包含了參與事件的應用程序曾經和當前正在執行的語句。這些表中都有一個 XMLID 列來標識一個事件,格式為 <event_header>_<event_id>_<event_type>_<event_timestamp>_<partition> 組成。對所鎖等待事件,event_header 為 db2LockEvent,event_type 為 LOCKWAIT。
我們使用下面腳本查詢鎖等待事件參與者。
清單 11. 查詢鎖事件參與者
select substr(XMLID,1,64) as XML_id,
EVENT_ID,
substr(EVENT_TYPE,1,32) evn_type ,
MEMBER,
DL_CONNS,
ROLLED_BACK_PARTICIPANT_NO
from LOCK_EVENT;
select substr(XMLID,1,64) as XML_id,
PARTICIPANT_NO,
PARTICIPANT_TYPE,
PARTICIPANT_NO_HOLDING_LK,
APPLICATION_HANDLE,
LOCK_WAIT_VAL,
LOCK_NAME,
LOCK_OBJECT_TYPE,
LOCK_ATTRIBUTES,
LOCK_CURRENT_MODE,
LOCK_MODE_REQUESTED,
LOCK_MODE, LOCK_COUNT,
LOCK_HOLD_COUNT,
LOCK_RRIID,
LOCK_STATUS,
LOCK_RELEASE_FLAGS,
TABLE_FILE_ID,
TABLE_NAME,
TABLE_SCHEMA,
TABLESPACE_NAME,
THRESHOLD_ID,
THRESHOLD_NAME
from LOCK_PARTICIPANTS;
通過查詢,我們發現表 LOCK_EVENT 中有一條記錄,事件類型為 LOCKWAIT,發生時間為 2009-11-18-15.29.51.094867。表 LOCK_PARTICIPANTS 中有兩條記錄,一條記錄的 PARTICIPANT_TYPE 為 Requester,應用程序句柄為 14 即 APP2,LOCK_OBJECT_TYPE 為 2 表示為行鎖,LOCK_MODE_REQUESTED 為 5 表示請求的鎖類型為排他鎖(X 鎖),請求鎖定的表為 STOCK,而行上目前加的鎖 LOCK_MODE 為 5 表示排他鎖(X 鎖)。表 LOCK_PARTICIPANTS 另一條記錄 PARTICIPANT_TYPE 為 Owner,應用程序句柄為 7 即 APP1。這意味著 APP1 為鎖的擁有者,在行上持有了 X 鎖導致應用程序 2 鎖等待。如果我們希望進一步了解參與者因為執行了何種語句導致鎖等待,我們需要進一步查詢表 LOCK_PARTICIPANT_ACTIVITIES。
清單 12. 查詢鎖事件參與者的活動
SELECT
PARTICIPANT_NO,
ACTIVITY_TYPE,
substr(STMT_TEXT,1,256) sql_text
FROM LOCK_PARTICIPANT_ACTIVITIES;
對清單 12 的查詢結果,我們重點關注 ACTIVITY_TYPE 和 SQL。活動類型取值 current 表示應用正在執行某個 SQL,取值 past 表示該 SQL 已經執行完成。上述查詢結果在顯示 PARTICIPANT_NO 為 1(與清單 11 查詢語句關聯,即為應用程序 2)正在執行 Update 語句,PARTICIPANT_NO 為 2(與清單 11 查詢語句關聯,即為應用程序 1)則已經完成 Update 語句執行。
清單 13. 引起鎖等待的 SQL
PARTICIPANT_NO ACTIVITY_TYPE SQL_TEXT
1 current update db2admin.stock set qty=qty+1 where itemno=300
2 past update db2admin.stock set qty=qty+1 where itemno=300
鎖超時事件
為避免應用程序處於無限等待狀態,我們可以為應用程序設定鎖超時。這可以通過修改數據庫配置參數 LOCKTIMEOUT 為所有應用程序設置超時時間,也可以通過為特定會話設置 CURRENT LOCK TIMEOUT 特殊寄存器達到這個目的。本文在兩個應用程序的命令行中設置 CURRENT LOCK TIMEOUT 特殊寄存器為 30 秒給 APP1 和 APP2 限定超時時間,然後重新執行 Update 語句。為避免與鎖等待的事件信息混淆,我們刪除事件、刪除非格式化表、刪除格式化結果表
清單 14. 為 APP1 和 APP2 會話設置鎖超時
db2 "set current lock timeout 30"
清單 15. 創建鎖超時事件
db2 " drop event monitor LOCKWAITEVM"
db2 " drop table LOCKWAITEVM"
db2 " drop table LOCK_ACTIVITY_VALUES"
db2 " drop table LOCK_EVENT"
db2 " drop table LOCK_PARTICIPANTS"
db2 " drop table LOCK_PARTICIPANT_ACTIVITIES "
db2 create event monitor locktimeout for locking
WRITE TO UNFORMATTED EVENT TABLE
db2 set event monitor locktimeout state 1
清單 16. 應用 1 修改 stock 表數據
db2 +c "update db2admin.stock set qty=qty+1 where itemno=259"
清單 17. 應用 2 修改 stock 表數據
db2 +c "update db2admin.stock set qty=qty+1 where itemno=259"
DB21034E 該命令被當作 SQL 語句來處理,因為它是無效的“命令行處理器”命令。
在 SQL 處理期間,它返回:SQL0911N 因為死鎖或超時,所以當前事務已回滾。
原因碼為 "68"。 SQLSTATE=40001
在經過 30 秒鐘的等待後,應用 2 返回 SQL0911N 錯,原因碼為 68 表示發生了鎖超時。我們使用下面程序格式化事件數據。
清單 18. 格式化鎖超時事件數據
db2 set event monitor locktimeout state 0
db2 "call EVMON_FORMAT_UE_TO_TABLES
( 'LOCKING', NULL, NULL, NULL, NULL, NULL, 'RECREATE_FORCE', -1,
'SELECT * FROM locktimeout ORDER BY event_timestamp')"
我們查詢 LOCK_EVENT 發現存在兩個事件,一個事件為 LOCKWAIT 一個事件為 LOCKTIMEOUT。這表明在鎖超時事件發生之前,由於等待時間超過了 5 秒(參數 MON_LW_THRESH 設置的值),先發生了鎖等待事件,然後才發生鎖超時。查詢表 LOCK_PARTICIPANTS 顯示相關鎖事件的參與方,查詢表 LOCK_PARTICIPANT_ACTIVITIES 顯示參與者執行 SQL 的歷史。
清單 19. 鎖超時事件
db2 "select substr(XMLID,1,64) XML_id from LOCK_EVENT"
XML_ID
----------------------------------------------------------------
db2LockEvent_2_LOCKWAIT_2009-11-18-17.34.09.292636_0
db2LockEvent_3_LOCKTIMEOUT_2009-11-18-17.34.34.292606_0
db2 "select PARTICIPANT_NO as p_no, PARTICIPANT_TYPE as p_type,
PARTICIPANT_NO_HOLDING_LK as p_no_holdling_lk, APPLICATION_HANDLE as
appl_hanle,substr(TABLE_NAME,1,10) as t_name from LOCK_PARTICIPANTS"
P_NO P_TYPE P_NO_HOLDLING_LK APPL_HANLE T_name
1 Requester 2 14 STOCK
2 Owner - 7 -
死鎖事件
兩個應用程序有沖突的鎖需求,不能完成應用事務的情況稱為死鎖。出現死鎖時,一個應用擁有資源 A 的鎖,去申請資源 B 的鎖,另一個應用擁有資源 B 的鎖,去申請資源 A 的鎖。雙方都處於鎖等待狀態,並且也不釋放自己擁有的鎖,導致無限循環等待。死鎖一般都是由於應用使用資源時順序不當導致的,可以通過建立資源訪問規則避免死鎖。
DB2 在 V9.7 版本以前,默認為數據庫建立了一個死鎖監視器,並啟動一個死鎖檢查進程定期啟動檢查是否有死鎖存在,默認檢查間隔為 10 秒(DLCHKTIME 參數控制)。DB2 V9.7 建議不再使用死鎖監視器,建議采用鎖監視。
清單 20. 創建死鎖事件監視
db2 " drop event monitor locktimeout "
db2 " drop table LOCKTIMEOUT"
db2 " drop table LOCK_ACTIVITY_VALUES"
db2 " drop table LOCK_EVENT"
db2 " drop table LOCK_PARTICIPANTS"
db2 " drop table LOCK_PARTICIPANT_ACTIVITIES "
db2 create event monitor lockdead for locking
WRITE TO UNFORMATTED EVENT TABLE
db2 set event monitor lockdead state 1
我們依次在 APP1 和 APP2 連接下執行下面 SQL:
清單 21. APP1 更新 ITEMNO 為 300 的行
db2 +c "update db2admin.stock set qty=qty+1 where itemno=300 "
清單 22. APP2 更新 ITEMNO 為 259 的行
db2 +c "update db2admin.stock set qty=qty+1 where itemno=259 "
清單 23. APP1 更新 ITEMNO 為 259 的行
db2 +c "update db2admin.stock set qty=qty+1 where itemno=259 "