Concurrency is a critical performance aspect of any database application. While there are many options available to improve it, one of the more recent features that has quietly made its way into DB2 for LUW and z/OS is concurrent access resolution. This feature can dramatically improve the concurrency and performance of some applications, but it does come with a price. A careful understanding of how concurrent access resolution affects query result sets, how it may impact database size and performance, and when it works and doesn’t work are critical when using this feature. This is especially true since you may not realize you already could be using it! In addition, it’s important to understand the other options that can be used to improve the concurrency of your applications and database, and how concurrent access resolution fits into the picture. However, before we dive into the details of concurrent access resolution, let’s first get an understanding of some of the choices available for application concurrency in DB2.
The Basics of Concurrency
Since tables in a DB2 database are shared objects that can be accessed concurrently, DB2 provides for both the concurrency and the consistency of the data. That is, DB2 attempts to allow the highest level of concurrent access to data while ensuring that only consistent data is returned to an application. DB2 manages the concurrency by maintaining locks on objects, or the parts of objects, being accessed by an application.
There are three basic types of locks: Share locks (S-Locks), Update locks (U-locks) and Exclusive locks (X-locks). By keeping these locks, DB2 is able to present a stable view of the database to one process while another process is updating the database. For example, if an application updates a row in a table, an X-lock will be taken and held until the application issues a commit or rollback. Typically, but depending on the isolation level set, another application that’s a reader of the updated object (or portion of the object) will wait until an X-lock is released (by commit or rollback) before reading the data. Locks can be taken at a row, page, partition, table or table space, depending on the various system and database settings.
The isolation level an application uses will also greatly impact the level of concurrency across that application, as well as other applications with which it shares data. DB2 uses one of four isolation levels for a connection. The isolation level can be specified at the application level via a bind parameter or connection attribute, and at the statement parameter via an isolation clause or a prepare attribute. DB2 supports these isolation levels:
• Repeatable Read (RR) in which DB2 ensures an application with isolation level RR doesn’t read a row that another process has changed until that other process has released the row, and that other processes don’t change a row an application with isolation level RR has read until that process commits or terminates. RR is the most restrictive isolation level and ensures the highest level of stability along with the lowest level of concurrency.
• Read Stability (RS) in which DB2 ensures that an application with isolation level RS doesn’t read a row that another process has changed until that other process has released the row, and that other processes don’t change a row an application with isolation level RS has read until that process commits or terminates (except for newly inserted rows or updated rows that don’t satisfy the application’s search condition). RS, like RR, provides a very high level of stability along with a very low level of concurrency.
• Cursor Stability (CS) in which DB2, like RR and RS, ensures that an application with isolation level CS doesn’t read a row that another process has changed until that other process has released the row. Unlike RR, however, CS allows other applications to change rows that the application with isolation level CS has read. This provides for a high level of stability with a high level of concurrency and is the default isolation level.
• Uncommitted Read (UR) in which DB2 ensures nothing. That is, an application reading with isolation level UR can read data that other applications have changed and haven’t yet released. Likewise, other applications can update the data that an application reading with isolation level UR has read. This provides for the highest level of concurrency, but provides for no data stability, as a UR reader can read “dirty” data that may eventually be rolled back.
The vast majority of applications execute with an isolation level of CS and, for the most part, this is satisfactory. However, if a higher level of performance and concurrency are desired, this isolation level may not be good enough. In these situations, an isolation level of UR may be desired or concurrent access resolution may be considered. We’ll examine these choices later.
Finally, the isolation level can be overridden at the statement level. One way to do this is by specifying an isolation clause in a select statement (see Figure 1).
Utilizing Uncommitted Read
By using a strategy that takes advantage of the UR isolation level, you can achieve a very high level of concurrency. This is possible because the uncommitted reader will acquire no locks on the data it’s reading, as well as wait for no locks on the data it’s reading. Since it waits for no locks, it can possibly read data that has been updated by another process and hasn’t yet committed to the database. Therefore, when examining such a strategy, you need to consider that the uncommitted reader will read data that may eventually be rolled back and never reapplied to the database. This requires an understanding of the applications modifying the database and their tolerance for such potential inconsistencies.
The potential for perceived inconsistencies when reading uncommitted may be exaggerated in a data sharing or PureScale environment when one process is modifying data on one member and an uncommitted reader is reading the affected data on another member. This is due to the fact that the uncommitted changes may be cached locally on the member where the updates are taking place, but won’t be written to DASD until a commit or write threshold is reached. The implications to the uncommitted reader may be such things as the presence of child data when a parent row is not present or history row data present when base table data isn’t for a system-period temporal table. This can be exaggerated when the units of work modifying the data are long in duration and is one reason why a very tight commit strategy is important when modifying data being read by uncommitted readers.