ASA SQL User's Guide
Using Transactions and Isolation Levels
Isolation levels and consistency
Setting the isolation level
ODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set according to the corresponding isolation level:
| ValuePtr | Isolation Level |
|---|---|
| SQL_TXN_READ_UNCOMMITTED | 0 |
| SQL_TXN_READ_COMMITTED | 1 |
| SQL_TXN_REPEATABLE_READ | 2 |
| SQL_TXT_SERIALIZABLE | 3 |
You can change the isolation level of your connection via ODBC using the function SQLSetConnectOption in the library ODBC32.dll.
The SQLSetConnectOption function reads three parameters: the value of the ODBC connection handle, the fact that you wish to set the isolation level, and the value corresponding to the isolation level. These values appear in the table below.
| String | Value |
|---|---|
| SQL_TXN_ISOLATION | 108 |
| SQL_TXN_READ_UNCOMMITTED | 1 |
| SQL_TXN_READ_COMMITTED | 2 |
| SQL_TXN_REPEATABLE_READ | 4 |
| SQL_TXT_SERIALIZABLE | 8 |
Do not use the SET OPTION statement to change an isolation level from within an ODBC application. Since the ODBC driver does not parse the statements, execution of any statement in ODBC will not be recognized by the ODBC driver.
The following function call sets the isolation level of the connection MyConnection to isolation level 2:
SQLSetConnectOption( MyConnection.hDbc,
SQL_TXN_ISOLATION,
SQL_TXN_REPEATABLE_READ )ODBC uses the isolation feature to support assorted database lock options. For example, in PowerBuilder you can use the Lock attribute of the transaction object to set the isolation level when you connect to the database. The Lock attribute is a string, and is set as follows:
SQLCA.lock = "RU"
The Lock option is honored only at the moment the CONNECT occurs. Changes to the Lock attribute after the CONNECT have no effect on the connection.