COMMIT Statement | SQL Statements | VantageCloud Lake - COMMIT - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
This statement is only supported on the Block File System on the primary cluster. It is not available for the Object File System.

Terminates the current ANSI session mode SQL transaction, commits all changes made within it, and drops the Transient Journal for the transaction.

See the following sections:

ANSI Compliance

COMMIT is ANSI SQL:2011-compliant with an extension.

COMMIT is valid only in ANSI session mode. If used in Teradata session mode, COMMIT returns a failure and ends the transaction.

Other SQL dialects support similar non-ANSI standard statements with names such as the following:
  • COMMIT TRANSACTION
  • COMMIT WORK

Required Privileges

None.

How ANSI Transactions Are Defined and Terminated

In ANSI session mode, the first SQL request in a session initiates a transaction. The transaction is terminated by sending either a COMMIT or a ROLLBACK/ABORT request. Request failures do not cause a rollback of the transaction, only of the request that causes them.

COMMIT Is Explicit

There are no implicit transactions in ANSI session mode. More accurately, each ANSI transaction is initiated implicitly, but completed explicitly. The COMMIT must be explicitly stated and be the last request of a transaction in order for the transaction to terminate successfully.

In ANSI session mode, you must issue a COMMIT (or ABORT/ROLLBACK) even when the only request in a transaction is a SELECT or SELECT AND CONSUME.

In a SELECT request, you issue a COMMIT or an ABORT/ROLLBACK.

In the case of a SELECT AND CONSUME, there is a difference in the outcomes between issuing a COMMIT or ABORT/ROLLBACK because an ABORT or ROLLBACK request reinstates the subject queue table of the request to its former status, containing the rows that were pseudo-consumed by the aborted SELECT AND CONSUME request.

Rules for Embedded SQL

The following rules apply to the COMMIT statement within an embedded SQL application:
  • COMMIT cannot be performed as a dynamic SQL statement.
  • COMMIT discards dynamic SQL statements prepared within the current transaction.
  • COMMIT closes open cursors.
  • COMMIT is valid only when you specify the TRANSACT(COMMIT), -tr(COMMIT), TRANSACT(ANSI), or -tr(ANSI) options to the preprocessor.

    Its use causes an error if the program is precompiled with the TRANSACT(BTET), -tr(BTET), or the TRANSACT(2PC) preprocessor options.

  • If you specify the RELEASE option, then the application program connection to the database (whether explicit or implicit) is terminated.

    If additional SQL requests (other than CONNECT or LOGON) are then performed, an implicit connection is attempted.

  • RELEASE is not valid when you specify the TRANSACT(ANSI) or -tr(ANSI) options to the preprocessor.

Relation to ABORT and ROLLBACK

The ABORT and ROLLBACK statements also cause the current transaction to be terminated, but with rollback rather than commit. See ABORT and ROLLBACK.

COMMIT and BTEQ

If you use COMMIT in a BTEQ script with either the .SESSION or the .REPEAT command, you must send the COMMIT request with the repeated SQL request as one request.

If you send the repeated request without the COMMIT, one request is blocked by other sessions and the job stops because of a deadlock.

The following dummy example shows how to do this:

.SESSION TRANS ANSI
.SESSIONS 10
.LOGON TDPID/USER,PASSWD

.IMPORT DATA FILE = data_file_name
.REPEAT I

USING i(INTEGER), j(INTEGER)
INSERT INTO table_name (col1, col2)
VALUES (:1, :j); COMMIT;

.QUIT
Related information