Transaction isolation and locking considerations for JDBC and DB2 UDB for iSeries

The DB2 Universal Database (UDB) for iSeries documentation uses the terms activation groups and commitment definitions. If you are more familiar with JDBC or ODBC, you can equate these terms to JDBC-specific terms:

WAS terminology	DB2 UDB for iSeries terminology
JDBC connection	activation group
transaction	commitment definition
transaction isolation levels	commitment-control lock levels

This topic describes how transaction isolation levels and commitment-control lock levels relate to each other, and how your JDBC application makes use of the various levels.

Data locks

DB2 UDB for iSeries locks data in row units (records) with these types of locks:

• Shared lock
  Shared locking limits concurrent connections that use different transactions to read-only operations on the data.

• Exclusive lock
  Exclusive locking prevents concurrent connections that are using different transactions from updating or deleting the data. Exclusive lock also prevents some concurrent connections from reading the data. Concurrent connections that are running TRANSACTION_REPEATABLE_READ (*RS or *ALL), TRANSACTION_READ_COMMITTED (*CS), or TRANSACTION_SERIALIZABLE (*RR) cannot read the data. Connections that are running TRANSACTION_READ_UNCOMMITTED (*UR or *CHG) or No Commit (*NC) are allowed to read the data.

DB2 UDB for iSeries can use both shared and exclusive locks in a multiple read-and-write situation.

Isolation levels

DB2 UDB for iSeries supports five isolation levels. For all isolation levels except No Commit, the database manager places exclusive locks on every row that is inserted, updated, or deleted. This ensures that any row changed during a transaction is not changed by any other transaction until after the modifications are committed.

For complete detailed descriptions of the various locks obtained while under a given transaction, refer to these DB2 UDB for iSeries documentation resources:

• For V5R2
  • Commitment Control
  • Isolation Level

• For V5R1
  • Commitment Control
  • Isolation Level

The following table briefly describes JDBC transaction isolation levels, CLI transaction isolation levels, and DB2 UDB for iSeries locking levels. It can be used as a reference when using iSeries commands or when using DB2 UDB for iSeries documentation.

Note: The table that follows does not include the No Commit isolation level, which does not use exclusive locks. Note also that enterprise beans do not support the No Commit isolation level.

• TRANSACTION_READ_UNCOMMITTED

  DB2 CLI value	SQL_TXN_READ_UNCOMMITTED
  Meaning	Dirty reads, nonrepeatable reads, and phantom reads are possible.
  DB2 UDB for iSeries documentation	Uncommitted read (*UR or *CHG).
  Row locks	During read: In general, none. During insert, update, or delete: In general, update until commit or rollback.
  Table or view locks	None

• TRANSACTION_READ_COMMITTED

  DB2 CLI value	SQL_TXN_READ_COMMITTED
  Meaning	Dirty reads are not possible. Nonrepeatable reads and phantom reads are possible.
  DB2 UDB for iSeries documentation:	Cursor stability (*CS)
  Row locks	During read: In general, read (released after each access). During insert, update, delete: In general, update until commit or rollback.
  Table or view locks	None

• TRANSACTION_REPEATABLE_READ

  DB2 CLI value	SQL_TXN_REPEATABLE_READ
  Meaning	Dirty reads and nonrepeatable reads are not possible. Phantom reads are possible.
  DB2 UDB for iSeries documentation:	Read stability (*RS or *ALL)
  Row locks:	During read: In general, read until commit or rollback. During insert, update, delete: In general, update until commit or rollback.
  Table or view locks	None

• TRANSACTION_SERIALIZABLE

  DB2 CLI value	SQL_TXN_SERIALIZABLE
  Meaning	Dirty reads, nonrepeatable reads, and phantom reads are not possible.
  DB2 UDB for iSeries documentation:	Repeatable read (*RR)
  Row locks	During read: In general, read until commit or rollback. During insert, update, delete: In general, update until commit or rollback.
  Table or view locks	*SHRNUP for read-only mode, *EXCLRD for update mode

For a JDBC connection that is running with automatic commit turned on (for example, through a call to Connection.setAutoCommit(true)), the types of locks acquired are the same as previously described. However, the locks are only held for the duration of the individual structured query language (SQL) operations.

When you access data with any transaction isolation level greater than No Commit, the table must be journaled. If the file being accessed is an SQL TABLE (physical file), then it is automatically journaled in the collection (library) in which it resides. However, explicitly journal a physical file that resides in a library. You can very loosely think of running a JDBC connection under either the NO_COMMIT or TRANSACTION_NONE isolation level as an optimization of auto-commit mode.