Locking in the DB2 under UNIX and PC Hosts Interface

The following LIBNAME and data set options let you control how the DB2 under UNIX and PC Hosts interface handles locking. For general information about an option, see LIBNAME Options for Relational Databases. For additional information, see your DB2 documentation.

READ_LOCK_TYPE= ROW | TABLE

UPDATE_LOCK_TYPE= ROW | TABLE

READ_ISOLATION_LEVEL= RR | RS | CS | UR

The DB2 database manager supports the RR, RS, CS, and UR isolation levels that are defined in the following table. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. All isolation levels therefore ensure that only this application process can change any given row during a unit of work: No other application process can change any rows until the unit of work is complete.

Isolation Levels for DB2 under UNIX and PC Hosts

Isolation Level	Definition
RR (Repeatable Read)	no dirty Reads, no nonrepeatable Reads, no phantom Reads
RS (Read Stability)	no dirty Reads, no nonrepeatable Reads; does allow phantom Reads
CS (Cursor Stability)	no dirty Reads; does allow nonrepeatable Reads and phantom Reads
UR (Uncommitted Read)	allows dirty Reads, nonrepeatable Reads, and phantom Reads

Here is how the terms in the table are defined.

Dirty reads

A transaction that exhibits this phenomenon has very minimal isolation from concurrent transactions. In fact, it can see changes that those concurrent transactions made even before they commit them.

For example, suppose that transaction T1 performs an update on a row, transaction T2 then retrieves that row, and transaction T1 then terminates with rollback. Transaction T2 has then seen a row that no longer exists.

Nonrepeatable reads

If a transaction exhibits this phenomenon, it is possible that it might read a row once and, if it attempts to read that row again later in the course of the same transaction, another concurrent transaction might have changed or even deleted the row. Therefore, the read is not (necessarily) repeatable.

For example, suppose that transaction T1 retrieves a row, transaction T2 then updates that row, and transaction T1 then retrieves the same row again. Transaction T1 has now retrieved the same row twice but has seen two different values for it.

Phantom reads

When a transaction exhibits this phenomenon, a set of rows that it reads once might be a different set of rows if the transaction attempts to read them again.

For example, suppose that transaction T1 retrieves the set of all rows that satisfy some condition. Suppose that transaction T2 then inserts a new row that satisfies that same condition. If transaction T1 now repeats its retrieval request, it sees a row that did not previously exist (a “phantom”).

UPDATE_ISOLATION_LEVEL= CS | RS | RR

The DB2 database manager supports the CS, RS, and RR isolation levels defined in the preceding table. Uncommitted reads are not allowed with this option.