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

SQL Anywhere 12.0.0 » SQL Anywhere Server - Programming » ODBC support » Working with result sets

 

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 when 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 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.

Note that the allow_snapshot_isolation database option must be set to On to use the Snapshot, Statement-snapshot, or Readonly-statement-snapshot settings.

For more information, see SQLSetConnectAttr in the Microsoft ODBC API Reference at [external link] http://msdn.microsoft.com/en-us/library/ms713605.aspx.

 Example