Chapter Contents

Previous

Next

UPDATE_ISOLATION_LEVEL=

Default value:	CS
Alias:	UIL=

	CS = Cursor Stability
	RS = Read Stability
	RR = Repeatable Read

Details

The degree of isolation identifies

• the degree with which rows that are read and updated by the current application are available to other concurrently executing applications

• the degree with which update activity of other concurrently executing application processes can affect the current application.

The DB2 database manager supports three isolation levels. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this application process during a unit of work is not changed by any other application process until the unit of work is complete. The isolation levels are defined in terms of several possible occurrences:

• Dirty read -- A transaction that exhibits this phenomenon has very minimal isolation from concurrent transactions. In fact, it will be able to see changes that are made by those concurrent transactions even before they commit.

  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 read -- If a transaction exhibits this phenomenon, it is possible that it may read a row once and, if it attempts to read that row again later in the course of the same transaction, the row might have been changed or even deleted by another concurrent transaction. 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 phenomemon, 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 will see a row that did not previously exist, a phantom.

The isolation levels for UPDATE_ISOLATION_LEVEL= include the following:

• Repeatable Read (RR)
  • does not allow dirty reads

  • does not allow nonrepeatable reads

  • does not allow phantom reads

• Read Stability (RS)
  • does not allow dirty reads

  • does not allow nonrepeatable reads

  • allows phantom reads

• Cursor Stability (CS)
  • does not allow dirty reads

  • allows nonrepeatable reads

  • allows phantom reads

  This is the default value for DB2.

UPDATE_ISOLATION_LEVEL= is ignored if UPDATE_LOCK_TYPE= is not set to ROW.

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.