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.
|
| ||||
---|---|---|---|---|---|
UPDATE
|
SELECT
|
UPDATE
|
SELECT
| ||
|
|
Some simultaneous updates may conflict |
— |
Always conflicts |
Always conflicts |
|
— |
— |
— |
— | |
|
|
Always conflicts |
— |
Always conflicts |
Always conflicts |
|
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.