ASA SQL User's Guide
  Using Transactions and Isolation Levels
    Isolation levels and consistency
      Setting the isolation level

Changing isolation levels within a transaction

Sometimes you will find that different isolation levels are suitable for different parts of a single transaction. Adaptive Server Anywhere allows you to change the isolation level of your database in the middle of a transaction.

When you change the ISOLATION_LEVEL option in the middle of a transaction, the new setting affects only the following:

• Any cursors opened after the change

• Any statements executed after the change

You may wish to change the isolation level during a transaction, as doing so affords you control over the number of locks your transaction places. You may find a transaction needs to read a large table, but perform detailed work with only a few of the rows. If an inconsistency would not seriously affect your transaction, set the isolation to a low level while you scan the large table to avoid delaying the work of others.

You may also wish to change the isolation level in mid transaction if, for example, just one table or group of tables requires serialized access.

For an example in which the isolation level is changed in the middle of a transaction, see Phantom row tutorial.