When to call SQLEndTran()

In manual-commit mode, SQLEndTran() must be called before SQLDisconnect() is called.

An application in autocommit mode is not required to call SQLEndTran() because a commit is issued implicitly at the end of each statement execution. If distributed unit of work is involved, additional rules can apply.

Recommendation: If your application performs updates, do not wait until the application disconnects before you commit or roll back transactions.

The other extreme is to operate in autocommit mode, which is also not recommended as this adds extra processing. The application can modify the autocommit mode by starting the SQLSetConnectAttr() function.

Consider the following behaviors to decide where in the application to end a transaction:
  • If using CONNECT (type 1) with MULTICONTEXT=0, only the current connection can have an outstanding transaction. If using CONNECT (type 2), all connections participate in a single transaction.
  • If using MULTICONTEXT=1, each connection can have an outstanding transaction.
  • Various resources can be held while you have an outstanding transaction. Ending the transaction releases the resources for use by other users.
  • When a transaction is successfully committed or rolled back, it is fully recoverable from the system logs. Open transactions are not recoverable.