Comparing SNAPSHOT, READ COMMITTED, and SNAPSHOT TABLE STABILITY

There are five classic problems all transaction management statements must address:

Lost updates, which can occur if an update is overwritten by a simultaneous transaction unaware of the last updates made by another transaction.
Dirty reads, which can occur if the system allows one transaction to select uncommitted changes made by another transaction.
Non-reproducible reads, which can occur if one transaction is allowed to update or delete rows that are repeatedly selected by another transaction. READ COMMITTED transactions permit non-reproducible reads by design, since they can see committed deletes made by other transactions.
Phantom rows, which can occur if one transaction is allowed to select some, but not all, new rows written by another transaction. READ COMMITTED transactions do not prevent phantom rows.
Update side effects, which can occur when row values are interdependent, and their dependencies are not adequately protected or enforced by locking, triggers, or integrity constraints. These conflicts occur when two or more simultaneous transactions randomly and repeatedly access and update the same data; such transactions are called interleaved transactions.

Except as noted, all three InterBase isolation levels control these problems. The following table summarizes how a transaction with a particular isolation level controls access to its data for other simultaneous transactions:

InterBase management of classic transaction conflicts
Problem	`SNAPSHOT, READ COMMITTED`	`SNAPSHOT TABLE STABILITY`
Lost updates	Other transactions cannot update rows already updated by this transaction.	Other transactions cannot update tables controlled by this transaction.
Dirty reads	Other `SNAPSHOT` transactions can only read a previous version of a row updated by this transaction. Other `READ COMMITTED` transactions can only read a previous version, or committed updates.	Other transactions cannot access tables updated by this transaction.
Non-reproducible reads	`SNAPSHOT` and `SNAPSHOT TABLE STABILITY` transactions can only read versions of rows committed when they started. `READ COMMITTED` transactions must expect that reads cannot be reproduced.	`SNAPSHOT` and `SNAPSHOT TABLE STABILITY` transactions can only read versions of rows committed when they started. Other transactions cannot access tables updated by this transaction.
Phantom rows	`READ COMMITTED` transactions may encounter phantom rows.	Other transactions cannot access tables controlled by this transaction.
Update side effects	Other SNAPSHOT transactions can only read a previous version of a row updated by this transaction. Other `READ COMMITTED` transactions can only read a previous version, or committed updates. Use triggers and integrity constraints to try to avoid any problems with interleaved transactions.	Other transactions cannot update tables controlled by this transaction. Use triggers and integrity constraints to avoid any problems with interleaved transactions.

Advance To:

Choosing Between SNAPSHOT and READ COMMITTED