Friday, September 04, 2009

Dynamic SQL Causing Lock Escalation?

Lock escalation is the promotion of a lock from a row, page or LOB lock to a table space lock because the number of page locks that are concurrently held on a given resource exceeds a preset limit.

But lock escalation can cause problems. Yes, when fewer locks are taken, CPU cost and memory usage can be reduced. On the other hand, escalating the size of a lock causes more resources to be locked... and that impacts concurrency with the most likely result being applications experiencing lock timeouts or deadlocks.

When lock escalation occurs, DB2 writes messages to the system log. So when complaints inevitably arrive about failing transactions you can look for the appropriate lock escalation message indicating that it could be the culprit.

But such simple steps are typically not sufficient to track down the root cause and take corrective actions. You need to be able to determine what SQL statement in what program and under what circumstances is causing the escalation. Doing so involves tracing and using performance monitoring tools.

For those interested in this topic, (indeed, the primary purpose of this blog post) you should seek out the IBM TechDoc titled Identifying the dynamic SQL statement which is causing a
lock escalation in DB2 for z/OS
. For those who don't know what a TechDoc is, don't worry, it isn't that complicated. It is basically a published piece of technical documentation that isn't part of a larger manual or IBM RedBook. They can be short papers, product flashes, presentations, etc.

This particular TechDoc details a methodology for identifying dynamic SQL statements involved in a lock escalation. It describes which traces to start, and which jobs to run to analyze the collected traces. It also explains how to analyze the trace report to find the problematic SQL statement.

You can search all of IBM's TechDocs by following this link.

Happy reading!


Anonymous said...

Hi Craig,
I was really surprised that you did not mention avoiding lock escalation. I am a firm believer in never having escalation. I, always, use the tablespace parm LOCKMAX 0. I, also, strongly recommend that all batch processes use our in-house commit routine(s). If a new/old process suddenly fails due to reaching the maximum number of locks, then it fails and will not run until that developer/group adds commit logic. Granted my bias comes from supporting very high volume OLTP applications, but I still can't see a good reason for allowing escalation.
Dave Nance

bitsofinfo said...

Have used DB2 for years fronted by an ORM that did quite a bit of dynamic SQL. The one time we ever had such an issue it was difficult to track down and it happened sporadically locking down an entire tablespace. Turns out it was caused by a rare situation where a piece of code in the middle of a transaction executed a read against a very large table that was missing a critical index! Once we added the index and did a reorg all was good.

Point being, when trying to track this stuff down, start with the simple stuff.

Craig S. Mullins said...

Hi Dave,

I agree with your assertion that lock escalation should, in general, be avoided...