CONNX enables the configuration of the initial isolation level for an ODBC connection via the CONNX import utility. The isolation level can be changed programmatically after connecting to the data source via the ODBC 2.x/3.x SQLSetConnectOption/SQLSetConnectAttr APIs. These API functions are called automatically by the higher-level wrapper functions implemented by ADO, RDO, and the MS Access/Visual Basic Jet Dynaset Engine.
Selecting the correct initial isolation level for the CDD depends upon the requirements of the ODBC applications which connect to it. As isolation level increases, concurrent access to shared data decreases, and vice versa. Exclusively read-only ODBC applications, such as report writers, require the lowest isolation level and the highest level of concurrent access.
Online Transaction Processing (OLTP) applications require high isolation levels and restricted concurrent data access. The ODBC 3.x specification defines transaction isolation levels by the presence/absence of key phenomena.
There are three types of key phenomena: dirty reads, nonrepeatable reads, and phantoms:
A dirty read occurs when a transaction reads data that has not been committed.
A nonrepeatable read occurs when a transaction reads the same row twice, but gets different data for each row.
Phantoms are rows that match search criteria, but are not initially seen, so that different rows are generated for the same criteria if a query is re-executed during the course of a transaction.
The following table defines the four ODBC transaction isolation levels, as defined by SQL-92. An "X" marks each possible phenomenon.
ODBC Transaction Isolation Levels
|
ODBC Transaction Isolation Level |
Dirty reads |
Nonrepeatable reads |
Phantoms |
|
Read Uncommitted |
X |
X |
X |
|
Read Committed |
|
X |
X |
|
Repeatable Read |
|
|
X |
|
Serializable |
|
|
|
For more information on ODBC isolation levels, refer to the Microsoft ODBC 3.0 Programmer's Reference, Volume 1, and SDK Guide, ISBN 1-57231-516-4, published in 1997 by Microsoft Press.
CONNX implements ODBC transaction isolation levels by mapping them to the analogous DB2 and DRDA isolation levels. The No Commit isolation level is implemented only on DB2/400 targets. Note that the No Commit isolation level is the default for DB2/400 CDD/data sources.
The following table defines the three types of transaction isolation levels:
Three Types of Transaction Isolation Levels
|
Transaction Isolation Level |
|
|
|
ODBC |
DB2 |
DRDA |
|
Read Uncommitted |
Uncommitted Read (DB2 UDB and mainframe targets [OS/390 and MVS]) |
CHG = Change |
|
Read Committed |
Cursor Stability |
CS = Cursor Stability |
|
Repeatable Read |
Read Stability |
ALL = All |
|
Serializable |
Repeatable Read |
RR = Repeatable Read |
|
Read Uncommitted |
No Commit (DB2/400 targets) |
NC = No Commit |
Note: A simultaneous ODBC API trace will expedite problem diagnosis. You can start the ODBC API trace via the Windows ODBC Administrator control panel applet.
