Question & Answer
Question
Cause
Answer
Db2 LOCKLIST Parameter
The Db2 LOCKLIST parameter indicates the amount of storage that is allocated within the database Heap to store lock information. There is one LOCKLIST per database and it contains the locks held by all applications, or in this case IBM Spectrum Protect (Tivoli Storage Manager) Server transactions, concurrently connected to the database. Locks are stored and manipulated by Db2 in the form of a request block that uses 40 bytes of memory on 32-bit platforms or 64 bytes on 64-bit systems. By default, Db2 manages the LOCKLIST size automatically and increases or decreases the total LOCKLIST allocation based on current activity. To determine the current value, issue the following command:
db2 "get db cfg for <db name>"
The LOCKLIST is composed of memory allocated as 4K pages. A LOCKLIST parameter value of 10000 (4KB pages) is equivalent to 40,960,000 bytes (about 39MB) of memory allotted to the LOCKLIST from the Database Heap. A single lock request can require up to 128 bytes out of the LOCKLIST heap as one request block is used for the lock descriptor and the second block is used to associate the IBM Spectrum Protect (Tivoli Storage Manager) server transaction with the resource being locked. If the lock resource has already been requested, then only one request block is required for future transactions requesting that lock resource.
Impact to the IBM Spectrum Protect (Tivoli Storage Manager) Server
The proper management of the Db2 LOCKLIST is crucial to prevent deadlocks within the IBM Spectrum Protect (Tivoli Storage Manager) server. When the server is under average workload and the amount of concurrent activity is not driving large spikes of lock requests, the Db2 automatic handling of the LOCKLIST works well for the IBM Spectrum Protect (Tivoli Storage Manager) server. However, when the amount of concurrent data movement activity is high, the inherent spikes to the Db2 LOCKLIST can cause lock escalations, which can result in deadlocks within the server.
The best case scenario is that the IBM Spectrum Protect (Tivoli Storage Manager) server redrives the request and the subsequent operation does not fail due to the deadlock. Most IBM Spectrum Protect (Tivoli Storage Manager) server operations effectively recover from this deadlock; in the case where significant data was moved, deleted, or updated, the time required to process the data a second, or third, time can result in severe performance degradation on the IBM Spectrum Protect (Tivoli Storage Manager) server. The worst case scenario is that the operation cannot be retried and the entire operation fails.
If the amount of concurrent data being moved exceeds 500GB at any given time, it is suggested that the Db2 LOCKLIST parameter be adjusted as explained below. If the IBM Spectrum Protect (Tivoli Storage Manager) server is involved with deduplicating large files either from the client, or server, properly tuning the LOCKLIST manually is extremely critical as large file deduplication inherently drives the number of rows needing to be managed by Db2 very high. This is because a large file is managed in many small pieces which result in many row locks within the Db2 LOCKLIST.
The following IBM Spectrum Protect (Tivoli Storage Manager) server messages are issued when a transaction is being rolled back due to a deadlock within Db2.
ANR0159E dbieval.c(863): Database deadlock detected on XX:X.
ANR0162W Supplemental database diagnostic information:
-X:XXXXX:-XXX ([IBM][CLI Driver][DB2/XXXXX] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001).
Tuning the Db2 LOCKLIST
To choose a LOCKLIST parameter value that will minimize the number of deadlocks that may occur within a IBM Spectrum Protect (Tivoli Storage Manager) server environment, the maximum amount of data that can be moved, or managed, within the IBM Spectrum Protect (Tivoli Storage Manager) server at any given time must be known or at least approximated with a fair amount of precision.
Tuning Recommendation: Increase the Db2 LOCKLIST value by twice the amount of anticipated workload so that there are no unexpected failures during daily and nightly operations.
It is important to note that increasing the Db2 LOCKLIST parameter value has a direct effect on the amount of memory that is required within the instance memory region. Increasing the parameter should be done with the understanding of what the potential memory implications will be on the system. The following information and examples can be used to tune the Db2 LOCKLIST parameter to an appropriate value based on the IBM Spectrum Protect (Tivoli Storage Manager) server environment and workload:
Key Formula for Db2 LOCKLIST Adjustments
Concurrent data movement
500GB = 2,500,000 locks = 122000 LOCKLIST
1TB = 5,000,000 locks = 244000 LOCKLIST
5TB = 25,000,000 locks = 1220000 LOCKLIST
Memory Requirements for LOCKLIST
122000 LOCKLIST = 499MB (122000 X 4096)
244000 LOCKLIST = 1GB (244000 X 4096)
1220000 LOCKLIST = 4.9 GB (1220000 X 4096)
Example #1
100 IBM Spectrum Protect (Tivoli Storage Manager) clients store 1TB of data in a 4-hour nightly window
Migration has the ability to move 1TB of data in the same 4-hour window
Total concurrent data movement - 2TB
Db2 LOCKLIST recommendation - 588000
Additional impact to system memory (potential) - 2.4 GB
Example #2
Migration has the ability to move up to 1TB of data in an 8-hour window
Reclamation can run in the same window and process up to 2TB of data
Expiration runs in the same window and can process up to 10 Million files which represent 500GB of data
Total concurrent data movement/management - 3.5 TB
Db2 LOCKLIST recommendation - 954000
Additional impact to system memory (potential) - 3.9GB
Updating the Db2 LOCKLIST Parameter
The following command can be used to update the LOCKLIST parameter:
db2 "connect to <db name>"
db2 "update db cfg for <db name> using LOCKLIST XXXXXXXX immediate"
where XXXXXXXXX = LOCKLIST value
The new value can be verified by issuing the following command:
db2 "get db cfg for <db name> show detail"
NOTE: As a result of updating the Db2 LOCKLIST parameter the MAXLOCKS parameter will be set to manual as well. The MAXLOCKS parameter controls what percentage of the LOCKLIST that a single transaction can own at any given time. Ensure this value is set in the range of 95-100. If it is not in this range, the parameter can be updated with the following command:
db2 "connect to <db name>"db2 "update db cfg for <db name> using maxlocks 97 immediate"
Was this topic helpful?
Document Information
Modified date:
13 August 2021
UID
swg21430874