COMMIT - Teradata Database - Teradata Vantage NewSQL Engine

Purpose

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

Required Privileges

None.

Syntax

Syntax Elements

WORK

If the SQL Flagger is enabled, the absence of WORK causes the request to be flagged.

For information about the SQL Flagger, see Teradata Vantage™ SQL Fundamentals, B035-1141.

RELEASE

The connection between the client application program and the Teradata Database, whether implicit or explicit, is to be terminated.

This option applies to embedded SQL only and is not ANSI-compliant.

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, it returns a failure and aborts the transaction.

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 always completed explicitly. The COMMIT must always 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 the case of a SELECT request, it makes no difference whether 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

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 along with the repeated SQL request as one request.

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

The following dummy example illustrates how this should be done:

     .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

Example: INSERT Request

The INSERT request in the following example opens the transaction. COMMIT closes the transaction.

     INSERT INTO employee (name, empno)
     VALUES ('Sinclair P', 101)
     WHERE dept = '400';
     COMMIT;

Example: UPDATE Followed By COMMIT

The following UPDATE initiates the transaction and COMMIT WORK terminates it:

     UPDATE parts SET part_num = 20555
     WHERE location = 'seoul';
     COMMIT WORK;