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 of the isolation_level database option enables you to set a single default isolation level for the entire database group.

You can also set the isolation level using table hints, but this is an advanced feature that should be used only when needed. For more information, see the WITH table-hint section in FROM clause.

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

If you want to use snapshot isolation, you must first enable snapshot isolation for the database.

For information about enabling and setting snapshot isolation levels, see Enabling snapshot isolation.

To set the isolation level for the current user

  • 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 PUBLIC group to 3. 

    SET OPTION PUBLIC.isolation_level = 3;

To set the isolation level just the current connection

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

    SET TEMPORARY OPTION isolation_level = 3;
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 SQL Anywhere uses the level assigned to that group.

For more information about users and groups, see Managing user IDs, authorities, and permissions.

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

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

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0