How to set 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 user-extended role. The PUBLIC setting of the isolation_level database option enables you to set a default isolation level.

You can also set the isolation level using table hints, but this is an advanced feature that is for setting the isolation level for an individual statement.

You can change the isolation level of your connection and the default level associated with your user ID using the SET OPTION statement. You can also change the isolation level for other users or groups.

Default isolation level

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

A default isolation level may be set for each user and role. If a level is stored in the database for your user ID, then the database server uses it.
If not, the database server checks the groups to which you belong until it finds a level. All users are grantees of the PUBLIC role. If it finds no other setting first, then SQL Anywhere uses the level assigned to PUBLIC.

Note

To use snapshot isolation, you must first enable snapshot isolation for the database.

Example

Example 1: 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;

Example 2: Set the isolation level for a user or for the PUBLIC role

Connect to the database.

Execute the SET OPTION statement, adding the grantee name and a period before isolation_level. For example, the following statement sets the default isolation for the PUBLIC role to 3.

SET OPTION PUBLIC.isolation_level = 3;

Example 3: Set the isolation level for 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;