Choosing ODBC transaction isolation level

You can use SQLSetConnectAttr to set the transaction isolation level for a connection. The characteristics that determine the transaction isolation level that SQL Anywhere provides include the following:

  • SQL_TXN_READ_UNCOMMITTED   Set isolation level to 0. When this attribute value is set, it isolates any data read from changes by others and changes made by others cannot be seen. The re-execution of the read statement is affected by others. This does not support a repeatable read. This is the default value for isolation level.

  • SQL_TXN_READ_COMMITTED   Set isolation level to 1. When this attribute value is set, it does not isolate data read from changes by others, and changes made by others can be seen. The re-execution of the read statement is affected by others. This does not support a repeatable read.

  • SQL_TXN_REPEATABLE_READ   Set isolation level to 2. When this attribute value is set, it isolates any data read from changes by others, and changes made by others cannot be seen. The re-execution of the read statement is affected by others. This supports a repeatable read.

  • SQL_TXN_SERIALIZABLE   Set isolation level to 3. When this attribute value is set, it isolates any data read from changes by others, and changes made by others cannot be seen. The re-execution of the read statement is not affected by others. This supports a repeatable read.

  • SA_SQL_TXN_SNAPSHOT   Set isolation level to snapshot. When this attribute value is set, it provides a single view of the database for the entire transaction.

  • SA_SQL_TXN_STATEMENT_SNAPSHOT   Set isolation level to statement-snapshot. When this attribute value is set, it provides less consistency than snapshot isolation, but may be useful in cases where long running transactions result in too much space being used in the temporary file by the version store.

  • SA_SQL_TXN_READONLY_STATEMENT_SNAPSHOT   Set isolation level to readonly-statement-snapshot. When this attribute value is set, it provides somewhat less consistency than statement-snapshot isolation, but avoids the possibility of update conflicts. Therefore, it is most appropriate for porting applications originally intended to run under different isolation levels.

For more information, see [external link] SQLSetConnectAttr in the Microsoft ODBC Programmer's Reference.

Example

The following fragment uses a snapshot isolation level:

SQLAllocHandle( SQL_HANDLE_DBC, env, &dbc );
SQLSetConnectAttr( dbc, SQL_ATTR_TXN_ISOLATION,
      SA_SQL_TXN_SNAPSHOT, SQL_IS_UINTEGER );
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0