Sybase 12.4.2 manual Checkpoints, savepoints, and transaction rollback, 303

CHAPTER 8 Transactions and Versioning

The level of isolation that Adaptive Server IQ provides prevents several types of inconsistencies. The ones most commonly encountered are listed here:

•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 Update 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. Transaction A's changes are completely lost.

Adaptive Server IQ protects you from all of these inconsistencies by ensuring that only one user can modify a table at any given time, by keeping the changes invisible to other users until the changes are complete, and by maintaining time-stamped 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) using SET OPTION ISOLATION_LEVEL, 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.

Checkpoints, savepoints, and transaction rollback

Besides permitting concurrency, transaction processing plays an important role in data recovery. Database recovery always recovers every committed transaction. Transactions that have not committed at the time of a database crash are not recovered.

303