15.00 - Coding Multistatement Requests - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Coding Multistatement Requests

Multistatement requests are application-independent, but their behavior can be different depending on whether they are specified as an implicit transaction or as part of an explicit transaction and whether they are submitted in Teradata or ANSI/ISO session mode.

Teradata Database treats multistatement requests as single implicit transactions in Teradata session mode when no open BEGIN TRANSACTION statement precedes them.

With the exception of multistatement INSERT requests, Teradata Database treats multistatement requests as single recovery units only if they are executed either as implicit transactions in Teradata session mode, or in ANSI/ISO session mode. Depending on any errors generated by the statements in the transaction, either the entire transaction or only the erring request is rolled back. For example, in Teradata session mode within an explicit transaction, Teradata Database rolls the entire transaction back to the BEGIN TRANSACTION statement, so in this case, the complete transaction is the recovery unit.

If several UPDATE requests are specified within one multistatement request inside an explicit transaction in Teradata session mode, and one of them fails, then all are rolled back. The fewer UPDATE statements in the multistatement request, the lower the impact of the rollback. However, the more statements included in the request, the higher the degree of parallelism among them.

The rollback issue for UPDATE requests is not true for multistatement INSERT requests, where the statement independence feature can frequently enable multistatement INSERT requests to roll back only the statements that fail within an explicit transaction or multistatement request and not the entire transaction or request.

Statement independence supports the following multistatement INSERT data error types:

  • Column‑level CHECK constraint violations
  • Data translation errors
  • Duplicate row errors for SET tables
  • Primary index uniqueness violations
  • Referential integrity violations
  • Secondary index uniqueness violations
  • Statement independence is not enabled for multistatement INSERT requests into tables defined with the following options:

  • Triggers
  • Hash indexes
  • Join indexes
  • See “INSERT/INSERT … SELECT” in SQL Data Manipulation Language for more information about statement independence. Note that various client data loading utilities also support statement independence. Consult the appropriate Teradata Tools and Utilities documentation for information about which load utilities and APIs support statement independence and what level of support they offer for the feature.