2nd Quarter 2011

Reading Things That Aren't There... and Missing Things That Are!

There are options that you can specify that may cause you to read data that is not really in the database. Alternately, you can set things up so that you miss reading data that is actually in the database.

How can this happen? Dirty reads will take care of the first one. Specifying ISOLATION(UR) implements read-through locks, also referred to as dirty read or uncommitted read. This option applies to read operations only. Using this isolation level, data may be read that never actually exists in the database. This can happen because the transaction can read data that has been changed by another process but is not yet committed.

A dirty read can cause duplicate rows to be returned where none exist or no rows may be returned when one or more actually exists. Uncommitted read provides the highest level availability and concurrency of the isolation levels, but the worst level of data integrity. It should be used only when data integrity problems can be tolerated. Potential candidates for applications to consider for dirty reads include those using analytical queries, estimates, and averages. But you shouldn’t choose uncommitted reads if you are calculating account balances! When choosing UR isolation take the time to analyze and ensure that these types of “problems” are acceptable for your application.

What about not reading data that is in the database? DB2 provides an option whereby a transaction can skip over rows that are locked. This is done using the SKIP LOCKED DATA option within SELECT, SELECT INTO, PREPARE, searched UPDATE and DELETE statements, and also the UNLOAD utility.

When you tell DB2 to skip locked data, any data that is locked is not accessed and your program will not have it available – even if it otherwise qualifies based on the SQL statement. DB2 just skips over any locked data instead of waiting for it to be unlocked. The benefit is improved performance because you will not incur any lock wait time. But it comes at the cost of missing data. You should only consider this option when your program can tolerate skipping over some data. Again, though, not very useful when calculating account balances!

The SKIP LOCKED DATA option is compatible with Cursor Stability and Read Stability isolations; it cannot be used with Uncommitted Read or Repeatable Read isolations. DB2 simply ignores the SKIP LOCKED DATA clause under UR and RR. Additionally, SKIP LOCKED DATA works only with row locks and page locks. Keep in mind that with row locking you will be skipping over locked rows, but with page locking you will be skipping over all the rows on the locked page.

Consider using these features with extreme caution and make sure you know exactly what you are telling DB2 to do. Otherwise, you might be reading more... or less than you really want!

Contribution by Craig Mullins

Craig S. Mullins, president and principal consultant of Mullins Consulting, Inc., is a data management strategist, researcher, and consultant. He has nearly three decades of experience in all facets of database systems development and has worked with mainframe DB2 since V1. You may know Craig from his popular books: "DB2 Developer's Guide" (with over 1500 pages of in-depth technical information on DB2 for z/OS) and "Database Administration: The Complete Guide to Practices and Procedures" (the industry's only comprehensive guide to heterogeneous database administration). Craig can be reached via his website at www.craigsmullins.com.

< < < Back to White Papers


  • PRESS
  • BECOMING A PARTNER
  • SUCCESS STORIES
  • UNIQUE TECHNOLOGIES
  • WHY CDB?

CDB Software:The Industry's Leading Authority on z/Series DB2 Data Management

CDB Software is a leading authority on z/Series DB2 environments and how they can be optimized to meet the changing demands of business. From the critical every-day processes of database performance and protection to the implementation of new requirements for security and compliance, CDB Software is the visionary leader for enterprise DB2 environments that want both flexibility and control.

For more information or to schedule a briefing with a CDB expert, contact CDB Press and Analyst Relations .

Press Releases      |      White Papers &Articles     |     Press Contacts     |     About CDB