運維必備-解決鎖問題的全過程

之前有介紹過，我主要是做資料倉儲運維的，業餘也會動手寫 python 程式，django 應用，vue 的 app，有興趣可以加我好友一起學習。最近比較讓我頭疼的是資料倉儲的 datastage 作業經常報 911 錯誤，最終問題得到了解決，在此總結一下，希望能幫到要解決 911 錯誤的朋友們。

先介紹下資料倉儲的概念：

一個面向主題的、整合的、非易失性的、隨時間變化的資料的集合，以用於支援管理決策過程，是一個典型的 OLAP（Online analytical processing）系統。

資料倉儲的架構如下圖所示：

上圖中從資料的抽取、加工轉換、到資料集市、到最後的資料分析、報表，有數以萬計的加工程式，資料庫連線的程式也是成百上千，這也是最容易出現 911 報錯的場景。

1、什麼是 911 ？

911 是 db2 資料庫的一種錯誤碼，表示鎖超時或死鎖。鎖超時就是一個事務 A 需要的資源正在被別的事務 B 佔有，假如資料庫設定的超時時間為 60 秒，超過了 60 秒，事務 B 仍沒有釋放資源，那麼事務 A 將報鎖超時錯誤並回滾。而死鎖就是事務 A 需要的資源正在被事務 B 佔有，事務A 等待，事務 B 需要的資源正在被事務 A 佔有，事務 B 也等待，那麼就發生了死鎖，此時資料庫會選一個成本較小的事務進行回滾。

2、什麼情況會發生 911

我們已經知道 911 是關於鎖的一種錯誤，要麼是鎖超時，要麼是死鎖。那麼就需要對鎖有一個細緻地瞭解，上一篇文章介紹過【運維必備之db2 的鎖】，預設情況下，db2 的 select 語句的隔離級別是【遊標穩定性（Cursor Stability）】，即 select * from table with cs ， 這種隔離級別會在查詢的行上加上 NS 鎖，NS 鎖的中文名稱叫【下一鍵共享鎖（Next Share）】，擁有者與其他程式都可以讀取該行，但不能進行修改。比如當一個程式(事務) A 正在查詢該行時，程式(事務) B 試圖更新（update)該行，那麼程式(事務) B 就會等待，如果超過 60 秒（假如資料庫設定超時為 60 秒），程式(事務) A 仍在查詢中，沒有釋放，那麼程式(事務) B 就會回滾，並報 911 錯誤，有些程式還會提示原因碼為 68。

而有些工具為了支援併發，如 datastage ，會將一個批量更新或插入的語句拆分成多個程式同時執行，從而提高效率。如果多個程式同時 update 相同的多行資料。 比如：一個程式(事務) A 已經修改行 1，但未提交，準備修改行 2 後一起提交，但行 2 上有排他鎖 X , 程式(事務) B 已經修改行 2，但未提交，準備修改行 1 後一起提交，但行 1 上有排他鎖 X，此時 A 與 B 互相等待，進入死鎖，死鎖後，資料庫會選擇一個事務進行回滾，一般選擇已花費成本較少的那個回滾，被回滾的那個事務會報 911 錯誤。

3、解決的過程

解決 911 的過程，就是要找到避免發生 911 的根本原因，減少事務發生 911 的條件。

定位問題

首先要定位出導致鎖等待或鎖超時的事務，db2 提供了鎖定事件監控器以捕獲其鎖定資料的活動。 這些型別包括:

• SQL 語句，例如：DML 、DDL 、CALL
• LOAD 命令
• REORG 命令
• BACKUP DATABASE 命令
• 實用程式請求

鎖定事件監控器會自動捕捉引起鎖等待、鎖超時、死鎖的詳細資訊，如鎖的擁有者，鎖的請求者，導致以上三種事件的SQL語句等。
資料庫相應的配置引數如下：

引數	捕捉型別
MON_LOCKWAIT	鎖等待
MON_LOCKTIMEOUT	鎖超時
MON_DEADLOCK	死鎖

還有一個引數 MON_LW_THRESH 是控制 MON_LOCKWAIT 的事件之前等待鎖定時花費的時間，以微秒為單位。
通過 db2 get db cfg 命令可以看到這些引數的值 :

$ db2 get db cfg
......省略
Lock timeout events                   (MON_LOCKTIMEOUT) = HIST_AND_VALUES
Deadlock events                          (MON_DEADLOCK) = HIST_AND_VALUES
Lock wait events                         (MON_LOCKWAIT) = NONE
Lock wait event threshold               (MON_LW_THRESH) = 30000000
......省略

MON_LOCKTIMEOUT 和 MON_DEADLOCK 取值範圍與 MON_LOCKWAIT 相同，含義類似：

• NONE，不會在任何分割槽中收集工作負載的鎖定超時資料。
• WITHOUT_HIST，發生鎖定事件時，會將關於鎖定事件的資料傳送到任何活動的鎖定事件監視器。不會將過去的活動歷史記錄以及輸入值傳送到事件監視器。
• WITH_HIST，對於所有此類鎖定事件，都可以收集當前工作單元中的過去活動歷史記錄。活動歷史緩衝區將在達到最大大小限制後迴繞。這意味著，要保留的過去活動數的預設限制是 250。如果過去活動的數目高於此限制，那麼將只報告最新的活動。
• HIST_AND_VALUES，對於那些包含輸入資料值的活動，會將那些輸入資料值傳送到任何活動的鎖定事件監視器。這些資料值不包括 LOB 資料、更改開始 LONG VARCHAR 資料、LONG VARGRAPHIC 資料、更改結束結構化型別資料或 XML 資料。

開啟鎖定事件監視器

自 DB2V9.7 版本之後，IBM 不推薦繼續使用早期的死鎖事件監視器（CREATE EVENT MONITOR FOR DEADLOCKS 語句和 DB2DETAILDEADLOCK），同時不推薦使用鎖定超時報告功能（DB2_CAPTURE_LOCKTIMEOUT 登錄檔變數）。不過在新建立的資料中，預設還是建立了 DB2DETAILDEADLOCK 事件，因此如果我們希望使用鎖定事件監視器，最好執行下面語句予以刪除。

清單 1. 刪除預設死鎖事件監視

#請使用例項使用者執行 **inst
db2 "SET EVENT MONITOR DB2DETAILDEADLOCK state 0 "
db2 "DROP EVENT MONITOR DB2DETAILDEADLOCK"
#下面是執行示例
$ db2 "SET EVENT MONITOR DB2DETAILDEADLOCK state 0 "
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "DB2DETAILDEADLOCK" is an undefined name.  SQLSTATE=42704
$ db2 "DROP EVENT MONITOR DB2DETAILDEADLOCK"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "DB2DETAILDEADLOCK" is an undefined name.  SQLSTATE=42704

如果出現以上的輸出資訊，說明預設的死鎖事件監視器已經被刪除了，可不理會。

清單 2. 修改鎖事件監控配置引數，並開啟事件監視器

#設定鎖定事件監視器的引數
$ db2 "update db cfg using MON_LOCKTIMEOUT HIST_AND_VALUES MON_DEADLOCK HIST_AND_VALUES MON_LOCKWAIT NONE"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
#建立鎖定事件監視器
$ db2 "create event monitor locktimeoutdeadlock for locking write to UNFORMATTED EVENT TABLE"
DB20000I  The SQL command completed successfully.
#開啟鎖定事件監視器
$ db2 "set event monitor locktimeoutdeadlock state 1"
SQL20156W  The event monitor was activated successfully, however some 
monitoring information may be lost.  SQLSTATE=01651

$ db2 get db cfg 
 ------------------------------- 省略 ------------------------- 
 Unit of work events                      (MON_UOW_DATA) = NONE
 Lock timeout events                   (MON_LOCKTIMEOUT) = HIST_AND_VALUES
 Deadlock events                          (MON_DEADLOCK) = HIST_AND_VALUES
 Lock wait events                         (MON_LOCKWAIT) = NONE
 Lock wait event threshold               (MON_LW_THRESH) = 5000000

上述示例中設定了 MON_LOCKWAIT 為 NONE，表示不捕捉鎖等待事件，當然也可以設定為捕捉，但鎖等待事件較多時會引起下述表記錄數的快速增長。

$ db2 list tables for all | grep -i locktimeoutdeadlock
LOCKTIMEOUTDEADLOCK             TESTINST         T     2018-12-05-11.57.47.835768

表 TESTINST.LOCKTIMEOUTDEADLOCK 儲存了捕捉到的事件資訊，但是非格式化的，這樣做的目的是減少鎖事件監控器對資料庫的效能消耗。為了更清楚地看出我們捕捉到的事件資訊，有兩種方法：

方法一、生成格式化的表

db2 "call EVMON_FORMAT_UE_TO_TABLES ( 'LOCKING', NULL, NULL, NULL, NULL, NULL, 'RECREATE_FORCE', -1, 'SELECT * FROM locktimeoutdeadlock ORDER BY event_timestamp')"

會生成以下和個格式化的表

$ db2 list tables for all | grep -i "lock_"
LOCK_ACTIVITY_VALUES            TESTINST         T     2018-12-03-14.51.20.975705
LOCK_EVENT                      TESTINST         T     2018-12-03-14.51.20.849810
LOCK_PARTICIPANT_ACTIVITIES     TESTINST         T     2018-12-03-14.51.20.912028
LOCK_PARTICIPANTS               TESTINST         T     2018-12-03-14.51.20.878247

裡面的資訊已經非常清楚了，讀者可以自行查詢分析，如果仍無法自行分析的，可參考官網的查詢語句進行分析：DB2 V9.7 鎖事件監控

方法二、生成格式化的文字檔案

/home/testinst/sqllib/java/jdk64/bin/java db2evmonfmt -d testdb -ue locktimeoutdeadlock -ftext >/tmp/lockinfo.txt

檢視 /tmp/lockinfo.txt 即可得到相關的鎖事件資訊，從而精準定位出鎖超時和死鎖的原因。下圖為 lockinfo.txt 的片斷，從中可以清楚地看到鎖的擁有者和請求者。

對策

定位出原因後就要想對策了。如果臨時處理：如果請求者事務的優先順序高，那就讓擁有鎖的事務回滾，可以通過
db2 "force application (agent id) “ 來結束事務，從而從擁有者那裡釋放鎖。再次開啟請求者事務即可。

長遠規劃 :
針對 911 超時：
1、如果資料庫的事務普遍比較耗時，可適當增加 db2 資料庫的鎖超時時間

$ db2 get db cfg| grep -i lock
 Lock timeout (sec)                        (LOCKTIMEOUT) = 120

2、如果事務的時效性要求並不高，可採取出錯重試的方式來解決，現在的排程工具都提供出錯重試功能，當一個任務偶爾報911 錯誤時可以設定重出錯自動重試，比如重試次數為 3 ，每次間隔 5 分鐘。

3、提升事務的隔離級別，假如有兩個事務 A和 B
,A 為更新操作，B 為讀取操作，預設情況下，如果 A 在更新時，B 讀取，如果B 讀取的時間過長，那麼 A
很有可能報鎖超時錯誤，此時可以提升 A 的隔離級別，可提升至
可重複讀級別，此時 A 在更新時， B 只能等待，或者允許 B 髒讀，即 select 語句 後面加 with ur，此時 B 讀取表時並不加行鎖。

4、優化 SQL 語句，提升查詢事務的效率，減少 SQL 執行時間；對於大資料量的更新或插入操作，可分步 commit ，減少鎖的佔用。

針對 911 死鎖：
提升事務的隔離級別也可有效避免死鎖，最有效的辦法還是找出導致死鎖的事務或程式，安排在不同地時間段執行。

（完）

公眾號 somenzz 堅持原創，和你一起學習技術。