ASA SQL User's Guide
  Using Transactions and Isolation Levels
    Isolation levels and consistency

Setting the isolation level

Each connection to the database has its own isolation level. In addition, the database can store a default isolation level for each user or group. The PUBLIC setting enables you to set a single default isolation level for the entire database's group.

The isolation level is a database option. You change database options using the SET OPTION statement. For example, the following command sets the isolation level for the current user to 3, the highest level.

SET OPTION ISOLATION_LEVEL = 3

You can change the isolation of your connection and the default level associated with your user ID using the SET OPTION command. If you have permission, you can also change the isolation level for other users or groups.

To set the isolation level for the current user ID

• Execute the SET OPTION statement. For example, the following statement sets the isolation level to 3 for the current user:

  SET OPTION ISOLATION_LEVEL = 3

To set the isolation level for a user or group

1. Connect to the database as a user with DBA authority.

2. Execute the SET OPTION statement, adding the name of the group and a period before ISOLATION_LEVEL. For example, the following command sets the default isolation for the special group PUBLIC to 3.

   SET OPTION PUBLIC.ISOLATION_LEVEL = 3

To set the isolation level just for your present session

• Execute the SET OPTION statement using the TEMPORARY keyword. For example, the following statement sets the isolation level to 3 for the duration of your connection:

  SET TEMPORARY OPTION ISOLATION_LEVEL = 3

  Once you disconnect, your isolation level reverts to its previous value.

Default isolation level 

When you connect to a database, the database server determines your initial isolation level as follows:

1. A default isolation level may be set for each user and group. If a level is stored in the database for your user ID, then the database server uses it.

2. If not, the database server checks the groups to which you belong until it finds a level. All users are members of the special group PUBLIC. If it finds no other setting first, then Adaptive Server Anywhere will use the level assigned to that group.

For more information about users and groups, see Managing User IDs and Permissions.

For more information about the SET OPTION statement syntax, see SET OPTION statement.

You may wish to change the isolation level in mid-transaction if, for example, just one table or group of tables requires serialized access. For information about changing the isolation level within a transaction, see Changing isolation levels within a transaction.

Setting the isolation level from an ODBC-enabled application
Changing isolation levels within a transaction