Understanding transactions and autocommit

Autocommit is available for ODBC-based drivers, including SQL Server. When autocommit on, all SQL operations are committed as soon as they are executed. When autocommit is off, all transactions remain open until they are committed with %SSC_COMMIT or rolled back with %SSC_ROLLBACK.

For SQL Server, autocommit is off by default. For other ODBC connections, autocommit is on by default. To turn autocommit on or off, use %SSC_CMD with the SSQL_ODBC_AUTOCOMMIT option (see %SSC_CMD).

Note the following:

To control transaction blocks, use the SQL Connection functions %SSC_COMMIT and %SSC_ROLLBACK, as in the following:

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 end transactions explicitly. 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: