Database Isolation Levels, Page 2
Cursor Stability protects against Dirty Reads and Lost Updates, but does not protect against Phantoms and Nonrepeatable Reads. Cursor Stability only locks one row at a time—the row that is currently being referenced by a cursor. As soon as the cursor moves to the next row, the lock on the previous row is released. This provides for much more concurrency because other transactions can update rows before and after a row that is being referenced by a cursor with cursor stability. There are two exceptions to this rule:
- If the cursor with cursor stability retrieves rows using an index, now rows can be modified or inserted into the cursor's result set.
- No transaction can modify or delete a row that has been updated by the Cursor Stability Cursor until the owning transaction is terminated.
This isolation level is a bit more complicated, so let me go through several scenarios:
- Transaction A opens cursor A with isolation level Cursor Stability and starts reading rows that fall under cursor's where clause.
- While cursor A is executing and referencing row 2, transaction B cannot update or delete row 2, but can update and insert all other rows.
- Transaction B can update row 2 as soon as cursor A releases row 2 given that it has not modified it.
- If transaction A re-runs cursor A, there is no guarantee that resultset will be the same—a nonrepeatable read can occur
- While transaction A's cursor is executing row 2, transaction B modifies row 3 and does not commit. Transaction A cannot see that change; therefore, it can in fact overlay transaction B updates—a lost update can occur.
An uncommitted read is the least restrictive of all isolation levels and provides the most database concurrency. This isolation level is mostly used to retrieve read-only data. Transactions running under this isolation level can see uncommitted data. An Uncommitted Read transaction locks only those rows that it modifies or if another transaction attempts to alter or drop the table the rows are being retrieved from. Uncommitted Read does not protect against dirty reads, nonrepeatable reads, and phantoms, but protects against lost updates.
Even though transactions running under Uncommitted Read will see uncommitted rows, they will not see tables, views, and indexes that have been created or dropped until the transaction that created/dropped them commits its changes. Consider the following situations:
- Transaction A, running with isolation level Uncommitted Read ,is updating row 1 and 2.
- While transaction A is running, transaction B (not running under Uncommitted Read) cannot read rows 1 and 2 because these rows are locked by Transaction A—lost updates phenomena cannot occur.
- While transaction A is running, transaction C (running under Uncommitted Read) can read rows 1 and 2—a dirty read can occur.
How to Specify the Isolation Level
The isolation level can be set in more than one way:
- The isolation Level can be set at the application level. For embedded SQL, the isolation level can be specified during BIND or PRECOMPILE.
- The isolation level can be set for CLI (Call Level Interface) using the SQLSetConnectAttr() function by passing the SQLATTR_TXN_ISOLATION attribute.
- The isolation level also can be set for ODBC (Open Database Connectivity) by changing the value of TXNISOLATION in the db2cli.ini file.
- The isolation level can be set in JDBC (Java Database Connection) at runtime. This can be done by invoking the setTransactionIsolation() method that resides in the java.sql.Connection class.
- The isolation Level can be assigned to a SELECT statement using a WITH clause.
ISOLATION [RR | RS | CS | UR]
WITH [RR | RS | CS | UR]
Note: If the isolation level is not explicitly set, the default isolation level used is CURSOR STABILITY.
In this article, you have learned about isolation levels, data concurrency, and database phenomena. You have also briefly touched a much broader subject of locks.
- DB2 9 Fundamentals Certification Study Guide, First Edition (Exam 730) by Roger E. Sanders. MC Press, 2007
- Understanding DB2: Learning Visually with Examples by Raul F. Chong, Clara Liu, Sylvia F. Qi and Dwaine R. Snow , IBM Press, 2005
- DB2 Developer's Guide, Fourth Edition by Craig S. Mullins, Sams, 2000
About the Author
Aleksey Shevchenko has been working with object-oriented languages for over seven years. For the past four years, he has served as a technical lead and a project manager. Aleksey has been implementing Enterprise IT solutions for Wall Street and the manufacturing and publishing industries.