Shulou(Shulou.com)06/01 Report--

1. Concept description

The so-called lock escalation (lock escalation) is a mechanism of database. In order to save memory overhead, it will convert a large number of fine-grained locks that occupy a lot of resources into a small number of coarse-grained locks with relatively few resources. In most cases, it mainly refers to upgrading a large number of row locks to a table lock. Of course, DB2 supports many granularities of locks, such as table spaces (table space), tables (table), rows (row), and indexes (index).

Generally speaking, the parameter adjustment of lock upgrade optimization is involved, and the following parameters are involved:

LOCKTIMEOUT

LOCKLIST

MAXLOCKS

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

LOCKTIMEOUT (lock timeout)-this parameter specifies the number of seconds that the application will wait to acquire a lock to help avoid global deadlocks in the application. Default value-1 [- 1; 0-32 767]

LOCKLIST---- this parameter indicates the amount of memory allocated to the lock list. Each database has a lock list that contains locks held by all applications that connect concurrently to the database. Locking is a mechanism used by the database manager to control multiple applications to access data in the database concurrently. Both rows and tables can be locked. Default value automatic [4-524288]

MAXLOCKS---- this parameter defines the percentage of lock lists that are pending by the application, which must be filled in before the database manager performs a lock upgrade. When any application holds this percentage of locks, the table with the most row locks is selected for lock upgrade. Default value automatic [1-100]

You can view the corresponding configuration of locks for each database in the following ways

$db2 get db cfg for | 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 emergence and influence of lock upgrade

When will the lock upgrade occur?

In fact, for each lock, the DB2 database consumes a certain amount of memory resources to manage and maintain (typically, the first lock on an object requires 256bytes, while the lock on that object needs only 128bytes from the second lock). Therefore, if a large number of row locks are requested on a table, in order to save database resources, the "smart" database housekeeper will replace a large number of row locks with a table lock that locks the entire table. thus releasing the resources occupied by a large number of row locks. This process is called lock upgrade. So, when will the database automatically upgrade row locks to table locks, what rules will lock escalation follow, and how to predict the occurrence of lock escalation? Two DB2 database configuration parameters that affect database lock upgrade need to be mentioned here:

Lock upgrades are mainly performed in DB2 databases in the following two situations:

1) when the memory used by an application lock is > LOCKLIST × MAXLOCKS

2) memory used by locks of multiple applications > LOCKLIST

So what will be the impact if the lock is upgraded?

Because this is a database self-control mechanism, we inadvertently enjoy the benefits, at the same time, we are often troubled by this mechanism. Obviously, once the concurrency and performance of the system are reduced, and the parallel is changed to serial, the performance will be reduced. Performance degradation can be caused by the following possible reasons:

Lock escalation caused by different transactions for the same table will induce deadlock (deadlock)

After the lock upgrade occurs, because the concurrent requests of the same table are forced to be processed in serial, if the waiting time of the lock is not long enough, it will be misjudged as lock waiting timeout by the database, thus misleading the programmer to judge the root cause of the problem. At this time, it is often thought that the waiting time of the lock caused by deadlock is too long.

When the percentage of LOCKLIST used by an application reaches MAXLOCKS, the database manager performs a lock upgrade (lock escalation), in which the row lock is converted to a separate table lock. Also, if LOCKLIST is running out, the database manager finds the connection that holds the most row locks on a table and converts those row locks into table locks to free up LOCKLIST memory. Locking the entire table greatly reduces concurrency and increases the chance of deadlock.

The problem of lock upgrade is difficult to catch because it is not generally recorded in the application log. Fortunately, DB2 provides many types of logs and database tools to identify and locate similar problems.

The following will introduce several methods to explore the upgrade of database locks for your reference. Some are based on event capture, some are based on statistics, and you can decide for yourself which way is right for you:

3. Lock monitoring deployment

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

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

