Isolation level interactions

Go Up to Specifying Isolation Level

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.