DB2性能優(yōu)化 – 如何通過調(diào)整鎖參數(shù)優(yōu)化鎖升級

1、概念描述

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

一般涉及到鎖升級優(yōu)化的參數(shù)調(diào)整，涉及的參數(shù)有以下幾個：

LOCKTIMEOUT

LOCKLIST

MAXLOCKS

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

LOCKTIMEOUT（鎖定超時）----此參數(shù)指定應(yīng)用程序?yàn)楂@取一個鎖定將等待的秒數(shù)，以幫助避免應(yīng)用程序出現(xiàn)全局死鎖。默認(rèn)值-1 [-1; 0 - 32 767 ]

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

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

通過以下方法，可查看每個數(shù)據(jù)庫對于鎖的相應(yīng)配置

$ 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、鎖升級的產(chǎn)生及影響

什么時候會發(fā)生鎖升級呢？

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

DB2 數(shù)據(jù)庫主要在以下兩種情形時會進(jìn)行鎖升級：

1)       當(dāng)一個應(yīng)用的鎖所使用的內(nèi)存 >LOCKLIST × MAXLOCKS

2)       多個應(yīng)用的鎖使用的內(nèi)存 >LOCKLIST

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

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

1. 不同事務(wù)對于同一張表引發(fā)的鎖升級會誘發(fā)死鎖（deadlock）

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

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

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

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

3、鎖監(jiān)控部署

查看 DB2 實(shí)例（instance）級別的數(shù)據(jù)庫通知日志（notification log）

• 日志路徑（默認(rèn)）：$/db2home/db2inst1/sqllib/db2dump/db2inst1.nfy

• 預(yù)置條件：需要預(yù)先打開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 數(shù)據(jù)庫（database）級別的數(shù)據(jù)庫診斷日志（diagnosing log）

• 日志路徑（默認(rèn)）：$/db2home/db2inst1/sqllib/db2dump/db2diag.log

• 預(yù)置條件：需要預(yù)先打開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.

利用自帶的數(shù)據(jù)庫 snapshot 快照

• DB2 數(shù)據(jù)庫快照可以用來采集一段時間范圍內(nèi)數(shù)據(jù)庫活動的一些統(tǒng)計(jì)信息以及某個時間點(diǎn)數(shù)據(jù)庫的狀態(tài)信息等

• 打開監(jiān)視器：db2 -v update monitor      switches using lock on

• 啟用監(jiān)視器：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、鎖升級優(yōu)化

鎖升級問題發(fā)生后如何通過參數(shù)設(shè)置來優(yōu)化？

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

2.       LOCKLIST配置參數(shù)的計(jì)算方法如下(操作系統(tǒng)為64位平臺)：

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

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

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

3.       如果在某個可行方案中將 MAXAPPLS設(shè)置為 AUTOMATIC，那么也應(yīng)該將 LOCKLIST設(shè)置為 AUTOMATIC。

4.       MAXLOCKS配置參數(shù)的計(jì)算方法如下：

MAXLOCKS = 100 * (512鎖/應(yīng)用程序 * 32字節(jié)/鎖 *2)/(LOCKLIST * 4096字節(jié))

該公式允許任何應(yīng)用程序持有的鎖是平均數(shù)的兩倍。如果只有幾個應(yīng)用程序并發(fā)地運(yùn)行，則可以增大MAXLOCKS，因?yàn)樵谶@些條件下鎖列表空間中不會有太多爭用。

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

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

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

7.       maxlocks 參數(shù)乘以 maxappls 參數(shù)不能小于 100。

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

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

鎖升級導(dǎo)致異常問題確認(rèn)后，如何解決 ?

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

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

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

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

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

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".

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

原本的db參數(shù)設(shè)置為：

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

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

Lock timeout (sec)                        (LOCKTIMEOUT) = 60

對LOCKLIST進(jìn)行了更改，直接double

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

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

Lock timeout (sec)                        (LOCKTIMEOUT) = 60

現(xiàn)在沒有了鎖升級的報(bào)警了。

對于原本的配置可以算出這些內(nèi)存空間最多可以鎖住多少行的數(shù)據(jù)：

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

我們再比對一下官方的文檔（我們的系統(tǒng)為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 一百多萬行，我們通過這個計(jì)算結(jié)果與db2diag.log中的sql語句需要的鎖相比較，沒有超過這個值的鎖升級成功，而在我們改過參數(shù)后——

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

204800000/56=3657142

發(fā)現(xiàn)有三百多萬行鎖升級成功的記錄：

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.

    其實(shí)關(guān)于鎖的內(nèi)容相對較多，理解起來也相對復(fù)雜，建議多從實(shí)際演練中增加鎖升級的優(yōu)化經(jīng)驗(yàn)，多配置一些監(jiān)控工具以便對數(shù)據(jù)的分析。