DB2 Performance Optimization – How to optimize lock escalation by adjusting lock parameters

1 , Concept description

The so-called lock upgrade (lock escalation), is a mechanism of the database, in order to save memory overhead, it will be a large number of high-resource-intensive fine-grained locks into a small amount of relatively small resources to occupy a coarse-grained lock, In most cases, the main point is to upgrade a large number of row locks to a table lock. Of course, DB2 supports a number of granularity locks, such as tablespace (table space), table, row, index, and so on.

Generally involves the lock upgrade optimization parameter adjustment, involves the following several parameters:

LOCKTIMEOUT

Locklist

MAXLOCKS

Let's take a look at the definitions of these parameters:

LOCKTIMEOUT (lock timeout)----This parameter specifies the number of seconds the application will wait to get a lock to help prevent the application from having global deadlock. Default value-1 [-1; 0-32 767]

Locklist----This parameter indicates the amount of memory allocated to the locked list. Each database has a locked list that contains locks held by all applications that are concurrently connected to the database. Locking is a mechanism that the Database Manager uses to control the concurrent access of multiple applications to data in the database. Both rows and tables can be locked. Default value automatic [4–524288]

MAXLOCKS----This parameter defines the percentage of the pending lock list for the application, you must complete the list before the Database Manager performs a lockdown upgrade. When the number of locks held by any application reaches this percentage, the "row lock most" table is selected for lock escalation. Default value automatic [1–100]

You can view the appropriate configuration for a lock for each database by using the following methods

$ 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 , the generation and impact of lock escalation

When will the lock escalation occur?

In fact, for each lock, the DB2 database consumes a certain amount of memory resources to manage and maintain (generally speaking, the first lock added on an object requires 256 bytes, while the lock added on the object requires only 128 bytes from the second lock). Therefore, if a large number of row locks are requested on a table, in order to conserve the database resources, the "smart" database steward uses a table lock that locks the entire table instead of a large number of row locks, freeing up the resources that were used by the original lot of row locks. And this process is called lock escalation. Then, when the database will automatically upgrade row locks to table locks, lock escalation followed by what the law, how to predict the occurrence of lock escalation? Here you need to mention two DB2 database configuration parameters that affect database lock escalation:

The DB2 database is locked for upgrade in the following two scenarios:

1) when an app's lock is used by the memory >locklistxmaxlocks

2) memory >locklist used by multiple application locks

What happens if a lock is upgraded?

As this is the mechanism of self-control of the database, we inadvertently enjoy the benefits of the same time, is often plagued by this mechanism. It is obvious that, once the system concurrency and performance are reduced, the performance will decrease with parallel serial changes. Performance degradation can be caused by the following possible causes:

1. Lock escalation caused by different transactions for the same table induces deadlocks (deadlock)

2. After a lock upgrade occurs, because concurrent requests to the same table are coerced into serial processing, the database is "misjudged" to lock waiting timeout if the lock waits for a long time, thus misleading the programmer to determine the root cause of the problem. At this point, it is often thought that the lock wait time caused by the deadlock is too long

3. When the percentage of locklist used by an application reaches Maxlocks, the database manager performs a lock escalation (lock escalation), in which the row locks are converted to a separate table lock. Also, if Locklist is nearly exhausted, the database Manager will find the connection that holds the most rows of locks on a table and convert those row locks into table locks to free up locklist memory. Locking the entire table can greatly reduce concurrency, and the chances of deadlocks will increase.

The issue of lock escalation is difficult to capture because it is not normally recorded in the Application log. Fortunately, DB2 provides several types of logs as well as some database tools to identify and locate similar problems.

Here are a few ways to explore the database lock escalation problem for your reference. Some are based on event capture type, some are based on statistics, can be self-judged in which way to suit themselves:

3 , lock monitoring deployment

View the database notification log at the DB2 instance (instance) level (notification log)

• Log path (default): $/db2home/db2inst1/sqllib/db2dump/db2inst1.nfy

• Pre-condition: The snapshot lock monitor needs to be opened beforehand, and after DB2 v9.7, it can set mon_lck_msg_lvl = 1 (our example version below is DB2 v9.1)

The following is an example of a lock upgrade log for the notification 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".

View the database diagnostic log at the DB2 database level (diagnosing log)

• Log path (default): $/db2home/db2inst1/sqllib/db2dump/db2diag.log

• Pre-condition: Snapshot lock monitor needs to be opened beforehand, after v9.7, set mon_lck_msg_lvl = 1 (our example version is DB2 v9.1)

The following is an example of a lock upgrade log for this diagnostic 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 "is successful.

take advantage of your own database snapshot snapshots

• DB2 database snapshots can be used to capture some statistical information about database activity over a period of time, as well as state information for a point-in-time database.

• Turn on the monitor: DB2-V update monitor switches using lock on

• Enable Monitor: Db2-v commit/db2-v Terminate

• Collection snapshot: db2-v get snapshot for database on Bassdb | Grep-i Lock

Here is an example of the output sample

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 , lock upgrade optimization

How does the lock escalation problem occur after the parameter settings are optimized?

1. Lock escalation issues can be resolved by increasing the size of the locklist and maxlocks database parameters. However, if you still experience a locking problem, you should check whether the lock on the updated row was not freed because the transaction could not be committed.

2. The locklist configuration parameters are calculated as follows (the operating system is a 64-bit platform):

(1) Calculate the lower limit of the lock list size: (MAXAPPLS * + *)/4096. Where 512 is the average number of locks per application, and 32 is the number of bytes required per lock on the object (already a lock). (40 bits are required on 32-bit platforms, 64 bits are required on 64-bit platforms).

(2) Calculate the upper limit of the lock list size: (MAXAPPLS * + *)/4096. Where 128 is the number of bytes required for the first lock on an object. (80 bits are required on 32-bit platforms and 128 bits on 64-bit platforms).

(3) for your data, estimate the number of concurrent numbers you might have, and choose an initial value based on your expected lock list, which is between the upper and lower limits that you calculated.

3. If Maxappls is set to AUTOMATIC in a viable scenario, the locklist should also be set to AUTOMATIC.

4. The maxlocks configuration parameters are calculated as follows:

MAXLOCKS = 100 * (512 lock/Application * 32 Bytes/Lock *)/(Locklist * 4096 bytes)

This formula allows any application to hold a lock that is twice times the average. If only a few applications run concurrently, you can increase the maxlocks because there is not much contention in the lock list space under these conditions.

5. maxlocks * locklist * 4096/(100 * 64) (on 64-bit systems in addition to the HP-UX environment)

4096 is the number of bytes in a page, 100 is the maximum percentage value allowed for Maxlocks, and 64 is the number of bytes per lock. Assuming you determine that one application requires 1000 locks, and you do not want a lock upgrade to occur, you should select values for Maxlocks and locklist in the formula so that the results are greater than 1000. Using 10 for Maxlocks and 100 for locklist, the formula will produce more than 1000 locks required.

6. The Locklist value is adjusted with the Maxlocks parameter, so the maxlocks parameter self-tuning feature is also automatically disabled if the locklist parameter Self-tuning feature is disabled. The Maxlocks parameter Self-tuning feature is also automatically enabled if the Locklist parameter Self-tuning feature is enabled.

7. The maxlocks parameter multiplied by the MAXAPPLS parameter cannot be less than 100.

8. maxlocks = 2 * 100/MAXAPPLS (where 2 is used to complete the two-time average, and 100 represents the maximum allowable percent value.) ）

9. maxlocks = 2 * 100/(average number of concurrently running applications) (this formula can be used instead if there are only a few concurrently running applications.) ）

How do I resolve a lock escalation that causes an exception issue to be confirmed?

Referring to the description of the conditions in which the lock escalation occurred, it is clear that we have the following ways to avoid lock escalation as much as possible:

1. Keep the MAXLOCKS constant and increase the value of Locklist: DB2 increases the overall memory capacity assigned to the lock list. This will increase the number of locks that can be held by any application before the lock upgrade if the maximum percentage of lock lists that a single application can hold is constant. This configuration is better suited to situations where multiple applications in the system are likely to hold a large number of row locks.

2. Keep the locklist constant and increase the value of MAXLOCKS: DB2 does not increase the overall memory capacity allocated to the lock list, but increases the maximum percentage of lock lists that a single application can hold. This increases the number of locks that a particular application can hold before the lock is upgraded. This configuration is more appropriate for situations where only a few applications in the system are likely to hold a large number of row locks.

3. Also increase the value of Locklist and MAXLOCKS: DB2 increases the overall memory capacity allocated to the lock list and increases the maximum percentage of lock lists that a single application can hold. This configuration is more suitable for situations where the system memory capacity is more abundant.

Due to the limitation of the overall memory capacity of the system, it is not possible to increase the value of the above parameters indefinitely (since tuning this part of the lock memory related parameters will inevitably affect other memory-related settings), it is necessary to control the value of the parameter in a more reasonable range. Space is limited, the author here is a bit, interested readers can self-study. In addition, the appropriate increase in the value of the LOCKTIMEOUT can effectively avoid the lock wait caused by the timeout phenomenon. After all, we don't want the "Error" keyword to appear in our system log. Of course, DB2 has its own rollback mechanism, which will not lead to loss of business data.

5 , illustrative examples

Let's take a concrete example to understand:

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

Red part of the content indicates that there is a lock upgrade, but failed, the reason for failure to see reason Code adm5503e, here is the lock timeout, the simple solution is to increase the locktimeout but this is not good, If the memory has the remaining words or increase locklist better, of course, if there is no way to increase locklist, then you have to start from the program:

The original DB parameter is set to:

Max Storage for lock list (4KB) (locklist) = 50000

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

Lock Timeout (sec) (LOCKTIMEOUT) = 60

Changes were made to the locklist directly double

Max Storage for lock list (4KB) (locklist) = 100000

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

Lock Timeout (sec) (LOCKTIMEOUT) = 60

There is no alarm for lock escalation now.

For the original configuration, you can figure out how many rows of data these memory spaces can lock up:

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

Let's compare the official documentation (our system is 64-bit):

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

Depending on whether other locks is held on the object:

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

Depending on whether other locks is held on the object:

For our clients, we can lock up to 102400000/56=1828571 more than 1 million rows, we compare the results with the lock required by the SQL statement in the Db2diag.log, and the lock upgrade without exceeding this value succeeds, and after we have changed the parameters--

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

204800000/56=3657142

A record of more than 3 million row lock upgrade successes was found:

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.

in fact, the content of the lock is relatively more, understanding is relatively complex, it is recommended to increase the optimization of lock upgrade experience, more configuration of some monitoring tools to analyze the data.

DB2 Performance Optimization – How to optimize lock escalation by adjusting lock parameters