Skip to main content

COMMIT (SQL)

Commits work performed during a transaction.

Synopsis

COMMIT [WORK]

Description

A COMMIT statement commits all work completed during the current transaction, resets the transaction level counter, and releases all locks established. This completes the transaction. Work committed cannot be rolled back.

COMMIT and COMMIT WORK are equivalent statements; both versions are supported for compatibility.

A transaction is defined as the operations that have occurred since and including the START TRANSACTION statement. A COMMIT restores the transaction level counter ($TLEVEL) to its state immediately prior to the START TRANSACTION statement that initialized the transaction. (Because InterSystems SQL does not support nested transactions, issuing additional START TRANSACTION statements within a transaction has no effect on the transaction initialization point.)

A single COMMIT causes all savepoints within the transaction to be committed.

A START TRANSACTION statement is used to explicitly begin a new transaction. However, use of START TRANSACTION is optional. If transaction processing is activated, the first database operation following a COMMIT implicitly begins a new transaction. A COMMIT statement is not meaningful if either transaction processing is not in effect, or transaction processing is in effect with automatic commits. If no transaction is in progress, a COMMIT completes successfully (SQLCODE 0), but performs no operation.

The effects of a COMMIT on queries are determined by the current isolation level. These transaction parameters can be set using either the SET TRANSACTION or START TRANSACTION command.

An SQLCODE -400 is issued if a transaction operation fails to complete successfully.

ObjectScript and SQL Transactions

ObjectScript and SQL transaction commands are fully compatible and interchangeable, with the following exception:

ObjectScript TSTART and SQL START TRANSACTION both start a transaction if no transaction is current. However, START TRANSACTION does not support nested transactions. Therefore, if you need (or may need) nested transactions, it is preferable to start the transaction with TSTART. If you need compatibility with the SQL standard, use START TRANSACTION.

ObjectScript transaction processing provides limited support for nested transactions. SQL transaction processing supplies support for savepoints within transactions.

If a transaction involves SQL update statements, the transaction should be started by the SQL START TRANSACTION statement and committed with the SQL COMMIT statement. Methods that use TSTART/TCOMMIT nesting can be included in the transaction, as long as they don't initiate the transaction. Methods and stored procedures should not normally use SQL transaction control statements, unless, by design, they are the main controller of the transaction. Stored procedures should not normally use SQL transaction control statements, because these stored procedures are normally called from ODBC/JDBC, which has its own model of transaction control.

Examples

The following Embedded SQL example demonstrates how a COMMIT restores the transaction level counter ($TLEVEL) to the level immediately prior to the START TRANSACTION, regardless of how many SAVEPOINTS have been established within the transaction. Note that the second START TRANSACTION in this program is a no-op which has no effect on $TLEVEL:

  &sql(SET TRANSACTION %COMMITMODE EXPLICIT)
    WRITE !,"Set transaction mode, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION)
    WRITE !,"Start transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT a)
    WRITE !,"Set Savepoint a, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT b)
    WRITE !,"Set Savepoint b, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION) /* Performs no operation */
    WRITE !,"Start transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT c)
    WRITE !,"Set Savepoint c, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(COMMIT)
    WRITE !,"Commit transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL

The following Embedded SQL example demonstrates that the first COMMIT statement commits the entire transaction and that extra COMMIT statements have no effect and do not result in an error:

  &sql(SET TRANSACTION %COMMITMODE EXPLICIT)
    WRITE !,"Set transaction mode, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION)
    WRITE !,"Start transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT a)
    WRITE !,"Set Savepoint a, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(COMMIT)
    WRITE !,"Commit transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
 &sql(COMMIT)   /* Performs no operation */
    WRITE !,"Commit again, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
 &sql(COMMIT)   /* Performs no operation */
    WRITE !,"Commit again, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL

See Also

FeedbackOpens in a new tab