User's Guide Part III. Using SQL Anywhere Chapter 19. Using Transactions and LocksThe degree to which the operations in one transaction are visible to the operations in a concurrent transaction is defined by isolation level. SQL Anywhere has four different isolation levels that prevent some or all inconsistent behavior. The isolation level is a database option that can be different for each connection. Database options are changed by using the SET command; the default setting is isolation level 0. For a description of the SET statement syntax, see the chapter "Watcom-SQL Language Reference".
All isolation levels guarantee that each transaction will execute completely or not at all, and that no updates will be lost. SQL Anywhere therefore ensures recoverability at all times, regardless of the isolation level.
The isolation levels are different with respect to dirty reads, nonrepeatable reads, and phantom rows. An x means that the behavior is prevented, and a û means that the behavior may occur.
| Isolation level | 0 | 1 | 2 | 3 |
|---|---|---|---|---|
| Dirty reads | û | x | x | x |
| Non-repeatable reads | û | û | x | x |
| Phantom rows | û | û | û | x |
If your application is using cursors to perform retrievals and updates, there is an additional level of isolation called cursor stability. Cursor stability guarantees that any row that is the current position of a cursor will not be modified by another transaction until the cursor leaves the row. No row fetched through a cursor yields uncommitted data. SQL Anywhere automatically provides cursor stability at isolation levels 1, 2, and 3.
Write locks are employed at all isolation levels. This ensures that once data is modified by a transaction, no other transaction can modify it until the transaction is either committed or rolled back. As long as the transaction completes, there is no danger of its database update being interfered with by any other user.
The default setting for SQL Anywhere is isolation level 0. Read locks are not employed at this isolation level.
When all transactions are running at isolation level 0, the only time a locking conflict occurs is when one transaction attempts to update or delete a row that has been inserted or updated by a different transaction and not yet committed.
The only distinction between levels 0 and 1 occurs when one transaction has a write lock on a row (because it is modifying the row) and a second transaction attempts to read the row.
At isolation level 0, the second transaction is allowed to read the row, at the risk that the read may be dirty. At isolation level 1, the second transaction checks to see if there is a write lock on the row before reading it, and is not allowed to read it if a write lock is in place.
At isolation level 1, cursor stability is achieved by putting a read lock on the current row of a cursor. This read lock is removed when the cursor is moved. This is the only type of lock that does not persist until the end of a transaction.
Read locks that persist until the end of a transaction are introduced at isolation level 2. Level 2 guarantees that there will be no non-repeatable reads. If an application once reads a row, that row will be available to be read again, and will give the same result.
No guarantee is given that the row can be updated, however, as another transaction may also have a read lock on the row.
Isolation level 3 is the most secure level, and is also the one at which concurrency is most affected.
At isolation level 3 a phantom lock is employed to prevent phantom rows. If your application looks up data in tables without using an index, concurrency can suffer considerably under isolation level 3.
SQL Anywhere allows you to change isolation levels at any time, including within a transaction, using the SET OPTION statement.
For more information, see "SET OPTION statement".
When the ISOLATION_LEVEL option is changed in the middle of a transaction, the new setting affects only the following:
This may be useful, for example, when just one table or group of tables involved in a transaction requires serialized access, and so a high isolation level.