Isolation Levels

An important aspect of transaction processing is the database server's ability to isolate an operation. ANSI standards define four levels of isolation. Each higher level provides transactions a greater degree of isolation from other transactions, and thus a greater assurance that the database remains internally consistent.

The isolation level controls the degree to which operations and data in one transaction are visible to operations in other, concurrent transactions. Table-level snapshot versioning supports the highest level of isolation. At this level, all schedules may be serialized.

Table-level snapshot versioning maintains this high level of isolation between concurrent transactions by following these rules:

Transaction management maintains a snapshot of committed data when each transaction begins.
A transaction can always read, as long as the snapshot version it uses is maintained.
A transaction's writes are reflected in the snapshot it sees.
Once a transaction begins, updates made by other transactions are invisible to it.

The level of isolation that table-level snapshot versioning provides prevents several types of inconsistencies, including (most commonly encountered):

Dirty reads – transaction A modifies an object, but does not commit or roll back the change. Transaction B reads the modified object. Then transaction A further changes the object before performing a COMMIT. In this situation, transaction B has seen the object in a state that was never committed.
Non repeatable reads – transaction A reads an object. Transaction B then modifies or deletes the object and performs a COMMIT. If transaction A attempts to read the same object again, it will have been changed or deleted.
Phantom data elements – transaction A reads a set of data that satisfies some condition. Transaction B then executes an INSERT and then a COMMIT. The newly committed data now satisfies the condition, when it did not previously. Transaction A then repeats the initial read and obtains a different set of data.
Lost updates – in an application that uses cursors, Transaction A writes a change for a set of data. Transaction B then saves an update that is based on earlier data. The changes of Transaction A are completely lost.

Table-level snapshot versioning ensures that only one user can modify a table at any given time, keeps the changes invisible to other users until the changes are complete, and maintains timestamped snapshots of data objects in use at any time.

While IQ allows you to set the isolation level to 0, 1, 2, or 3 (comparable to ANSI levels 1, 2, 3, or 4), there is no reason to do so. All users execute at isolation level 4, even if you set a different level. There is no performance advantage to setting a lower isolation level.

For more information on preventing concurrent transactions from accessing or modifying tables, see the LOCK TABLE statement in Reference: Statements and Options.