| Navigation: SQL Driver Strings(General) >====== ISOLATIONLEVEL ====== |    |
DRIVER('SQLDriver', '/ISOLATIONLEVEL = number' )
number = SEND(file,'/ISOLATIONLEVEL = number')
file{PROP:IsolationLevel} = number
number = file{PROP:IsolationLevel}
(NOTE: Not valid for ORACLE Accelerator)
The following terms are used to define transaction isolation levels:
| Dirty Read | Transaction 1 changes a row. Transaction 2 reads the changed row before transaction 1 commits the change. If transaction 1 rolls back the change, transaction 2 will have read a row that is considered to have never existed. |
| Nonrepeatable Read | Transaction 1 reads a row. Transaction 2 updates or deletes that row and commits this change. If transaction 1 attempts to reread the row, it will receive different row values or discover that the row has been deleted. |
| Phantom | Transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 inserts a row that matches the search criteria. If transaction 1 reexecutes the statement that read the rows, it receives a different set of rows. |
The number parameter must be one of the following values:
| 1 | Dirty reads, nonrepeatable reads, and phantoms are possible. |
| 2 | Dirty reads are not possible. Nonrepeatable reads and phantoms are possible. |
| 4 | Dirty reads and nonrepeatable reads are not possible. Phantoms are possible. |
| 8 | Transactions are serializable. Dirty reads, nonrepeatable reads, and phantoms are not possible. |
| 16 | Transactions are serializable, but higher concurrency is possible than with 8. Dirty reads are not possible. |
Typically, 8 is implemented by using locking protocols that reduce concurrency and 16 is implemented by using a non-locking protocol such as record versioning. Oracle's Read Consistency isolation level is an example of 16.
By default, the ODBC driver sets the transaction isolation level to what is set in the data source. The other SQL based drivers set it to 1.
The return number is the current value of the isolation level. A zero return indicates the file is not connected to a database.
IsolationLevel uses the ODBC isolation level standard. This may not be the same as the isolation levels documented on the target driver's native back end. For example, with Sybase's ASA, the documented isolation levels are 0, 1, 2 and 3 and they correspond to
| ODBC level | Sybase Level |
| 1 | 0 |
| 2 | 1 |
| 4 | 2 |
| 8 | 3 |
| 16 | N/A |
FAQs:
Does PROP:LOGOUTISOLATION work only on the FILE LEVEL or is it on the Database level? How does PROP:LOGOUTISOLATION deal with multiple tables on a transaction frame, when one isolation level is different from another?
Isolation levels work at a connection level. So they apply to all files opened using the same OWNER attribute.
How can you remove the ODBC cursor after a LOGOUT no matter what isolation level you choose (i.e., GET(file,0)) - does this remove a cursor?
The ODBC cursor is maintained throughout the life of the file (e.g., until PROP:Disconnect or the end of a thread for threaded files.)