Understanding transactions and autocommit

When a database engine is configured with autocommit on, all SQL operations are committed as soon as they are executed. If autocommit is off, all transactions remain open until they are committed with %SSC_COMMIT or rolled back with %SSC_ROLLBACK. The default autocommit setting is database‑dependent. To turn autocommit on or off, use %SSC_CMD with the SSQL_ODBC_AUTOCOMMIT option (see %SSC_CMD).

For better performance and transaction control, we recommend that you turn autocommit off and use the SQL Connection functions %SSC_COMMIT and %SSC_ROLLBACK to control transaction blocks, as in the following typical process:

begin transaction (%SSC_COMMIT)
update order data
if error on update
  rollback (%SSC_ROLLBACK)
else
  update customer
  if error on update customer
    rollback (%SSC_ROLLBACK)
  else
    commit (%SSC_COMMIT)
  endif
endif

If autocommit is off, %SSC_COMMIT or %SSC_ROLLBACK must be used after data access (DML) operations, and you should call %SSC_COMMIT before invoking a DML operation (this includes insert, update, delete, and select commands). Note, however, that the database determines if %SSC_COMMIT must be called before the database is actually modified.

Important

If a database engine employs a cursor caching mechanism, it is critical to explicitly end transactions. If you do not, the cache will quickly exhaust system resources.

If you are accessing an Oracle database, Oracle recommends that you explicitly end every transaction in your application with a COMMIT or ROLLBACK statement. This includes the final transaction, the one before you disconnect. If the application terminates abnormally and you have not done this, the last uncommitted transaction is automatically rolled back.

Row locking and transactions

While write transactions or read‑with‑lock transactions are in process, affected data rows are locked by the database engine. Depending on the type of lock, adjacent rows may be locked as well (for example, in page‑level locking). A lock persists until the database engine commits or rolls back the data, thereby closing the transaction and releasing any affected rows. However, note the following: