Isolation level interactions (Embedded SQL Guide)

Go Up to Isolation Level

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

Isolation level Interaction with SELECT and UPDATE

		SNAPSHOT or READ COMMITTED		SNAPSHOT TABLE STABILITY
		UPDATE	SELECT	UPDATE	SELECT
SNAPSHOT or READ COMMITTED	UPDATE	Some simultaneous updates may conflict	—	Always conflicts	Always conflicts
	SELECT	—	—	—	—
SNAPSHOT TABLE <br/> STABILITY	UPDATE	Always conflicts	—	Always conflicts	Always conflicts
	SELECT	Always conflicts	—	Always conflicts	—

As this table illustrates, SNAPSHOT and READ COMMITTED transactions offer the least chance for conflicts. For example, if t1 is a SNAPSHOT transaction with READ WRITE access, and t2 is a READ COMMITTED transaction with READ WRITE access, t1 and t2 only conflict when they attempt to update the same rows. If t1 and t2 have READ ONLY access, they never conflict with any other transaction.

A SNAPSHOT TABLE STABILITY transaction with READ WRITE access is guaranteed that it alone can update tables, but it conflicts with all other simultaneous transactions except for SNAPSHOT and READ COMMITTED transactions running in READ ONLY mode. A SNAPSHOT TABLE STABILITY transaction with READ ONLY access is compatible with any other read-only transaction, but conflicts with any transaction that attempts to insert, update, or delete data.