Locking Parameters

So I thought I’d write a post covering locking parameters. This is by no means a comprehensive coverage of isolation levels and locking, but more of a practically oriented guide to the parameters available in DB2 that relate to locking.

LOCKTIMEOUT

This database configuration parameter specifies the time in seconds that a connection will wait for a needed lock before returning an error to the user.

Locktimeout is actually powerful functionality for OLTP/e-commerce databases. The idea is that an application should either do its work or fail and get out of the way. DB2 has a bit of a bad reputation for concurrency. I tend to think that this is because DB2 favors data integrity over concurrency, but I’m sure an Oracle dba would disagree with that characterization. For WebSphere Commerce or any OLTP/e-commerce databases, LOCKTIMEOUT should be set to a value between 30 and 90 seconds. Other types of databases may have other appropriate values.

Be exceedingly careful with the default of -1, though. -1 means “wait forever”, and this has a couple of implications. The first is that this “wait forever” may appear to the end user to be a hang – a query that is simply not returning results. The other one is that you can end up with some interesting lock chaining scenarios. The main problem is not always that one connection is waiting on one other connection – the problem tends to be that the waiting connection also has a dozen or a hundred other locks, and other connections may pile up behind those locks. db2top even has an option from the locks screen to list out lock chains. I’ve seen some ESB datbases (where the ESB application holds a lock on the SIBOWNER table continuously) where runstats and/or automatic runstats evaluation have piled up behind the application locks over the course of weeks to the point where the database finally becomes unusable, and the various runstats have to be manually cancelled. This does not occur if  LOCKTIMEOUT is set to a value.

To check your current value:

$ db2 get db cfg for <db_name> |grep LOCKTIMEOUT
 Lock timeout (sec)                        (LOCKTIMEOUT) = 60

The database must be recycled for changes to LOCKTIMEOUT to take effect.

LOCKTIMEOUT info center entry

LOCKLIST

This database configuration parameter is the size in 4k pages of the area of memory that DB2 uses to store locking information.

Contrary to some beliefs, changes to this parameter will not help locktimeout or deadlock issues unless there are also lock escalations are also occurring. I have to explain this at least a couple of times a year to one client or another. Generally the only time you will tune this is if you do see lock escalation. Lock escalations are noted in the DB2 diagnostic log and also in database snapshots. This parameter can be designated as one of the ones that is automatically tuned by STMM. If you are not allowing STMM to automatically tune it, I do recommend going higher than the default to start – I usually start with 4800 when manually tuning.

Each lock takes either 128 or 256 bytes, depending on whether other locks are held on the same object.

To check your current value:

$ db2 get db cfg for <db_name> |grep LOCKLIST
 Max storage for lock list (4KB)              (LOCKLIST) = 4800

One nice thing is that any changes to this parameter will take place immediately – no need to recycle the database or the instance for it to take effect.

LOCKLIST info center entry

MAXLOCKS

This database configuration parameter specifies the maximum percentage of the LOCKLIST that a single connection can use. This is designed to help prevent all of the LOCKLIST being consumed by a singe connection. It is something that is more likely to be tuned on previous versions of DB2 where LOCKLIST could not be set to automatically increase to avoid locking issues, and may still need tuning, particularly on ODS or DW databases where memory is constrained. Like LOCKLIST, this parameter can be set to automatic.

To check your current value:

$ db2 get db cfg for <db_name> |grep MAXLOCKS
 Percent. of lock lists per application       (MAXLOCKS) = 10

Like LOCKLIST, this parameter can be changed online with changes taking place immediately with no recycle needed.

MAXLOCKS info center entry

DLCHKTIME

This database configuration parameter specifies the frequency (in milliseconds) that db2 checks for deadlocks. The default is 10,000 ms (1o seconds). I don’t think I’ve ever seen this one changed.

To check your current value:

$ db2 get db cfg for wc005d04 |grep DLCHKTIME
 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000

If you do end up having to change it, you can change it immediately without a database or instance recycle.

DLCHKTIME info center entry

DB2_EVALUNCOMMITTED

This DB2 registry parameter is one of three that changes how db2 locks rows. As such it is dangerous and should only be used if your application explicitly supports its use. WebSphere Commerce supports the use of all three. Be careful of DB2 instances where you have more than one database – this is set at the instance level, and you’ll want to make sure that all applications accessing any database in the instance support these parameters.

This DB2 registry parameter allows db2 to evaluate rows to see if they meet the conditions of the query BEFORE locking the row when using RS or CS isolation levels. The normal behavior for these isolation levels would be to lock the row before determining if it matched.

To check your current value:

$ db2set -all |grep DB2_EVALUNCOMMITTED
[i] DB2_EVALUNCOMMITTED=YES

If nothing is returned from this command, then the parameter is not set. The entire db2 instance must be recycled (db2stop/db2start) for changes to this parameter to take effect.

DB2_EVALUNCOMMITTED info center entry

DB2_SKIPDELETED

This DB2 registry parameter is one of three that changes how db2 locks rows. As such it is dangerous and should only be used if your application explicitly supports its use. WebSphere Commerce supports the use of all three. Be careful of DB2 instances where you have more than one database – this is set at the instance level, and you’ll want to make sure that all applications accessing any database in the instance support these parameters.

This DB2 registry parameter allows DB2 to skip uncommitted deleted rows during index scans. If it is not set, db2 will still evaluate uncommitted deleted rows during index scans. The normal behavior is for DB2 to evaluate uncommitted deleted rows in indexes until they are actually committed.

To check your current value:

$ db2set -all |grep DB2_SKIPDELETED
[i] DB2_SKIPDELETED=ON

If nothing is returned from this command, then the parameter is not set. The entire db2 instance must be recycled (db2stop/db2start) for changes to this parameter to take effect.

DB2_SKIPDELETED info center entry

DB2_SKIPINSERTED

This DB2 registry parameter is one of three that changes how db2 locks rows. As such it is dangerous and should only be used if your application explicitly supports its use. WebSphere Commerce supports the use of all three. Be careful of DB2 instances where you have more than one database – this is set at the instance level, and you’ll want to make sure that all applications accessing any database in the instance support these parameters.

This DB2 registry parameter allows DB2 to skip uncommitted newly inserted rows. If this parameter is not set, DB2 waits for the inserts to be committed or rolled back before continuing – you can see how this might not be ideal for a database that requires high concurrency. Like the others, this applies to CS and RS isolation levels.

To check your current value:

$ db2set -all |grep DB2_SKIPINSERTED
[i] DB2_SKIPINSERTED=ON

DB2_SKIPINSERTED info center entry

Deadlock Event Monitor

While not a parameter per se, having your deadlock event monitor properly set up is important to deadlock analysis. DB2 starting with version 8 comes with a detailed deadlock event monitor enabled by default. This is actually awesome because it means that in many cases, we have the data we need to analyze deadlocks after they happen. But one of the problems is that this event monitor is set up with very little space. Because of this, I re-create it whenever I’m setting up a new database. My favorite syntax for that is:

db2 "create event monitor <dl_evmon_name> for deadlocks with details write to file 'ros_detaildeadlock' maxfiles 2000 maxfilesize 10000 blocked append autostart"

You have to have the disk space to support that, and you still may have to clear out old output files by dropping and recreating the event monitor from time to time.

I frequently get questions about having the deadlock event monitor write to tables. My inclination is not to write to tables – mostly because I’ve seen deadlocking issues with hundreds or thousands of deadlocks per hour that just creamed the database – and the additional load of writing to tables during such an issue might make things even worse.

So I hope something there helps someone who is looking at locking parameters.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home