User's Guide
PART 2. Relational Database Concepts
CHAPTER 15. Using Transactions and Locks
Adaptive Server Anywhere affords you control over the amount of locking it uses to isolate transactions. For example, you can eliminate inconsistencies such as the dirty read demonstrated in the previous example.
This section revisits the dirty read and introduces two other typical types of inconsistencies which you may encounter. Knowledge of these inconsistencies will help you select appropriate levels of isolation for the transactions in your own databases.
It then proceeds to introduce a fourth type of inconsistency known as lost updates. These are particularly interested to you if you use something known as cursors in your SQL programs.
Three typical types of inconsistencyThere are three typical types of inconsistency that can occur during the execution of concurrent transactions. This list is not exhaustive as other types of inconsistencies can also occur. These three types are mentioned in the ISO SQL/92 standard and are important because behavior at lower isolation levels is defined in terms of them.
You will recognize the first type as the one you demonstrated in the previous tutorial.
Dirty read Transaction A modifies a row, but does not commit or roll back the change. Transaction B reads the modified row. Transaction A then either further changes the row before performing a COMMIT, or rolls back its modification. In either case, transaction B has seen the row in a state which was never committed.
Non-repeatable read Transaction A reads a row. Transaction B then modifies or deletes the row and performs a COMMIT. If transaction A then attempts to read the same row again, the row will have been changed or deleted.
Phantom row Transaction A reads a set of rows that satisfy some condition. Transaction B then executes an INSERT, or an UPDATE on a row which did not previously meet A's condition. Transaction B commits these changes. These newly committed rows now satisfy the condition. Transaction A then repeats the initial read and obtains a different set of rows.
Other types of inconsistencies can also exist. These three were chosen for the ISO SQL/92 standard because they are typical problems and because it was convenient to describe amounts of locking between transactions in terms of them.
Amounts of locking will be described further in Isolation levels and consistency.