Error : The error SQLCODE=-912, SQLSTATE=57011 indicates that the maximum number of lock requests has been reached for the database in DB2. This typically occurs when the system runs out of locks due to high concurrency or large transactions. .
Root Causes and Solutions :
Check the LOCKLIST and MAXLOCKS database configuration parameters:
db2 get db cfg for YOUR_DB_NAME
Focus on these parameters:
LOCKLIST: Total memory available for lock management.
MAXLOCKS: Percentage of the lock list that a single application can use.
Increase the values as needed:
db2 update db cfg for YOUR_DB_NAME using LOCKLIST 1024;
db2 update db cfg for YOUR_DB_NAME using MAXLOCKS 50;
Tip: Start with incremental increases, e.g., doubling the values
Long-running or uncommitted transactions can exhaust the lock resources.
Check Active Locks:
sql
Copy code
SELECT * FROM SYSIBMADM.MON_LOCKWAITS;
Solution:
Commit or rollback long-running transactions.
COMMIT; -- Or ROLLBACK;
Optimize transactions by breaking them into smaller batches.
DB2 escalates row-level locks to table-level locks when running out of memory, consuming more lock resources.
Check Lock Escalations:
SELECT TABNAME, TABSCHEMA, LOCK_ESCALS FROM TABLE(MON_GET_TABLE('','',-2)) AS T
WHERE LOCK_ESCALS > 0;
Solution:
Analyze queries with frequent escalations and optimize indexing or commit earlier.
Increase lock memory to avoid escalations.
To help avoid lock escalations, you might consider the following strategies
-Break Up Large Operations
If your application is trying to insert, update, or delete a large number of rows in one go, this can use up locklist memory quickly. Instead of processing everything in one operation, perform smaller batches and commit after each set of rows. You can track the number of locks an application uses by monitoring the `lock_count` from the MON_GET_LOCKS() table function.
-Adjust Isolation Levels
High isolation levels, like ‘Repeatable Read,’ require a lock for each row read. For large datasets, this can put a strain on locklist memory. Possible adjustments include running smaller queries, acquiring a table lock before executing your main operation, or lowering the isolation level if that aligns with your business needs. Again, the `lock_count` can help identify applications holding excessive locks.
-Tune Locklist Memory
If lock escalation happens often, the locklist memory allocated may be too small for your workload. Increasing this memory can help. A database administrator might start by increasing the allocated memory by 25%-100% while keeping an eye on the lock escalation occurrences.
Review MAXLOCKS Configuration
-If the MAXLOCKS setting is set too low, escalations will happen frequently. This parameter generally works best when configured between 50% and 70% to prevent runaway queries without being overly restrictive.
MAXLOCKS can be changed using>>
db2 connect to rdsadmin user <master-user>
db2 "call rdsadmin.update_db_param('<dbname>','maxlocks','97 automatic')" OR
db2 "call rdsadmin.update_db_param('<dbname>','maxlocks','97')"
Note: Please test this in lower environment first.
Also rasie AWS Suport request disabled the parameter db2_avoid_lock_escalation .
You can run, the following
db2 "select current timestamp as curr_timestamp, member, varchar(reg_var_name,64) as reg_var_name, varchar(reg_var_value,64) as reg_var_value, varchar(reg_var_on_disk_value,64) as reg_var_on_disk_value, level from table(env_get_reg_variables(null)) where reg_var_name = 'DB2_AVOID_LOCK_ESCALATION'"
if return values should be zero if values come as below mentioned measn DB2_AVOID_LOCK_ESCALATION not change need to reboot instance
CURR_TIMESTAMP |MEMBER|REG_VAR_NAME |REG_VAR_VALUE|REG_VAR_ON_DISK_VALUE|LEVEL|
--------------------------+------+-------------------------+-------------+---------------------+-----+
2025-03-07-09.51.53.609582| 0|DB2_AVOID_LOCK_ESCALATION|YES |YES |I |
== reference links ==
[1] IBM Documentation on Lock Escalation - http://www-01.ibm.com/support/docview.wss?uid=swg21600309
SELECT APPLICATION_HANDLE, SUM(LOCK_COUNT) AS TOTAL_LOCKS
FROM TABLE(MON_GET_LOCKS('', -2)) AS LOCKS GROUP BY APPLICATION_HANDLE ORDER BY TOTAL_LOCKS DESC
db2 get db cfg --- Retrieves the full database configuration for the current database.
db2 get db cfg | grep -i max
db2inst1@ ~]$ db2 get db cfg | grep -i max
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(2273792)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(93)
Max number of active applications (MAXAPPLS) = AUTOMATIC(107)
Max number of users in the cache (AUTHN_CACHE_USERS) = 0
Max DB files open per database (MAXFILOP) = 61440
Percent max primary log space by transaction (MAX_LOG) = 0
Percent log file reclaimed before soft chckpt (SOFTMAX) = 0
Maximum allowable CPU utilization (%) (WLM_CPU_LIMIT) = 0
MAXLOCKS size to be used by optimizer (OPT_MAXLOCKS) = 0
db2 get db cfg show detail | grep - i max ---Details
db2inst1@~]$ db2 get db cfg show detail | grep - i max
grep: i: No such file or directory
grep: max: No such file or directory
[db2inst1@ip-10-113-25-166 ~]$ db2 get db cfg show detail | grep -i max
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(2273792) AUTOMATIC(2273792)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(93) AUTOMATIC(93)
Max number of active applications (MAXAPPLS) = AUTOMATIC(107) AUTOMATIC(107)
Max number of users in the cache (AUTHN_CACHE_USERS) = 0 0
Max DB files open per database (MAXFILOP) = 61440 61440
Percent max primary log space by transaction (MAX_LOG) = 0 0
Percent log file reclaimed before soft chckpt (SOFTMAX) = 0 0
Maximum allowable CPU utilization (%) (WLM_CPU_LIMIT) = 0 0
db2 get db cfg | grep -i lock ----Lock
db2inst1@i]$ db2 get db cfg | grep -i lock
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(2273792)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(93)
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
LOCKLIST size to be used by optimizer (OPT_LOCKLIST) = 0
MAXLOCKS size to be used by optimizer (OPT_MAXLOCKS) = 0
db2 get db cfg | grep -i monitor
[db2inst1@~]$ db2 get db cfg | grep -i monitor
Monitor Collect Settings
db2 list indoubt transactions with prompting ----Lists all indoubt transactions in the database.
Memory allocation, resource contention, or improperly configured database parameters. Here are some common queries and tips to diagnose and address memory-related issues in DB2:
Check Current Memory Usage
Use the following query to get the memory usage details:
SELECT *
FROM SYSIBMADM.DBCFG
WHERE NAME IN ('DATABASE_MEMORY', 'DATABASE_HEAP', 'LOCKLIST', 'SORTHEAP');
Look at the buffer pool hit ratio and other statistics to determine if buffer pool size adjustments are necessary:
SELECT BP_NAME, DATA_LOGICAL_READS, DATA_PHYSICAL_READS,
(1 - FLOAT(DATA_PHYSICAL_READS) / DATA_LOGICAL_READS) * 100 AS HIT_RATIO
FROM SYSIBMADM.BP_HITRATIO
WHERE DATA_LOGICAL_READS > 0;
SELECT * FROM SYSIBMADM.APPLICATIONS
SELECT * FROM SYSIBMADM.SNAPAPPL;
Check the database memory settings and their current values:
SELECT NAME, VALUE, VALUE_FLAGS
FROM SYSIBMADM.DBCFG
WHERE NAME LIKE 'MAX%' OR NAME LIKE 'DATABASE_MEMORY';
---Query the catalog to list all available views
SELECT TABNAME
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'SYSIBMADM';
Memory issues might stem from lock contention.
--Retrieve lock information
SELECT AGENT_ID,TABNAME,TABSCHEMA,LOCK_OBJECT_TYPE,LOCK_MODE,LOCK_STATUS
FROM SYSIBMADM.SNAPLOCK WHERE TABSCHEMA ='test';