Articles & Content


Locking - What Locking? DB2 9.7 Currently Committed and other Lock Avoidance Enhancements to Increase Performance and Throughput (C11)

Topic: 2010 EMEA

Subtopic: DB2 for LUW

DATE: 2010-11-10 (13:00 - 14:00)
SPEAKERS: Mike Winer (IBM)

Locking in DB2 for LUW has gone through many years and releases of changes, improvements, etc - all to reduce locking implications such as lock waits, lock timeouts, and deadlocks.This session will begin with a summary of the locking features delivered over the years, so those customers using versions prior to DB2 9.7 can understand how to control some impacts of locking. This will be a great lead-in to the significant changes and improvements provided in DB2 9.7.Currently Committed is the major locking enhancement in DB2 9.7. While not a new isolation level, it is a new behavior and semantic for Cursor Stability (with a minor impact to Read Stability!). Learn what Currently Committed is, how it works, and how it can have wholesale effects to all workloads without modifying ANYTHING except a single database configuration parameter!Beyond Currently Committed, DB2 9.7 introduces many lock avoidance techniques, internally, which help avoid lock conflicts and provide significant performance improvements. Hear what these lock avoidance techniques are, where and when they apply, and the additional benefits DB2 9.7 has to offer.While Currently Committed and other lock avoidance techniques solve many issues which may have been experience in prior releases, locking still occurs and unfortunately may result in lock waits, timeouts, and deadlocks. This session will describe the new Lock Event Monitors available in DB2 9.7 which can be used to help diagnose locking issues.

EXP. LEVEL: Beginner,Intermediate,Advanced


Receive a history lesson on traditional/original locking in DB2 for LUW and understand the various features delivered over the years to help reduce locking and related issues.

Currently Committed is the new Cursor Stability semantic provided by DB2. Hear what this is, how to enable it, and why it's completely safe for just about all database and application environments.

What would a database be like if it didn't perform any locking? Well, DB2 can't do this, but 9.7 introduces many lock avoidance techniques which not only help prevent lock waits and conflicts, but significantly improve performance compare to prior releases. Learn what these techniques are and how to recognize they are occurring.

Lock snapshots and db2pd were quite valuable in prior releases. They are still available and just as valuable as before. However, learn about the new Lock Events provided in DB2 9.7 which can be used instead.


This file is being provided by IDUG. We would encourage you to join IDUG to get full access to all of our files and resources. Joining IDUG is FREE and signing up is simple. Click here to join! or login!

Download File
Click to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.