Specifying Isolation Level
Go Up to Creating a Transaction Parameter Buffer
Contents
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 withisc_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:
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 |
|
Some simultaneous updates may conflict |
— |
Conflicts |
Conflicts |
|
— |
— |
— |
— | |
consistency |
|
Conflicts |
— |
Conflicts |
Conflicts |
|
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.