There are four different levels of transaction support in the ODBC and also the possibility not to support transactions. To see the level of the transaction support, use the IDataSource.TransactionSupport property. It returns one of following values:
| Return Value | Meaning |
| None | Transactions are not supported. |
| DML | Transactions can contain only DML statements (select, insert, update & delete). Any DDL statement causes an error. |
| DDLCommit | Same as DML, but any DDL statement causes the transaction to be committed. |
| DDLIgnore | Same as DML, but all DDL statements are ignored. |
| All | Transactions can contain DDL and DML statements in any order. |
Every transaction is somehow isolated from the other transactions. The IDataSource.TransactionIsolation property controls the isolation. After connection to the data source, the default isolation is set. The level can be changed only when there is no active transaction, but not all drivers support changing of isolation level. Thus if you don't have any active transaction and the change of the isolation level invokes an exception, then the ODBC driver doesn't support the changing of the isolation level. Some drivers can require calling the IResultSetStatement.Cancel method before a change of isolation. The level can be one following:
| Isolation Level | Meaning |
| Read uncommitted | The transaction is not isolated; thus it can read uncommitted data. This is typically for read-only applications. |
| Read committed | The transaction waits until all rows are not locked for writing by other transactions. The transaction can read data only when they are not locked for writing by any other transaction. Transaction keeps read lock until it read all necessary data and write locks until it is committed or rolled back. |
| Repeatable read | The transaction waits until all rows are not locked for writing by other transactions. The transaction holds read locks on all rows it returns to the application and all writes locks until it is committed or rolled back |
| Serializable | The transaction waits until all rows are not locked for writing by other transactions. The transaction keeps all read and write locks on all rows it affects until it is committed or rolled back. |
In particular, transaction isolation levels are defined by the presence or absence of the following phenomena:
| Phenomena | Meaning |
| Dirty reads | A dirty read occurs when a transaction reads data that has not been committed yet. |
| Nonrepeatable reads | A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. |
| Phantoms | A phantom is a row that matches the search criteria but is not initially seen. |
The following table describes the occurrence of each phenomenon (X marked):
| Isolation Level | Dirty Reads | Nonrepeatable Reads | Phantoms |
| Read uncommitted | X | X | X |
| Read committed | - | X | X |
| Repeatable read | - | - | X |
| Serializable | - | - | - |
There is the maximum of one transaction per connection in ODBC. Some drivers are even limited to the one transaction for all connections to one data source. Check the IDataSource.MultipleTXs to get this information about the connected data source.
The connection can be either in auto-commit or manual-commit mode. In the auto-commit one each statement is treated as a single transaction and it is always committed as soon as it is completed, so that it can not be rolled back. In the manual mode, the transaction starts with the first used SQL statement and it is ended as it is committed or rolled back. This can be done with IDataSource's methods Commit and Rollback.
Using the IDataSource.AutoCommit property can change the commit mode. Switching to the auto-commit mode from the manual one causes all entered statements to be committed.
After the transaction is committed or rolled back or committed mode is changed some ODBC drivers may destroy all entered SQL statements; thus they need to be entered once again. To examine the behavior of currently used driver, call the IDataSource.TXKillStatement method to determine if you need to re-enter statements.
Using of commit and rollback SQL statements may lead to undefined state, as this is driver-specific. Using of Commit and Rollback methods guarantees the compatibility for all ODBC drivers, but however it wouldn't able to take all advantages. Using driver-specific SQL statements to control transactions may give you more capabilities, but it is driver dependent.