Section 11.3. DB2 Isolation Levels

11.3. DB2 Isolation Levels

DB2 provides four isolation levels to control locking behavior. From the lowest isolation level to the highest these are

• Uncommitted read

• Cursor stability

• Read stability

• Repeatable read

These isolation levels use different locking strategies, so you can choose the level of data protection depending on the application design.

11.3.1. Uncommitted Reads

An uncommitted read (UR) is the lowest isolation level but provides the highest concurrency to the database applications. When you configure an application to perform uncommitted reads, the application will not acquire any row lock to read data. However, a nonrestrictive table lock is required (see Section 11.5, DB2 Locking, for more information). Since no row locks are acquired, there is no conflict with any read or write operations undergoing on the same data. With this isolation level, uncommitted reads, nonrepeatable reads, and phantom reads can still occur.

Figure 11.6 shows an example of two applications accessing the same row. Assume that App A locks row 2 for an update operation. No other application can make changes to row 2. The only concurrent operation that can be issued against row 2 is an uncommitted read as illustrated by App B.

Figure 11.6. Concurrent data access with the uncommitted read isolation level

No lock is acquired for read operations for applications configured with the UR isolation level. For any update, insert, or delete operation, an application with UR will still hold locks until the transaction is committed or rolled back. App C in Figure 11.6 illustrates this.

11.3.2. Cursor Stability

Cursor stability (CS) is the default DB2 isolation level. This isolation level works well with most applications because it uses a certain degree of locking to protect data, and at the same time it also provides a high level of concurrency. As the name of this isolation level implies, it uses a mechanism to provide a stable read where the cursor is positioned. DB2 will only lock the row where the cursor is pointing.

A cursor can be viewed as a pointer to one row in a set of rows (also called a result set). You need to OPEN the cursor so that it is positioned just before the first row of the result set. To move the cursor to the next row, you execute a FETCH operation. As a best practice, you should CLOSE the cursor when it is no longer required.

When a cursor is opened, no lock is acquired until the application fetches the first row of the result set. In the same unit of work, if the application fetches the second row, DB2 will release the previous row lock and acquire a lock on the second row. In Figure 11.7, App A with a CS isolation level fetches row 2. This application will only lock the row it is reading: row 2. When App D tries to alter that particular row, it has to wait.

Figure 11.7. Concurrent read with two applications in cursor stability isolation level

In Figure 11.7 App B holds a lock on row 7 for read (fetching). At the same time, App C obtains a share lock and can still read the same row. Therefore, with isolation level CS, concurrent reads are still possible.

Using isolation level CS, nonrepeatable read and phantom read scenarios can still occur; however, the uncommitted read scenario is not possible.

Now you understand that a row lock is released when the application with CS reads the next row. But what happens if the application makes changes to a row while it is being read? Figure 11.8 illustrates this scenario.

Figure 11.8. Reading and updating data with cursor stability isolation level

(1) App A uses CS isolation level and starts a transaction.

(2) App A locks row 1 for read. App A releases the lock on row 1 when it fetches row 2.

(3) Row 2 is locked. During the read, the App A decides to update the row. The lock will be held until the current transaction is completed (5).

(4) App A fetches row 7 and acquires a lock. At this point App A holds two locks: one for read and one for update.

(5) The current transaction is completed and all locks will be released.

11.3.3. Read Stability

Read stability (RS) is another isolation level DB2 uses to protect data. Unlike CS, RS not only locks the current row that is being fetched, it also applies the appropriate locks to all rows that are in the result set. This ensures that within the same transaction, rows that have been previously read by a cursor cannot be altered by other applications.

Figure 11.9 shows that all the rows in the result set are locked even when the cursor is only processing a particular row. No wait is necessary if more than one application reads the same set of rows concurrently. However, any update operation will have to wait until the reads are completed.

Figure 11.9. The read stability isolation level

RS causes DB2 to perform more locking than the UR or CS isolation levels. With RS, the uncommitted read and nonrepeatable read scenarios cannot occur; however, phantom reads can still happen.

Similar to the other isolation levels, if an application with RS updates a row, a lock will be held until the transaction is completed.

11.3.4. Repeatable Reads

Repeatable (RR) read is the highest isolation level. It also gives you the lowest concurrency. Similar to RS, applications with RR forces DB2 to lock all the rows in the result set as well as rows that are used to build the result set. A query that involves a two-table join is issued and DB2 decides to perform table scans on both tables to obtain the result. This isolation level locks all the rows in the two tables. If a row is read by the application using RR, no other application can alter it until the transaction is completed. This ensures that your result set is consistent throughout the duration of the unit of work. One consideration is that due to the additional locking, it can greatly reduce concurrency.

In Figure 11.10, you can see that behavior for applications A, B, C, and D is the same as RS. However, if App E tries to update a row in table T1 that is not in the result set, it has to wait until the lock is released.

Figure 11.10. The repeatable read isolation level

With repeatable read isolation level, none of the locking scenarios can occur. Applications with RR can only read committed data and perform repeatable read.

Table 11.1 summarizes the four isolation levels and locking scenarios that may occur.

Table 11.1. Four Isolation Levels and Locking Scenarios

Locking Scenarios	Uncommitted Read	Cursor Stability	Read Stability	Repeatable Read
Lost update	No	No	No	No
Uncommitted read	Yes	No	No	No
Nonrepeatable read	Yes	Yes	No	No
Phantom read	Yes	Yes	Yes	No