Isolation level interactions

From InterBase
Jump to: navigation, search

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.