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.

    其实关于锁的内容相对较多，理解起来也相对复杂，建议多从实际演练中增加锁升级的优化经验，多配置一些监控工具以便对数据的分析。