BEGIN TRANSACTION - Teradata Database

Purpose

Defines the beginning of an explicit logical transaction in Teradata session mode.

An explicit Teradata session mode transaction must always be terminated with an END TRANSACTION statement. You must always specify both BEGIN TRANSACTION and END TRANSACTION statements to define the limits of an explicit transaction in Teradata session mode.

Syntax

ANSI Compliance

BEGIN TRANSACTION is a Teradata extension to the ANSI SQL:2011 standard.

The statement is valid only in Teradata session mode. If you submit a BEGIN TRANSACTION statement in an ANSI mode session, the system returns an error.

For ANSI session mode transaction control statements, see “COMMIT” and “ROLLBACK”.

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

BEGIN
BEGIN WORK

Required Privileges

None.

Rules for Embedded SQL

The following rules apply to the use of BEGIN TRANSACTION in embedded SQL:

BEGIN TRANSACTION is valid only when you specify the TRANSACT(BTET) or -tr(BTET) options to the preprocessor. Otherwise, an error is returned and the precompilation fails.
BEGIN TRANSACTION cannot be performed as a dynamic SQL statement.

Explicit Transactions

An explicit, or user-generated, transaction is a single set of BEGIN TRANSACTION/END TRANSACTION statements surrounding one or more additional SQL statements.

A BTEQ .LOGOFF command following a BEGIN TRANSACTION statement and one or more additional statements also forms an explicit transaction as well as terminating the current session.

Note that a .LOGOFF command aborts the transaction and rolls back the processing results of all the statements that occurred between BEGIN TRANSACTION and .LOGOFF.

All other Teradata session mode transactions are implicit.

Implicit Transactions

An implicit, or system-generated, transaction, is typically one of the following:

A macro.
A single-statement request.
A multistatement request that is not part of an explicit transaction.

Rules for Transactions Containing DDL Statements

A transaction can be any solitary SQL statement, including a DDL statement.

Note that when a request contains multiple statements, a DDL statement is allowed only if the following things are true:

The transaction is explicit (bracketed by BEGIN TRANSACTION and END TRANSACTION statements).
The DDL statement is the last statement in the transaction (immediately followed by the END TRANSACTION statement).

Teradata Database Transaction Handling Protocol

Teradata Database manages transactions to maintain valid, consistent, and available data for all users. A transaction may consist of one or more requests which are sent one at time and processed as each is received. Locks are applied as needed for each request. Various locks are placed on its associated database objects according to the types of statements contained in the request.

If a statement does not complete successfully or causes processing to time out for some reason, such as a statement error, deadlock, privilege violation, table constraint violation, or premature logoff, the system performs the following transaction management steps.

The entire transaction is aborted. Abort processing performs the following actions.
1. Performs an implicit END TRANSACTION.
2. Backs out any changes made to the database by the transaction.
3. Releases any usage locks associated with the transaction.
4. Discards any partially accumulated results (spools) generated by the transaction.
A failure or time-out response is returned to the requestor.

Most statement errors resulting from multistatement INSERT statements abort the entire transaction.

Statement independence is a method of handling multistatement INSERT errors by only rolling back the individual statements that failed, not the entire transaction. See Multistatement and Iterated INSERT Requests.

The client software you are using must support statement independence to prevent all multistatement requests and iterated INSERT statements in a transaction or multistatement request from being rolled back. Refer to the documentation for your client software for information on support for statement independence.

Specifying SQL Request Modifiers With Explicit Transactions

When the Parser receives a BEGIN TRANSACTION statement, it immediately looks for an SQL statement keyword in the SQL text that follows. Keep this in mind when determining the placement of request modifiers such as EXPLAIN, LOCKING, NONTEMPORAL, and USING.

For example, if the first statement in an explicit transaction is associated with a USING request modifier, that USING request modifier must precede the BEGIN TRANSACTION statement.

Nested BEGIN TRANSACTION/END TRANSACTION Pairs

When BEGIN TRANSACTION/ET pairs are nested, Teradata Database checks to ensure that each BEGIN TRANSACTION statement has a matching END TRANSACTION statement.

The outermost BEGIN TRANSACTION/END TRANSACTION pair defines the explicit transaction; the inner BEGIN TRANSACTION/END TRANSACTION pairs have no effect on the transaction because Teradata Database does not support transaction nesting.

Any embedded multistatement requests and macro executions are considered part of the outermost BEGIN TRANSACTION/END TRANSACTION explicit transaction and are not considered to be implicit transactions in this context.

In a multistatement request, there can be no more than one BEGIN TRANSACTION statement, and if you do specify a BEGIN TRANSACTION statement, it must be the first statement in a request.

For example, the following series of requests is treated as one explicit transaction.

     BEGIN TRANSACTION;
      SELECT …;
      UPDATE …
      EXEC a(3,4);
        BEGIN TRANSACTION;
         UPDATE …;
         INSERT …
         ;INSERT …;
        END TRANSACTION;
      INSERT …;
     END TRANSACTION;

If an error occurs in the middle of a nested BEGIN TRANSACTION/END TRANSACTION, everything rolls back to the initial BEGIN TRANSACTION.