Commit modes in CLI applications

In CLI applications, you can use auto-commit or manual-commit mode to handle transactions. In manual-commit mode, the transaction ends when you use SQLEndTran() to either rollback or commit the transaction. In auto-commit mode, every SQL statement is a complete transaction, which is automatically committed.

A transaction is a recoverable unit of work, or a group of SQL statements that can be treated as one atomic operation. This means that all the operations within the group are guaranteed to be completed (committed) or undone (rolled back), as if they were a single operation. When the transaction spans multiple connections, it is referred to as a distributed unit of work (DUOW).

Transactions are started implicitly with the first access to the database using SQLPrepare(), SQLExecDirect(), SQLGetTypeInfo(), or any function that returns a result set, such as catalog functions. At this point a transaction has begun, even if the call failed.

CLI supports two commit modes:

auto-commit: For a non-query statement, the commit is issued at the end of statement execution. For a query statement, the commit is issued after the cursor has been closed. The default commit mode is auto-commit (except when participating in a coordinated transaction).
manual-commit: This means that any statements executed (on the same connection) between the start of a transaction and the call to SQLEndTran() are treated as a single transaction. If CLI is in manual-commit mode, a new transaction will be implicitly started if the application is not already in a transaction and an SQL statement that can be contained within a transaction is executed.

An application can switch between manual-commit and auto-commit modes by calling SQLSetConnectAttr(). Auto-commit can be useful for query-only applications, because the commits can be chained to the SQL execution request sent to the server. Another benefit of auto-commit is improved concurrency because locks are removed as soon as possible. Applications that must perform updates to the database should turn off auto-commit as soon as the database connection has been established and should not wait until the disconnect before committing or rolling back the transaction.

The examples of how to set auto-commit on and off:

Setting auto-commit on:

    /* ... */
    
    /* set AUTOCOMMIT on */
    sqlrc = SQLSetConnectAttr( hdbc,
                               SQL_ATTR_AUTOCOMMIT,
                               (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;

    /* continue with SQL statement execution */

Setting auto-commit off:

    /* ... */
    
    /* set AUTOCOMMIT OFF */
    sqlrc = SQLSetConnectAttr( hdbc,
                               SQL_ATTR_AUTOCOMMIT,
                               (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;

    /* ... */

    /* execute the statement */ 
    /* ... */
    sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;

    /* ... */

    sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );     
    DBC_HANDLE_CHECK( hdbc, sqlrc);     

    /* ... */

When multiple connections exist to the same or different databases, each connection has its own transaction. Special care must be taken to call SQLEndTran() with the correct connection handle to ensure that only the intended connection and related transaction is affected. It is also possible to rollback or commit all the connections by specifying a valid environment handle, and a NULL connection handle on the SQLEndTran() call. Unlike distributed unit of work connections, there is no coordination between the transactions on each connection in this case.