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

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

isolation_level option

Controls the locking isolation level.

Allowed values

0, 1, 2, 3, Snapshot, Statement-snapshot, Readonly-statement-snapshot

Default

0

A temporary setting for the current user is established by the JDBC driver (0).

A temporary setting for the current user is established by SAP Open Client and jConnect TDS connections (1).

Scope
  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY PUBLIC OPTION Yes Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily? Yes, with SET ANY PUBLIC OPTION Yes (current connection only) No
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).

Set the allow_snapshot_isolation option to On to use the Snapshot, Statement-snapshot, or Readonly-statement-snapshot settings.

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.