1. Operations Guide
  2. Transaction Management
  3. Transaction Implicit Commit Behavior

Transaction Implicit Commit Behavior

You can configure whether the application enables implicit commit behavior or requires explicit commit behavior for DML or DDL statements.

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.

TimesTen DDL Commit Behavior

The TimesTen database issues an implicit COMMIT before and after any DDL statement. A durable commit is performed after the processing of each DDL statement. This behavior is the same as the Oracle database.

DDL statements include the following:

  • CREATE, ALTER and DROP statements for any database object, including tables, views, users, procedures and indexes.

  • TRUNCATE

  • GRANT and REVOKE

There are certain things to keep into consideration:

  • DDL changes cannot be rolled back.

  • DDL statements delete records from global temporary tables unless the tables were created with the ON COMMIT PRESERVE ROWS clause.

  • Tables created with the CREATE TABLE . . . AS SELECT statement are visible immediately.

  • TRUNCATE statements are committed automatically. However, the truncate of the parent and child tables must be truncated in separate transactions, with the child table truncated first. You cannot truncate a parent table unless the child table is empty.