Posted by Deadlocking is an application problem. There are only a few things that DBAs can do to reduce deadlocking, and they all require buy-in from the application. Let me repeat that another way. Don’t set the parameters mentioned here without understanding the impact on your application.
Currently Committed
This is new behavior in DB2 9.7. It has a similar effect to Oracle’s locking methodology in that “Readers don’t block writers and writers don’t block readers”. If you’ve created your database on 9.7, it is on by default. If you upgrade to 9.7, you can turn it on like this:
db2 update db cfg for <dbname> using CUR_COMMIT ON
It is a database configuration parameter, so you’d have to look at it for each database if you have more than one on an instance.
One thing I like about this is that it can be set separately from DB2_COMPATIBILITY_VECTOR. For applications like WebSphere Commerce that don’t support DB2_COMPATIBILITY_VECTOR, this is nice.
Because this reduces locking overall, it also reduces deadlocking. It also changes how locks are acquired, so your application should explicitly support this setting. WebSphere Commerce 7 supports it. WebSphere Commerce 6 does not support it.
Three DB2 Registry Parameters
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
Increasing LOCKLIST only helps if you’re seeing lock escalations
I put that whole sentence in a heading because I very frequently run into someone who wants me to change LOCKLIST to deal with a deadlocking issue. Increasing LOCKLIST will only help if you’re actually seeing lock escalations. Lock escalations are noted both in the db2diag.log and in counters in the database snapshot.
What won’t help
Changing the LOCKTIMEOUT database configuration parameter will only help if you have it set unreasonably high (higher than 90 seconds for an e-commerce database), and then only in deadlocks that are side-effects of long lock waits.
Changing DLCHKTIME will not help reduce deadlocking.
Deadlocking is an application or database design problem
Deadlocking is an application problem that manifests in the database. Even if it isn’t a database problem, DBAs frequently help developers troubleshoot issues. See my blog entries on analyzing deadlocks to get an idea of how to do this.
Analyzing Deadocks – the old way
Analyzing Deadlocks – the new way
