DB2 LUW Performance: Fighting Over Data - LOCKS

Every once in a while I hear a DBA say they are having Lock problems. Since read-only or read-mostly Data Warehouse databases rarely have lock problems, I quickly assume they have an OLTP database. It is my opinion that locks are rarely, if ever, a PROBLEM. Locks are a SYMPTOM of another very real problem.
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
If you will be running any concurrent ETL, import, or load processes while your data warehouse queries are running, be sure to switch the table LOCKSIZE back to ROW.
  • alter table schema.tablename LOCKSIZE ROW
The ALTER LOCKSIZE statement takes effect virtually immediately.

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
DBI Logo