LOCKLIST and MAXLOCKS database configuration parameters and lock escalations

Answer

The definition of the LOCKLIST parameter can be found in the Information Center:

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.config.doc%2Fdoc%2Fr0000267.html

and the MAXLOCKS parameter :

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.config.doc%2Fdoc%2Fr0000268.html

The LOCKLIST db cfg parameter controls the amount of memory used to store lock information.
This is expressed in units of 4KB pages.

Locks (on a row, table, etc) are stored and manipulated by the database using an internal structure known as an “LRB” or “Lock Request Block”.

The LOCKLIST Information Center document describes the LRB size accurated. Briefly, in v9.7, v9.8, and v10.1, this LRB structure is 128 bytes in size on all platforms. In v8.2, v9.1, and v9.5 this LRB structure is 48 bytes on 32 bit platforms, and 64 bytes in size on 64 bit platforms. On HP-UX 64 bit, this LRB is 80 bytes.
The remainder of this document will assume a 128 byte LRB size for simplicity.


Thus, the total number of LRBs that the database can use is determined by:

Total LRBs = (LOCKLIST * 4096) / sizeof(LRB)

So, using a locklist of e.g. 10,000 pages ,

Total LRBs = (10000 pages * 4096 bytes/page) / 128 bytes
= 1024000 LRBs

When a lock is requested by an application (row, table, etc), DB2 will search the chain of in-use LRBs to determine if the same lock is already held by another transaction.

If the lock is not already held by another transaction (this is the only transaction to hold this lock), the Lock Manager will consume 2 LRBs acquiring the lock. The first LRB is used as a special header LRB and is not associated with any particular transaction. The second LRB is used to associate the transaction with the resource being locked.




Fig 1: A conceptual view of a transaction T1 holding 2 unique locks.

If the lock is already held by another transaction (this is the second, third, or Nth transaction to hold this lock), the Lock Manager will consume 1 LRB acquiring the lock. The LRB is used to associate the transaction with the resource being locked.



Fig 2: A conceptual view of a transaction T1 holding 2 unique locks, followed by transaction T2 holding 1 non-unique lock and 1 unique lock.

Since these special header LRBs are not associated with any particular transaction, they will not be displayed in lock snapshot or db2pd –locks output. However, a database snapshot displays the amount of locklist memory used (in bytes) and this value accounts for all LRBs including header LRBs.

Locklist-Full Escalation

This type of escalation will occur when the number of LRBs in the unused pot drops below a specific threshold. This threshold is determined internally by some factors including the size of the locklist. Also, for performance reasons, each agent servicing a transaction can cache a small number of LRBs (up to 100) for future use, which will affect the number of LRBs in the unused pot. For simplicity, we will approximate that locklist-full escalation will occur when less than 2% of the LRBs remain in the unused pot.

Maxlocks Escalation

This type of escalation will occur when the number of locks (not LRBs) held by a particular application database connection exceeds the limit defined by MAXLOCKS.

This limit is calculated as:

Maximum Locks per Appl = Total LRBs * (MAXLOCKS/100)
= (LOCKLIST*4096)/sizeof(LRB) * (MAXLOCKS/100)

So, using a LOCKLIST of 10000 pages and a MAXLOCKS value of 20%

Maximum Locks per Appl = ((10000*4096)/128) * (20/100)
= 640000 * (0.20)
= 64000

The maximum number of locks (not LRBs) that an application can hold before escalation occurs is 64000.

Why this design has led to confusion for DBAs

Every lock being held by a transaction will consume 1 LRB, and for every unique lock another LRB is consumed as a header LRB for internal purposes. Furthermore, maxlocks-escalation is determined by the number of locks (not LRBs) held by a transaction, while locklist-escalation is determined by the number of unused LRBs (not locks) remaining in the system. This has led to confusion.

Consider the following scenario:

3 applications running concurrently, a LOCKLIST value of 20000 pages, and a MAXLOCKS setting of 20%. Each application is locking up to 500,000 unique rows.

i) the total number of LRBs available in the locklist is:

Total LRBs = (LOCKLIST*4096) / sizeof(LRB)
= 640000

ii) the maximum number of locks an application/transaction can hold before maxlocks-escalation is

Maximum Locks per Appl = Total LRBs * (MAXLOCKS/100)
= 128000

A DBA unfamiliar with the details of this document might expect to see all 3 transactions acquiring 128000 locks before escalating (since 3 * 128000 is 384000 and is well below 640000 ).

Rather, what we may see here is:

Application 1) acquires about 128000 locks (which consumes about 256000 LRBs since locks are for unique rows) before triggering maxlocks-escalation.

Application 2) acquires about 128000 locks (which consumes about 256000 LRBs since locks are for unique rows) before triggering maxlocks-escalation.

If escalation from first 2 transactions hasn’t occurred yet, Application 3) would acquire about 64000 locks (which consumes about 128000 LRBs since locks are for unique rows) before triggering locklist-escalation.

An important note on automatic tuning of the LOCKLIST and MAXLOCKS parameter:

When LOCKLIST is set to AUTOMATIC, the MAXLOCKS setting cannot be set.

The design for automatic lock tuning will aim to avoid lock escalations. It will keep the MAXLOCKS setting high (starting off with 98%) and will automatically adjust this downwards as more memory is allocated for LOCKLIST.

When LOCKLIST can no longer grow (because the limit of available database memory has been reached), then lock escalations can still occur. Due to the high value for MAXLOCKS maintained by the automatic turning algorithm, a lock escalation that occurs is likely to be a locklist-full escalation rather than a maxlocks escalation.

A locklist-full escalation can happen on a transaction that is not holding many locks at that point in time which may be unpredictable. Hence in some cases, and this is a decision for a DBA to make, it is better to tune MAXLOCKS manually in order to favour early lock escalation for big consumers of locks.