Specifying Isolation Level

From InterBase

Go Up to Creating a Transaction Parameter Buffer

The isolation level parameter specifies the view of the database permitted a transaction as it relates to actions performed by other simultaneously occurring transactions.

isc_tpb_concurrency

By default, a transaction sees a stable view of the database as of the instant it starts and can share read/write access to tables with simultaneous transactions. This mode is known as “concurrency” because it allows concurrent transactions to share data. The following declaration creates a TPB specifying an isolation level of isc_tpb_concurrency:

static char isc_tpb[] = {isc_tpb_version3, isc_tpb_write, isc_tpb_concurrency};

isc_tpb_read_committed

A second isolation level, isc_tpb_read_committed, does not provide a consistent view of the database. Unlike a concurrency transaction, a read committed transaction sees changes made and committed by transactions that were active after this transaction started. Two other parameters, isc_tpb_rec_version, and isc_tpb_no_rec_version, should be used with the isc_tpb_read_committed parameter. They offer refined control over the committed changes a transaction is permitted to access:

  • isc_tpb_no_rec_version, the default refinement, specifies that a transaction can only read the latest version of a row. If a change to a row is pending, but not yet committed, the row cannot be read. Although default, this refinement may impose unnecessary wait conditions for your transaction. This option should be combined with isc_tpb_nowait to avoid deadlock messages.
  • isc_tpb_rec_version specifies that a transaction can read the latest committed version of a row, even if a more recent uncommitted version is pending.

The following declaration creates a TPB with a read committed isolation level, and specifies that the transaction can read the latest committed version of a row:

static char isc_tpb[] = {isc_tpb_version3, isc_tpb_write,
isc_tpb_read_committed, isc_tpb_rec_version};

isc_tpb_consistency

InterBase also supports a restrictive isolation level. isc_tpb_consistency prevents a transaction from accessing tables if they are written to by other transactions; it also prevents other transactions from writing to a table once this transaction reads from or writes to it. This isolation level is designed to guarantee that if a transaction writes to a table before other simultaneous read and write transactions, then only it can change a data of a table. Because it restricts shared access to tables, isc_tpb_consistency should be used with care. However, this mode is serializable, so it meets the highest requirements for transaction consistency. Combined with table reservations, it is also deadlock-free.

A TPB should only specify one isolation mode parameter (and one refinement parameter, if isolation mode is isc_tpb_read_committed). If more than one is specified, later declarations override earlier ones.

If a TPB is declared that omits the isolation mode parameter, InterBase interprets it as isc_tpb_concurrency.

Isolation level interactions

To determine the possibility for lock conflicts between two transactions accessing the same database, each transaction’s isolation level and access mode must be considered. The following table summarizes possible combinations:

Isolation Level Interaction with Read and Write Operations

isc_tpb_concurrency, isc_tpb_read_committed

isc_tpb_consistency

isc_tpb_write

isc_tpb_read

isc_tpb_read

isc_tpb_writes

concurrency, read_committed

isc_tpb_write

Some simultaneous updates may conflict

Conflicts

Conflicts

isc_tpb_read

consistency

isc_tpb_write

Conflicts

Conflicts

Conflicts

isc_tpb_read

Conflicts

Conflicts

As this table illustrates, isc_tpb_concurrency and isc_tpb_read_committed transactions offer the least chance for conflicts. For example, if t1 is an ­isc_tpb_concurrency transaction with isc_tpb_write access, and t2 is an ­isc_tpb_read_committed transaction with isc_tpb_write access, t1 and t2 only conflict when they attempt to update the same rows. If t1 and t2 have isc_tpb_read access, they never conflict with other transactions.

An isc_tpb_consistency transaction with isc_tpb_write access is guaranteed that if it gains access to a table that it alone can update a table, but it conflicts with all other simultaneous transactions except for isc_tpb_concurrency and ­isc_tpb_read_committed transactions running in isc_tpb_read mode. An ­isc_tpb_consistency transaction with isc_tpb_read access is compatible with any other read-only transaction, but conflicts with any transaction that attempts to insert, update, or delete data.

Advance To: