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.
SQL Anywhere provides the following isolation levels:
This isolation level... | Has these characteristics... |
---|---|
0—read uncommitted |
|
1—read committed |
|
2—repeatable read |
|
3—serializable |
|
snapshot1 |
|
statement-snapshot1 |
|
readonly-statement-snapshot1 |
|
1 Snapshot isolation must be enabled for the database by setting the allow_snapshot_isolation option to On for the database.
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 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.
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
Isolation levels in ODBC-enabled applications
Viewing the isolation level
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |