ASA SQL User's Guide
Using Transactions and Isolation Levels
How locking works
The previous sections describe the locks acquired when all transactions are operating at a given isolation level. For example, when all transactions are running at isolation level 2, locking is performed as described in the appropriate section, above.
In practice, your database is likely to need to process multiple transactions that are at different levels. A few transactions, such as the transfer of money between accounts, must be serializable and so run at isolation level 3. For other operations, such as updating an address or calculating average daily sales, a lower isolation level will often suffice.
While the database server is not processing any transactions at level 3, it optimizes some operations so as to improve performance. In particular, many extra anti-insert and insert locks are often necessary to support a level 3 transaction. Under some circumstances, the database server can avoid either placing or checking for some types of locks when no level 3 transactions are present.
For example, the database server uses anti-insert locks to guard against two distinct types of circumstances:
Ensure that deletes in tables with unique attributes can be rolled back.
Eliminate phantom rows in level 3 transactions.
If no level 3 transactions are using a particular table, then the database server need not place anti-insert locks in the index of a table that contains no unique attributes. If, however, even one level 3 transaction is present, all transactions, even those at level 0, must place anti-insert locks so that the level 3 transactions can identify their operations.
Naturally, the database server always attaches notes to a table when it attempts the types of optimizations described above. Should a level 3 transaction suddenly start, you can be confident that the necessary locks will be put in place for it.
You may have little control over the mix of isolation levels in use at one time as so much will depend on the particular operations that the various users of your database wish to perform. Where possible, however, you may wish to select the time that level 3 operations execute because they have the potential to cause significant slowing of database operations. The impact is magnified because the database server is forced to perform extra operations for lower-level operations.