A Teradata SQL transaction can be a single Teradata SQL statement, or a sequence of Teradata SQL statements, treated as a single unit of work.
- Implicit
- Explicit
- Two-phase commit (2PC)
Implicit Transactions
An implicit transaction is a request that does not include the BEGIN TRANSACTION and END TRANSACTION statements. The implicit transaction starts and completes all within the SQL request: it is self-contained.
- Single DML statement that affects one or more rows of one or more tables.
- Macro or trigger containing one or more statements.
- Request containing multiple statements separated by SEMICOLON characters. SEMICOLON characters can appear anywhere in the input line. The Parser interprets a SEMICOLON at the end of an input line as the request terminator.
DDL statements are not valid in a multistatement request and are therefore not valid in an implicit multistatement transaction.
Explicit Transactions
In Teradata session mode, an explicit transaction contains one or more statements enclosed by BEGIN TRANSACTION and END TRANSACTION statements. The first BEGIN TRANSACTION initiates a transaction and the last END TRANSACTION terminates the transaction.
When multiple statements are included in an explicit transaction, you can only specify a DDL statement if it is the last statement in the series.
2PC Rules
- A 2PC transaction contains one or more DML statements that affect multiple databases and are coordinated externally using the 2PC protocol.
- A DDL statement is not valid in a two-phase commit transaction.