Edit

Performing Transactions in ODBC

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Important

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server going forward. For SQLNCLI that ships as a component of SQL Server Database Engine (versions 2012 through 2019), see this Support Lifecycle exception.

Transactions in ODBC are managed at the connection level. When an application completes a transaction, it commits or rolls back all work completed through all statement handles on that connection. To commit or roll back a transaction, applications should call SQLEndTran instead of submitting a COMMIT or ROLLBACK statement.

An application calls SQLSetConnectAttr to switch between the two ODBC modes of managing transactions:

  • Autocommit mode

    Each statement is automatically committed when it is completed successfully. When you run in autocommit mode, no other transaction management functions are required.

  • Manual-commit mode

    All executed statements are included in the same transaction until it is specifically stopped 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, or ROLLBACK TRANSACTION because this can cause indeterminate behavior in the driver. An ODBC application should 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.

See Also

Performing Transactions (ODBC)