iAnywhere.com - Isolation levels and consistency

Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Transactions and Isolation Levels

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
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
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
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
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 to use these isolation levels. 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.

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 inconsistences are possible and performance is the same as isolation level 0 with respect to contention. 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 will execute completely or not at all, and that no updates will be lost.

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

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