isolation_level option [database] [compatibility]

Controls the locking isolation level.

Allowed values

0, 1, 2, 3, snapshot, statement-snapshot, readonly-statement-snapshot

Default

0

1 for Open Client, jConnect, and TDS connections

Scope

Can be set for an individual connection or for the PUBLIC group. Takes effect immediately.

Remarks

This option controls the locking isolation level as follows:

  • 0   Allow dirty reads, non-repeatable reads, and phantom rows.

  • 1   Prevent dirty reads. Allow non-repeatable reads and phantom rows.

  • 2   Prevent dirty reads and non-repeatable reads. Allow phantom rows.

  • 3   Serializable. Prevent dirty reads, non-repeatable reads, and phantom rows.

  • snapshot   Use a snapshot of committed data from the time when the first row is read or updated by the transaction.

  • statement-snapshot   For each statement, use a snapshot of committed data from the time when the first row is read from the database. Non-repeatable reads and phantom rows can occur within a transaction, but not within a single statement.

  • readonly-statement-snapshot   For read-only statements, use a snapshot of committed data from the time when the first row is read from the database. Non-repeatable reads and phantom rows can occur within a transaction, but not within a single statement. For updatable statements, use the isolation level specified by the updatable_statement_isolation option (can be one of 0 (the default), 1, 2, or 3).

For more details about supported isolation levels, see Isolation levels and consistency.

The allow_snapshot_isolation option must be set to On to use the snapshot, statement-snapshot, or readonly-statement-snapshot settings.

If you are using the iAnywhere JDBC driver, the default isolation level is 0.

Queries running at isolation level snapshot, statement-snapshot, or readonly-statement-snapshot see a snapshot of a committed state of the database.

You can override any temporary or public settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements by including an OPTION clause in the statement. See:

See also
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1