DB2性能優化 – 如何通過調整鎖參數優化鎖升級

1、概念描述

所謂的鎖升級（lock escalation），是數據庫的一種作用機制，為了節約內存的開銷， 其會將為數眾多并占用大量資源的細粒度的鎖轉化為數量較少的且占用相對較少資源的粗粒度的鎖，多數情況下主要指將為數眾多的行鎖升級為一個表鎖。 當然，DB2 支持很多粒度的鎖，如表空間（table space），表（table），行（row）以及索引（index）等。

一般涉及到鎖升級優化的參數調整，涉及的參數有以下幾個：

LOCKTIMEOUT

LOCKLIST

MAXLOCKS

下面我們大概了解一下這幾個參數的定義：

LOCKTIMEOUT（鎖定超時）----此參數指定應用程序為獲取一個鎖定將等待的秒數，以幫助避免應用程序出現全局死鎖。默認值-1 [-1; 0 - 32 767 ]

LOCKLIST----此參數指示分配給鎖定列表的內存量。每個數據庫都有一個鎖定列表，鎖列表包含了并發連接到該數據庫的所有應用程序所持有的鎖。鎖定是數據庫管理器用來控制多個應用程序并發訪問數據庫中數據的機制。行和表都可以被鎖定。默認值automatic [4 – 524288]

MAXLOCKS----此參數定義應用程序掛起的鎖定列表的百分比，必須在數據庫管理器執行鎖定升級之前填寫該列表。當任何一個應用程序持有的鎖數量達到這個百分比時，會選取“行鎖最多”的表進行鎖升級。默認值 automatic [1 – 100]

通過以下方法，可查看每個數據庫對于鎖的相應配置

$ db2 get db cfg for <dbname>|grep -i lock

 Max storage for lock list (4KB)              (LOCKLIST) = 4096

 Percent. of lock lists per application       (MAXLOCKS) = 10

 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000

 Lock timeout (sec)                        (LOCKTIMEOUT) = -1

 Block log on disk full                (BLK_LOG_DSK_FUL) = NO

 Block non logged operations            (BLOCKNONLOGGED) = NO

 Lock timeout events                   (MON_LOCKTIMEOUT) = NONE

 Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST

 Lock wait events                         (MON_LOCKWAIT) = NONE

 Lock wait event threshold               (MON_LW_THRESH) = 5000000

 Lock event notification level         (MON_LCK_MSG_LVL) = 1

2、鎖升級的產生及影響

什么時候會發生鎖升級呢？

其實對每一個鎖，DB2 數據庫都要耗費一定的內存資源來管理并維護（一般情況下，對某個對象上所加的第一個鎖需要 256 字節，而在該對象上所加的鎖從第二個鎖開始就只需要 128 字節了）。 因此，如果在一個表上，有大量的行鎖被請求時，為了節約數據庫資源的占用，“聰明的”數據庫管家會用一個鎖住整個表的表鎖來代替為數眾多的行鎖，從而釋放了原本大量行鎖所占用的資源。 而這個過程，就被稱之為鎖升級。那么，數據庫什么時候會將行鎖自動升級為表鎖、鎖升級遵循怎樣的規律、該如何預測鎖升級的發生呢？ 這里就需要提到兩個影響數據庫鎖升級的 DB2 數據庫配置參數：

DB2 數據庫主要在以下兩種情形時會進行鎖升級：

1)       當一個應用的鎖所使用的內存 >LOCKLIST × MAXLOCKS

2)       多個應用的鎖使用的內存 >LOCKLIST

那如果發生鎖升級了，會有什么影響？

由于這是數據庫自行控制的機制，我們在不經意之間享受到了好處的同時，也常常受到該機制的困擾。 顯而易見，一旦降低了系統并發性及性能，并行改串行，性能會隨之降低。性能降低可由以下幾個可能的原因產生：

1. 不同事務對于同一張表引發的鎖升級會誘發死鎖（deadlock）

2. 在鎖升級發生后，由于同表的并發請求被強制轉換成串行處理，如果鎖等待的時間不是足夠長的話，會被數據庫“誤判”為 lock      waiting timeout，從而誤導程序員判斷問題的根本原因。此時，常常會被認為是由于死鎖引起的鎖等待時間過長

3. 當一個應用程序使用的LOCKLIST的百分比達到MAXLOCKS時，數據庫管理器將執行一次鎖升級(lock escalation)，在這個操作中將使行鎖轉換成單獨的一個表鎖。而且，如果LOCKLIST快要耗盡，數據庫管理器將找出持有一個表上最多行鎖的連接，并將這些行鎖轉換成表鎖，以釋放LOCKLIST內存。鎖定整個表會大大降低并發性，死鎖的幾率也就增加了。

鎖升級的問題，由于一般不在應用程序日志里面進行記錄，所以很難被捕獲到。幸運的是DB2提供了多種類型的日志以及一些數據庫工具來確認和定位類似問題的發生。

以下將介紹幾種關于數據庫鎖升級問題的探知方法供各位參考。有的是基于事件捕獲型的，有的是基于統計的，可通過自行判斷用哪種方式適合自己：

3、鎖監控部署

查看 DB2 實例（instance）級別的數據庫通知日志（notification log）

• 日志路徑（默認）：$/db2home/db2inst1/sqllib/db2dump/db2inst1.nfy

• 預置條件：需要預先打開snapshot的 lock monitor；在 DB2 v9.7 之后，可以 set mon_lck_msg_lvl = 1(我們下面的示例版本是DB2      v9.1)

下面是該通知日志的一個鎖升級log示例

2017-03-18-01.34.29.630201    Instance:db2inst1    Node:001

PID:28236(db2agntp (BASSDB) 1)   TID:1   Appid:172.16.5.54.54061.170317172607

data management   sqldEscalateLocks Probe:2

ADM5500W  DB2 is performing lock escalation.  The total number of locks

currently held is “57630”, and the target number of locks to hold is “28815”.

查看 DB2 數據庫（database）級別的數據庫診斷日志（diagnosing log）

• 日志路徑（默認）：$/db2home/db2inst1/sqllib/db2dump/db2diag.log

• 預置條件：需要預先打開snapshot的 lock monitor；在 v9.7 之后，可以 set mon_lck_msg_lvl = 1(我們下面的示例版本是DB2      v9.1)

下面是該診斷日志的一個鎖升級log示例

2017-03-18-01.34.29.667386+480  E10178829A480    LEVEL: Warning

PID     : 28236(db2agntp (BASSDB) 1)   TID  : 1       PROC: db2agntp (BASSDB) 1

Instance : db2inst1                     Node :001

APPHDL : 0-22-1                      Appid:172.16.5.54.54061.170317172607

AUTHID : BASS2

FUNCTION : DB2 UDB, data management   sqldEscalateLocks, probe:3

MESSAGE ：ADM5502W  The escalation of “57624” locks on table “BASS2.DWD_CUST_RELATION_20170317” to lock intent “X” was successful.

利用自帶的數據庫 snapshot 快照

• DB2 數據庫快照可以用來采集一段時間范圍內數據庫活動的一些統計信息以及某個時間點數據庫的狀態信息等

• 打開監視器：db2 -v update monitor      switches using lock on

• 啟用監視器：db2 -v commit / db2 -v      terminate

• 收集快照：db2 -v get snapshot for      database on bassdb | grep -i lock

下面是輸出樣例

Locks held currently                          = 2541

Lock waits                                  = 38884

Time database waited on locks (ms)            = 659308372

Lock list memory in use (Bytes)                = 648832

Deadlocks detected                         = 110

Lock escalations                            = 0

Exclusive lock escalations                    = 0

Agents currently waiting on locks             = 0

Lock Timeouts                             = 159

Internal rollbacks due to deadlock            = 327

Memory Pool Type                           = Lock Manager Heap

4、鎖升級優化

鎖升級問題發生后如何通過參數設置來優化？

1.       鎖升級問題可以通過增加LOCKLIST和MAXLOCKS數據庫參數的大小來解決。但是，如果仍然遇到鎖定問題，應檢查是否因未能提交事務而未釋放已更新行上的鎖。

2.       LOCKLIST配置參數的計算方法如下(操作系統為64位平臺)：

(1) 計算鎖列表大小的下限：(512 * 32 * MAXAPPLS)/4096。其中，512是每個應用程序平均所含鎖數量的估計值，32是對象(已有一把鎖)上每把鎖所需的字節數。（在 32 位平臺上需要 40 位，那么 64 位平臺上需要 64 位）。

(2) 計算鎖列表大小的上限：(512 * 128 * MAXAPPLS)/4096。其中，128是某個對象上第一把鎖所需的字節數。（在 32 位平臺上需要 80 位，在 64 位平臺上需要 128 位）。

(3)對于您的數據，估計可能具有的并發數，并根據您的預計為鎖列表選擇一個初始值，該值位于您計算出的上限和下限之間。

3.       如果在某個可行方案中將 MAXAPPLS設置為 AUTOMATIC，那么也應該將 LOCKLIST設置為 AUTOMATIC。

4.       MAXLOCKS配置參數的計算方法如下：

MAXLOCKS = 100 * (512鎖/應用程序 * 32字節/鎖 *2)/(LOCKLIST * 4096字節)

該公式允許任何應用程序持有的鎖是平均數的兩倍。如果只有幾個應用程序并發地運行，則可以增大MAXLOCKS，因為在這些條件下鎖列表空間中不會有太多爭用。

5.       maxlocks * locklist * 4096 /(100 * 64)（在除HP-UX 環境的 64 位系統上）

4096 是一頁中的字節數，100 是允許 maxlocks 具有的最大百分比值，64 是每個鎖定的字節數。假設確定其中一個應用程序需要 1000 個鎖定，并且您不希望發生鎖定升級，那么應為該公式中的 maxlocks 和 locklist 選擇值，以便結果大于 1000。對 maxlocks 使用 10 并且對 locklist 使用 100，該公式將產生多于所需的 1000 個鎖定。

6.       LOCKLIST值是與 MAXLOCKS參數一起調整的，因此，如果禁用 LOCKLIST參數自調整功能，也將自動禁用 MAXLOCKS參數自調整功能。如果啟用 LOCKLIST參數自調整功能，也將自動啟用 MAXLOCKS參數自調整功能。

7.       maxlocks 參數乘以 maxappls 參數不能小于 100。

8.       maxlocks = 2 * 100 / maxappls（其中 2 用來完成兩次平均，而 100 表示允許的最大百分比值。）

9.       maxlocks = 2 * 100 / (并發運行的應用程序的平均數目) （如果僅有幾個并發運行的應用程序，可用此公式代替。）

鎖升級導致異常問題確認后，如何解決 ?

參照前文所述導致發生鎖升級的發生條件中的描述，顯而易見我們有如下的方式來盡可能的避免鎖升級：

1. 保持 MAXLOCKS 不變，加大 LOCKLIST 的值：DB2 會增加分配給鎖列表的總體內存容量。這樣在單個應用程序能夠持有的鎖列表的最大百分比不變的情況下，      任意一個應用程序在鎖升級前能夠持有的鎖的數量都會有所增加。該配置比較適合系統中有多個應用程序都有可能持有大量行鎖的場合。

2. 保持 LOCKLIST 不變，加大 MAXLOCKS 的值：DB2 不會增加分配給鎖列表的總體內存容量，但會增大單個應用程序能夠持有的鎖列表的最大百分比。      這樣某個特定的應用程序在鎖升級前能夠持有的鎖的數量會有所增加。該配置比較適合系統中只有少數的應用程序有可能持有大量行鎖的場合。

