ANSI Mode Transactions - Advanced SQL Engine - Teradata Database

Database Introduction

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
jiv1600056352873.ditamap
dita:ditavalPath
jiv1600056352873.ditaval
dita:id
B035-1091
lifecycle
previous
Product Category
Teradata Vantageā„¢

All ANSI transactions are implicitly opened. Either of the following events opens an ANSI transaction:

  • Execution of the first SQL request in a session.
  • Execution of the first request following the close of a previous transaction.

Transactions close when the application performs a COMMIT, ROLLBACK, or ABORT request.

When the transaction contains a DDL statement, including DATABASE and SET SESSION, which are considered DDL statements in this context, the statement must be the last request in the transaction other than the transaction closing statement.

A session executing under ANSI transaction semantics allows neither the BEGIN TRANSACTION statement, the END TRANSACTION statement, nor the two-phase commit protocol. When an application submits these statements in ANSI mode, the database software generates an error.

In ANSI mode, the system rolls back the entire transaction if the current request:

  • Results in a deadlock.
  • Performs a DDL statement that aborts.
  • Executes an explicit ROLLBACK or ABORT statement.

Teradata Database accepts the ABORT and ROLLBACK statements in ANSI mode, including conditional forms of those statements. If the system detects an error for either a single or multistatement request, it only rolls back that request, and the transaction remains open, except in special circumstances.

Application-initiated, asynchronous aborts also cause full transaction rollback in ANSI mode.