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

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  SQLSetConnectAttr in the Microsoft ODBC Programmer's Reference.

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 );