3. 同時加大 LOCKLIST 和 MAXLOCKS 的值：DB2 會同時增加分配給鎖列表的總體內存容量和增大單個應用程序能夠持有的鎖列表的最大百分比。      該配置比較適合系統內存容量比較充裕的場合。

由于系統整體內存容量的限制，不可能無限增大上述參數的值（因為調優了這部分鎖內存相關的參數之后勢必會影響其他內存相關的設置）， 所以需要在一個較為合理的范圍內控制該參數的取值。篇幅所限，筆者這里就一點而過，有興趣的讀者可以自行研究。此外，適當的加大 LOCKTIMEOUT 的設值可以有效的避免鎖等待而導致的超時現象。 畢竟我們都不希望有“Error”關鍵字出現在我們的系統日志當中。當然 DB2 有自己的回滾機制，不至于會出現業務數據遭到損失的情況。

5、舉例說明

下面我們用一個具體的例子來理解：

2017-02-23-14.21.20.342532   Instance:db2inst1   Node:000

PID:253627(db2agent (BASSDB) 0)   TID:1   Appid:*LOCAL.db2inst1.0600D1010730

data management  sqldEscalateLocks Probe:4   Database: BASSDB

ADM5503E  The escalation of "1428392" locks on table "BASS2.ODS_EXTENT_DAILY " to

lock intent "X" has failed.  The SQLCODE is "-911".

紅色部分內容說明了有鎖升級發生，但是失敗了，失敗的原因要看Reason Code ADM5503E, 這里的情況是lock timeout, 簡單的解決辦法是增加LOCKTIMEOUT 但是這并不好，如果內存還有剩余的話還是增加LOCKLIST比較好一點，當然了，如果沒辦法增加LOCKLIST的話，那就得從程序處著手了：

原本的db參數設置為：

Max storage for lock list (4KB)              (LOCKLIST) = 50000

Percent. of lock lists per application       (MAXLOCKS) = 50

Lock timeout (sec)                        (LOCKTIMEOUT) = 60

對LOCKLIST進行了更改，直接double

Max storage for lock list (4KB)              (LOCKLIST) = 100000

Percent. of lock lists per application       (MAXLOCKS) = 50

Lock timeout (sec)                        (LOCKTIMEOUT) = 60

現在沒有了鎖升級的報警了。

對于原本的配置可以算出這些內存空間最多可以鎖住多少行的數據：

50000*4K*50%*1024=102400000 Byte

我們再比對一下官方的文檔（我們的系統為64位）：

On 32-bit platforms, each lock requires 36 or 72 bytes of the lock list,

depending on whether other locks are held on the object:

On 64-bit platforms, each lock requires 56 or 112 bytes of the lock list,

depending on whether other locks are held on the object:

對于我們客戶的具體情況，最多可以鎖住 102400000/56=1828571 一百多萬行，我們通過這個計算結果與db2diag.log中的sql語句需要的鎖相比較，沒有超過這個值的鎖升級成功，而在我們改過參數后——

100000*4K*50%*1024=204800000 Byte

204800000/56=3657142

發現有三百多萬行鎖升級成功的記錄：

2017-02-23-14.35.21.435254   Instance:db2inst1   Node:000

PID:323542(db2agent (BASSDB) 0)   TID:1   Appid:*LOCAL.db2inst1.070D92045332

data management  sqldEscalateLocks Probe:3   Database:BASSDB

ADM5502 The escalation of "3124245" locks on table "BASS2.ODS_OTHER_DAY" to

lock intent "X" was successful.

    其實關于鎖的內容相對較多，理解起來也相對復雜，建議多從實際演練中增加鎖升級的優化經驗，多配置一些監控工具以便對數據的分析。