Preset condition: the lock monitor; that needs to open snapshot in advance can be set mon_lck_msg_lvl = 1 after DB2 v9.7 (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 (diagnosing log) at the DB2 database (database) level

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

Preset condition: the lock monitor; of snapshot needs to be opened in advance. After v9.7, you 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 this diagnostic log

2017-03-18-01.34.29.667386480 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.

Take advantage of the database snapshot snapshot that comes with it

DB2 database snapshot can be used to collect some statistical information of database activity within a period of time and database status information at a certain point in time.

Open the monitor: db2-v update monitor switches using lock on

Enable the monitor: db2-v commit / db2-v terminate

Collect snapshots: db2-v get snapshot for database on bassdb | grep-I lock

Here is a sample output

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 to optimize through parameter setting after the lock upgrade problem occurs?

1. The lock escalation problem can be solved by increasing the size of the LOCKLIST and MAXLOCKS database parameters. However, if you still encounter locking problems, check to see if the lock on the updated row was not released because the transaction could not be committed.

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

(1) calculate the lower limit of the lock list size: (512 * 32 * MAXAPPLS) / 4096. Where 512 is an estimate of the average number of locks per application, and 32 is the number of bytes required for each lock on the object (which already has one lock). If you need 40 bits on a 32-bit platform, you need 64 bits on a 64-bit platform.

(2) calculate the upper limit of lock list size: (512 * 128 * 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 concurrency you may have, and select an initial value for the lock list based on your estimate, which lies between the upper and lower limits you calculated.

3. If MAXAPPLS is set to AUTOMATIC in a feasible scenario, then LOCKLIST should also be set to AUTOMATIC.

4. The MAXLOCKS configuration parameters are calculated as follows:

MAXLOCKS = 100 * (512 locks / applications * 32 bytes / locks * 2) / (LOCKLIST * 4096 bytes)

This formula allows any application to hold twice as many locks as the average. If only a few applications are running concurrently, you can increase the MAXLOCKS because there won't be too much contention in the lock list space under these conditions.

5. Maxlocks * locklist * 4096 / (100 * 64) (on 64-bit systems except HP-UX environments)

4096 is the number of bytes in a page, 100 is the maximum percentage allowed for maxlocks, and 64 is the number of bytes per lock. Assuming that you have determined that one of the applications requires 1000 locks and you do not want a lock upgrade to occur, select values for maxlocks and locklist in this formula so that the result is greater than 1000. Using 10 for maxlocks and 100 for locklist, the formula produces more than 1000 locks required.

6. The LOCKLIST value is adjusted in conjunction with the MAXLOCKS parameter, so if you disable the LOCKLIST parameter self-tuning feature, the MAXLOCKS parameter self-tuning feature is also automatically disabled. If you enable the LOCKLIST parameter self-tuning feature, the MAXLOCKS parameter self-tuning feature is also automatically 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 achieve two averages, and 100 represents the maximum percentage allowed. )

9. Maxlocks = 2 * 100 / (average number of applications running concurrently) (if there are only a few applications running concurrently, use this formula instead. )

After the abnormal problem caused by lock upgrade is confirmed, how to solve it?

Referring to the above description of the occurrence conditions that lead to lock escalation, it is obvious that we have the following ways to avoid lock escalation as much as possible:

Leave MAXLOCKS unchanged and increase the value of LOCKLIST: DB2 increases the overall memory allocated to the lock list. In this way, if the maximum percentage of lock lists that a single application can hold remains unchanged, the number of locks that any application can hold before the lock is upgraded will increase. This configuration is suitable for situations where multiple applications in the system are likely to hold a large number of row locks.

Leave LOCKLIST unchanged and increase the value of MAXLOCKS: DB2 does not increase the overall memory 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 suitable for situations where only a few applications in the system are likely to hold a large number of row locks.

Increase both LOCKLIST and MAXLOCKS values: DB2 increases both the overall memory allocated to the lock list and the maximum percentage of lock lists that a single application can hold. This configuration is more suitable for situations where the memory capacity of the system is abundant.

Due to the limitation of the overall memory capacity of the system, it is impossible to infinitely increase the value of the above parameters (because tuning this part of the lock memory-related parameters is bound to affect other memory-related settings). Therefore, it is necessary to control the value of this parameter within a reasonable range. Due to the limitation of space, the author will just pass by here, and interested readers can study it on their own. In addition, properly increasing the LOCKTIMEOUT setting can effectively avoid the timeout caused by lock waiting. After all, we don't want the "Error" keyword to appear in our system logs. Of course, DB2 has its own rollback mechanism so that business data will not be lost.

5. Give an example

Let's use 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".

The red part indicates that a lock upgrade occurred but failed. The reason for the failure depends on Reason Code ADM5503E. Here is lock timeout. The simple solution is to add LOCKTIMEOUT, but this is not good. If there is any memory left, it is better to add LOCKLIST. Of course, if there is no way to add LOCKLIST, you have to start with 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 have been made to LOCKLIST to double directly

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

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

Lock timeout (sec) (LOCKTIMEOUT) = 60

Now there is no alarm for lock upgrade.

For the original configuration, you can calculate the maximum number of rows of data that can be locked in this memory space:

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

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

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:

According to the specific situation of our customers, we can lock more than 1024000005,828571 rows at most. Compared with the locks required by the sql statement in db2diag.log, we found that no locks exceeding this value were upgraded successfully, and after we changed the parameters--

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

204800000amp 56mm 3657142

It is found that more than 3 million row locks have been successfully upgraded:

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, there are relatively many contents about locks, and it is also relatively complex to understand. It is suggested to increase the optimization experience of lock upgrade from the actual exercise, and configure more monitoring tools to analyze the data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.