January 18, 2008
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
« Y2K38 and DB2, Is Your Application Safe? | Main | DB2 9 Certification Study Questions: DB2 diagnostics log file and data row compression »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.