Transactions in ODBC
Transactions in ODBC are managed at the connection level. When an application completes a transaction, it commits or rolls back all work done through all statement handles on that connection. To commit or roll back a transaction, applications should call SQLEndTran rather than submitting a COMMIT or ROLLBACK statement.
An application calls SQLSetConnectAttr to switch between the two ODBC modes of managing transactions:
- Autocommit mode
Each individual statement is automatically committed when it completes successfully. When running in autocommit mode no other transaction management functions are needed.
- Manual-commit mode
All executed statements are included in the same transaction until it is specifically terminated by calling SQLEndTran.
Autocommit mode is the default transaction mode for ODBC. When a connection is made, it is in autocommit mode until SQLSetConnectAttr is called to switch to manual-commit mode by setting autocommit mode off. When an application turns autocommit off, the next statement sent to the database starts a transaction. The transaction then remains in effect until the application calls SQLEndTran with either the SQL_COMMIT or SQL_ROLLBACK options. The command sent to the database after SQLEndTran starts the next transaction.
If an application switches from manual-commit to autocommit mode, the driver commits any transactions currently open on the connection.
ODBC applications should not use Transact-SQL transaction statements (such as BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) because this can result in indeterminate behavior in the driver. An ODBC application should either:
- Run in autocommit mode and not use any transaction management functions or statements.
-or-
- Run in manual-commit mode and use the ODBC SQLEndTran function to either commit or roll back transactions.