Choosing Between SNAPSHOT and READ COMMITTED

Go Up to Isolation Level

The choice between SNAPSHOT and READ COMMITTED isolation levels depends on the needs of an application. SNAPSHOT is the default InterBase isolation level. READ ­COMMITTED duplicates SNAPSHOT behavior, but can read subsequent changes committed by other transactions. In many cases, using READ COMMITTED reduces data contention.

SNAPSHOT transactions receive a stable view of a database as it exists the moment the transactions start. READ COMMITTED transactions can see the ­latest committed versions of rows. Both types of transactions can use SELECT statements unless they encounter the following conditions:

• Table locked by SNAPSHOT TABLE STABILITY transaction for UPDATE.
• Uncommitted inserts made by other simultaneous transactions. In this case, a SELECT is allowed, but changes cannot be seen.

READ COMMITTED transactions can read the latest committed version of rows. A SNAPSHOT transaction can read only a prior version of the row as it existed before the update occurred.

SNAPHOT and READ COMMITTED transactions with READ WRITE access can use INSERT, UPDATE, and DELETE unless they encounter tables locked by SNAPSHOT TABLE STABILITY transactions.

SNAPSHOT transactions cannot update or delete rows previously updated or deleted and then committed by other simultaneous transactions. Attempting to update a row previously updated or deleted by another transaction results in an update conflict error.

A READ COMMITTED READ WRITE transaction can read changes committed by other transactions, and subsequently update those changed rows.

Occasional update conflicts may occur when simultaneous SNAPSHOT and READ COMMITTED transactions attempt to update the same row at the same time. When update conflicts occur, expect the following behavior:

• For mass or searched updates, updates where a single UPDATE modifies multiple rows in a table, all updates are undone on conflict. The UPDATE can be retried. For READ COMMITTED transactions, the NORECORD_VERSION option can be used to narrow the window between reads and updates or deletes. For more information, see Starting a transaction with READ COMMITTED isolation level.
• For cursor or positioned updates, where rows are retrieved and updated from an active set one row at a time, only a single update is undone. To retry the update, the cursor must be closed, then reopened, and updates resumed at the point of previous conflict.

For more information about UPDATE through cursors, see Working with Data.