Lock table but remaining lock escalation

[Frederick Tant]

DB2 V9.7

The DB is used by 1 application with no concurrent applications.

I got the problem that large update/insert cause lock escalation, to solve the issue I set a lock on the table so there's no row locking any more.
BUT

after setting a lock on a table (LOCK TABLE <table> IN EXCLUSIVE MODE;) I remain having the message " DB2 is performing lock escalation". This together with very bad performance.

Any sugestion why LOCK table is not working?

ADM5501I DB2 is performing lock escalation. The affected application is named
"db2bp.exe", and is associated with the workload name "SYSDEFAULTUSERWORKLOAD"
and application ID "*LOCAL.DB2.101002234850" at member "0". The total number
of locks currently held is "153966", and the target number of locks to hold is
"76983".

[vijay2082]

Hi,

Where did you read that locking a table in exclusive mode prevents lock escalation ? Go through the below link to know what lock exclusive does.

http://publib.boulder.ibm.com/infoce.../r0000972.html

Now, regarding the issue kindly let em know few details :

1) What is the size of table in question ? (send me select * from syscat.tables where tabname='<YOURTABLE_NAME>')
2) What is the application doing on the table and details about the application? Is it SAP ?

3) Send db2support is possible
db2support . -d <db_name> -c -g -s -f

4) Look at the workoad detail for any restriction

Cheers, Vijay

[Frederick Tant]

Hi Vijay, thanks for your reply
We use DB2 for data analysis using SQL query and stored procedures. No external programcalls (sap etc).
We have no need of logging (since we can repopulate the tables in case of a db problem, script error etc).
De scripts are the only user, so no shared access to the DB needed.

The lock escalation is visible when executing for ex. insert into system.codestohandle (ean,code) SELECT ean,code FROM system.tmp_comp ;

This fills 5mil records.

In my point of view the lock escalation appears due to the massive row locking on every row as like in the ex an import statement. In my thoughts the record locks escalation problem will not appear when the table is locked by LOCK TABLE. (Since there is no record lock on a loced table) Is this a correct statement?

Some fields from syscat

status n
type t
card 5289325
npages 20908
overflow 0
locksize r
rowformat n
compression n
access_mode f

the db2support command runs into an error. maybe the output of db CFG is usefull


Database Configuration for Database

Database configuration release level = 0x0d00
Database release level = 0x0d00

Database territory = BE
Database code page = 1208
Database code set = UTF-8
Database country/region code = 32
Database collating sequence = IDENTITY
Alternate collating sequence (ALT_COLLATE) =
Number compatibility = OFF
Varchar2 compatibility = OFF
Date compatibility = OFF
Database page size = 4096

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Statement concentrator (STMT_CONC) = OFF

Discovery support for this database (DISCOVER_DB) = ENABLE

Restrict access = NO
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

Backup pending = NO

All committed transactions have been written to disk = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = NO
User exit for logging status = NO

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(489720)
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = 10000
Percent. of lock lists per application (MAXLOCKS) = 100
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(2449)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 4096
Sort list heap (4KB) (SORTHEAP) = 4096

Database heap (4KB) (DBHEAP) = 4096
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 477
Log buffer size (4KB) (LOGBUFSZ) = 1500
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 87279
Buffer pool size (pages) (BUFFPAGE) = 250
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(4096)
Default application heap (4KB) (APPLHEAPSZ) = 5120
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = -1

Changed pages threshold (CHNGPGS_THRESH) = 80
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(1)
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(3)
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

Track modified pages (TRACKMOD) = OFF

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC(40)
Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
Max DB files open per application (MAXFILOP) = 65535

Log file size (4KB) (LOGFILSIZ) = 80000
Number of primary log files (LOGPRIMARY) = 26
Number of secondary log files (LOGSECOND) = 7
Changed path to log files (NEWLOGPATH) =
Path to log files = D:\DB2\NODE0000\SQL00001\SQLOGDIR\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Percent max primary log space by transaction (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 1040
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0

First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

Automatic maintenance (AUTO_MAINT) = ON
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Automatic statement statistics (AUTO_STMT_STATS) = ON
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

Auto-Revalidation (AUTO_REVAL) = DEFERRED
Currently Committed (CUR_COMMIT) = ON
CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW
Enable XML Character operations (ENABLE_XMLCHAR) = YES
WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0
Monitor Collect Settings
Request metrics (MON_REQ_METRICS) = BASE
Activity metrics (MON_ACT_METRICS) = BASE
Object metrics (MON_OBJ_METRICS) = BASE
Unit of work events (MON_UOW_DATA) = NONE
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
Number of package list entries (MON_PKGLIST_SZ) = 32
Lock event notification level (MON_LCK_MSG_LVL) = 1

SMTP Server (SMTP_SERVER) =
SQL conditional compilation flags (SQL_CCFLAGS) =
Section actuals setting (SECTION_ACTUALS) = NONE

[vijay2082]

Hi Frederick,

Thanks for providing that extra information. Yeah locking the table in exclusive mode certainly(should...;) prevent row level lock escalation. Few more questions before I suggest something.

1) What is the fpages output in syscat.tables for the table in question ?

2) While inserting the records using below dml do you use commits in between ?

insert into system.codestohandle (ean,code) SELECT ean,code FROM system.tmp_comp

If ans to question 2 is YES then I will suggest you to remove commits from in between the chunk of the dml insert statement as it will set table level lock to row level whenever you commit.. so your semantic should be like ....

< lock table in exlusive mode>
insert into system.codestohandle (ean,code) SELECT ean,code FROM system.tmp_comp

commit;
< lock table in exlusive mode>
insert into system.codestohandle (ean,code) SELECT ean,code FROM system.tmp_comp
commit;


OR --- don't commit until whole rows are inserted into the table

3) Since you are on v9.7 I will suggest switching locklist and maxlocks to automatic and enabling self tuning memory manager to ON

Setting these parameters to automatic will allocate and de-allaocate memory as needed without having any adverse affect on the system. ( Although turning off commits in step 2 should solve your issue for now)

Cheers, Vijay