Transaction Autocommit Behavior

Autocommit configures whether TimesTen issues an implicit commit after DML or DDL statements.

Some database APIs (such as ODBC and JDBC) support autocommit, which is enabled by default when using those APIs. Other APIs (such as OCI) do not provide an autocommit feature.

When autocommit is on, the following behavior occurs:

  • An implicit commit is issued immediately after a statement runs successfully.

  • An implicit rollback is issued immediately after a statement fails, such as a primary key violation.

  • If the statement generates a result set, the automatic commit is not issued until that result set and any other open result set in the transaction have been explicitly closed. Any statements processed while a result set is open is not committed until all result sets have been closed.

    Fetching all rows of a result set does not automatically close the result set. After the result set has been processed, the result set must be explicitly closed if using the read committed isolation level or the transaction must be explicitly committed or rolled back if using serializable isolation level.

    Note:

    Even with durable commits and autocommit enabled, you could lose work if there is a failure or the application exits without closing result sets.

  • If you are using ODBC or JDBC batch operations to INSERT, UPDATE or DELETE several rows in one call when autocommit is on, a commit occurs after the entire batch operation has completed. If there is an error during the batch operation, those rows that have been successfully modified are committed within this transaction. If an error occurs due to a problem on a particular row, only the successfully modified rows preceding the row with the error are committed in this transaction. The pirow parameter to the ODBC SQLParamOptions function contains the number of the rows in the batch that had a problem.

Commits can be costly for performance and intrusive if they are implicitly run after every statement. TimesTen recommends you disable autocommit so that all commits are intentional. Disabling autocommit provides control over transactional boundaries, enables multiple statements to run within a single transaction, and improves performance, since there is no implicit commit after every statement.

If autocommit is disabled, transactions must be explicitly completed with a commit or rollback after any of the following:

  • Completing all the work that was to be done in the transaction.

  • Issuing a transaction-consistent (blocking) checkpoint request.

  • Updating column and table statistics to be used by the query optimizer.

  • Calling a TimesTen built-in procedure that does not generate a result set in order for the new setting specified in the procedure to take effect, such as the ttLockWait procedure.

You must establish a connection to a database before changing the autocommit setting. To disable autocommit, perform one of the following:

  • In ODBC-based applications, run SQLSetConnectOption function with SQL_AUTOCOMMIT_OFF.

  • In JDBC applications, Connection.setAutoCommit(false) method.

  • When running ttIsql, issue the autocommit 0 command.