The real problem is poor performing statements (SQL or XQueries). While a statement is taking too long to run, it may be holding locks on data (with isolation levels RS, RR, CS). If other statements need conflicting access to data locked by the first statement, then lock contention can occur. Lock contention is revealed by Lock Waits, Lock Wait Time (ms), Timeouts, and sometimes even Deadlocks.
Database Configuration Parameter LOCKTIMEOUT
Check your DB CFG ( db2 "get db cfg for DBNAME show detail" ) and find your value for LOCKTIMEOUT. If this is set to -1, the default value, you are flirting with disaster. -1 means that lock contention will never time out. Like a house of cards, your database can quickly come to a grinding halt as statements wait upon other statements - which are waiting upon other statements, and so on. Generally, your LOCKTIMEOUT value should be set to 30 seconds or less, with 10-15 seconds being ideal. If a statement can't get the lock resources it needs, and since it is likely holding locks while waiting to obtain unavailable locks, then it is desirable to have waiting statements timeout quickly so that lock contention does not exacerbate rapidly.
Database Configuration Parameter DLCHKTIME
This parameter specifies how often the DB2 deadlock detection mechanism examines current locks for the possibility of a deadly embrace. The default value is 10,000 ms, or 10 seconds. If LOCKTIMEOUT is set to 30 seconds, the DLCHKTIME 10000 is a good value. If you set LOCKTIMEOUT to 15 seconds, use DLCHKTIME of 5000. If you have a Data Warehouse database, LOCKTIMEOUT 120 and DLCHKTIME 60000 would be good choices. The higher DLCHKTIME will save a little CPU time by decreasing the frequency of deadlock checking.
Data Warehouse Databases and LOCKSIZE
If your data warehouse database is read-only, there is little point in having statements incur the CPU expense of acquiring row level locks as they traverse the data. You can save some CPU cycles by altering the database tables to LOCKSIZE table:
- alter table schema.tablename LOCKSIZE TABLE
- alter table schema.tablename LOCKSIZE ROW
Avoiding LOCK problems
The secret to avoiding lock contention problems is to have an optimum physical design in place which supports the statement activity of the database with great speed and efficiency. When statements perform fast, the likelihood of lock contention is reduced. To have statements perform at maximum speed and efficiency, you need the right physical design in place.
Optimum Physical Design
Briefly, a high quality, optimized physical design includes the prudent use of high quality indexes, MDC tables, possibly MQTs, and appropriate clustering indexes. Indexes should not:
- Have redundant definitions
- Have skewed distributions
- Have low cardinalities
The Shameless Marketing Moment
I'd like your help and advice. I'm going to practice my IDUG presentation "Extreme Makeover: Optimal Physical Design Rehab - DB2 LUW Edition (E09)" via upcoming DBI Webinars and your participation and feedback would be appreciated (one lucky participant will be randomly selected to win an Amazon.com gift certificate). This presentation covers many physical design techniques in greater depth than can be covered in a blog post.
These Webinars are being held on:
- Tuesday, January 22, 2008, 1:30pm-3:00pm CST: Register
- Tuesday, January 29, 2008, 10am-11:30am CST: Register
Just for Fun
Since many of you might be traveling to Dallas Texas this May for IDUG North America, or if you travel on business or for pleasure, you really need to watch this video story on hotels: http://www.breitbart.tv/?p=7714
... you'll never drink from a hotel glass or coffee mug again!
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info
