User's Guide
   PART 2. Relational Database Concepts
     CHAPTER 15. Using Transactions and Locks       

Isolation levels and consistency

There are four isolation levels 

Adaptive Server Anywhere allows you to control the degree to which the operations in one transaction are visible to the operations in other concurrent transactions. You do so by setting a database option called the isolation level. Adaptive Server Anywhere has four different isolation levels that prevent some or all inconsistent behavior. These four isolation levels are numbered from 0 through 3. Level 3 provides the highest level of isolation. At level 3, all schedules are serializable.

For example, you can set the isolation for the current connection to level 3 by executing the following statement:

SET TEMPORARY OPTION ISOLATION_LEVEL = 3

     For information about serializable schedules see Correctness.

Lower levels allow more inconsistencies, but you will find them useful when you must give your database a high level of concurrency. Because transactions at lower isolation levels use fewer locks, they tend to reduce blocking. It is less likely that one transaction will need access to rows for which another transaction has acquired a lock.

All isolation levels guarantee that each transaction will execute completely or not at all, and that no updates will be lost. Adaptive Server Anywhere therefore ensures recoverability at all times, regardless of the isolation level.

Isolation levels and dirty reads, non-repeatable reads, and phantom rows 

The isolation levels are different with respect to dirty reads, non-repeatable reads, and phantom rows. The four isolation levels have different names under ODBC, as shown in the bottom row of the table. An x means that the behavior is prevented, and a û means that the behavior may occur.

Isolation level	0	1	2	3
SQLCA.lock	RU	RC	RR	TS
Dirty reads	û	x	x	x
Non-repeatable reads	û	û	x	x
Phantom rows	û	û	û	x

This table demonstrates two points:

• Each isolation level eliminates of the three typical types of inconsistencies.

• Each level eliminates the types of inconsistencies eliminated at all lower levels.

Isolation levels that prevent lost updates 

Read locks prevent lost updates. When a transaction first reads a value, it acquires a read lock on that row. Now, should another transaction also wish to update the row, it must acquire a write lock on the row. Since write locks are exclusive locks, the database server will not grant a write lock to a transaction while any other transaction holds a read lock on the same row. No updates can be lost.

  Setting the isolation level

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 to 3, the highest level.

SET OPTION ISOLATION_LEVEL = 3;

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. You can change the isolation of your connection and the default level associated with your user ID using the SET command. If you have permission, you can also change the isolation level for other users or groups.

In fact, the above command changes both the isolation level for your present connection, and also changes the default level associated with your user ID. Thus, if you form a second connection after executing this command, the isolation will initially be set to level 3.

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 Anywhere will use the level assigned to that group.

Once connected to the database, you can change the isolation level for your connection using the TEMPORARY option of the SET command. Temporary options stay in effect only as long as you remain connected. They do not change defaults stored for your user ID. They affect only the connection in which the command is executed. To set your isolation level to level 2 for the duration of your present session, you use the following command.

SET TEMPORARY OPTION ISOLATION_LEVEL = 2;

To set the option for a group, prepend the name of the group and a period to ISOLATION_LEVEL. For example, the following command sets the default isolation for the special group PUBLIC.

SET OPTION PUBLIC.ISOLATION_LEVEL = 3;

To change the default isolation level of a group you must have permission to do so. Because the group PUBLIC is special, you must have dba privilege to change settings associated with it.

Setting temporary options for the PUBLIC group has a special effect. The setting stays in effect only as long as the present database engine remains in operation. Should the database engine shut down, the original default will appear when the database engine is restarted. Again, because PUBLIC is a special group, you must have dba privilege to set its options.

     For further information about users and groups, please refer to Managing User IDs and Permissions.

     For a description of 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 with in a transaction, see Changing the isolation level within a transaction.

  Setting the isolation level from an ODBC-enabled application

ODBC uses the isolation feature to support assorted database lock options. For example, in PowerBuilder you can use the Lock attribute of the transaction object to set the isolation level when you connect to the database. The Lock attribute is a string, and is set as follows:

// Set the lock attribute to read uncommitted
// in the default transaction object SQLCA.
SQLCA.lock = "RU"