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 |
|
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. 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 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.
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
Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |