You can use SQLSetConnectAttr to set the transaction isolation level for a connection. The characteristics that determine the transaction isolation level that SAP Sybase IQ 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.
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 http://msdn.microsoft.com/en-us/library/ms713605.aspx.
Example
The following fragment sets the isolation level to Snapshot:
SQLAllocHandle( SQL_HANDLE_DBC, env, &dbc );
SQLSetConnectAttr( dbc, SQL_ATTR_TXN_ISOLATION,
SA_SQL_TXN_SNAPSHOT, SQL_IS_UINTEGER );