Isolation levels and consistency

SQL 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.

SQL Anywhere also allows you to control the isolation levels of individual tables in a query with corresponding table hints. See FROM clause.

SQL Anywhere provides the following isolation levels:

This isolation level...	Has these characteristics...
0—read uncommitted	Read permitted on row with or without write lock No read locks are applied No guarantee that concurrent transaction will not modify row or roll back changes to row Corresponds to table hints NOLOCK and READUNCOMMITTED Allow dirty reads, non-repeatable reads, and phantom rows
1—read committed	Read only permitted on row with no write lock Read lock acquired and held for read on current row only, but released when cursor moves off the row No guarantee that data will not change during transaction Corresponds to table hint READCOMMITTED Prevent dirty reads Allow non-repeatable reads and phantom rows
2—repeatable read	Read only permitted on row with no write lock Read lock acquired as each row in the result set is read, and held until transaction ends Corresponds to table hint REPEATABLEREAD Prevent dirty reads and non-repeatable reads Allow phantom rows
3—serializable	Read only permitted on rows in result without write lock Read locks acquired when cursor is opened and held until transaction ends Corresponds to table hints HOLDLOCK and SERIALIZABLE Prevent dirty reads, non-repeatable reads, and phantom rows
snapshot¹	No read locks are applied Read permitted on any row Database snapshot of committed data is taken when the first row is read or updated by the transaction
statement-snapshot¹	No read locks are applied Read permitted on any row Database snapshot of committed data is taken when the first row is read by the statement
readonly-statement-snapshot¹	No read locks are applied Read permitted on any row Database snapshot of committed data is taken when the first row is read by a read-only statement Uses the isolation level (0, 1, 2, or 3) specified by the updatable_statement_isolation option for an updatable statement

¹ Snapshot isolation must be enabled for the database by setting the allow_snapshot_isolation option to On for the database. See Enabling snapshot isolation.

The default isolation level is 0, except for Open Client, jConnect, and TDS connections, which have a default isolation level of 1.

For information about MobiLink isolation levels, see MobiLink isolation levels.

Lock-based isolation levels prevent some or all interference. Level 3 provides the highest level of isolation. Lower levels allow more inconsistencies, but typically have better performance. Level 0 (read uncommitted) is the default setting.

The snapshot isolation levels prevent all interference between reads and writes. However, writes can still interfere with each other. Few inconsistencies are possible and contention performance is the same as isolation level 0. Performance not related to contention is worse because of the need to save and use row versions.

Notes

All isolation levels guarantee that each transaction executes completely or not at all, and no updates are lost.

The isolation is between transactions only: multiple cursors within the same transaction cannot interfere with each other.

Snapshot isolation
Typical types of inconsistency
Set the isolation level
Setting the isolation level from an ODBC-enabled application
Viewing the isolation level

Discuss this page in DocCommentXchange.