15.10 - ANSI Session Mode Transaction Processing Case Studies - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

This topic presents several case studies concerning ANSI session mode transaction semantics. Also see “Teradata Session Mode Transaction Processing Case Studies” on page 663).

The following transaction is an example of a successful ANSI mode transaction.

 

When ANSI transaction semantics are in effect, SQL Error responses do not cause a rollback, while failure responses do.

The following example shows how error responses do not roll back a transaction.

 

Like Teradata mode transactions, there can only be 1 DDL request in a transaction, and it must be the last sequential request other than a COMMIT request.

Unlike Teradata mode transactions, an ANSI mode transaction does not roll back if you attempt to submit another DDL request before committing the transaction. Instead, it continues to respond with Error responses until the requestor either issues a COMMIT request or a ROLLBACK/ABORT request.

 

Like Teradata mode transactions, you cannot mix DDL and DML statements with a single request in ANSI session mode.

The following multistatement request and macro, which have the identical semantics, both fail. An ANSI mode Failure response rolls back the transaction.

    SELECT *
    FROM table_1
    ;SELECT *
    FROM table_2
    ;CREATE TABLE table_5 (
       col_1 INTEGER);
 
    *** Failure 3576 Data definition not valid unless solitary.
              Statement# 1, Info =0
 
    CREATE MACRO mac_1 AS (
      SELECT *
      FROM table_1;
      SELECT *
      FROM table_2;
      CREATE TABLE table_5 (
        col_1 INTEGER););
 
    *** Failure 3576 Data definition not valid unless solitary.
              Statement# 1, Info =0

Depending on how you structure a transaction that contains a DELETE request, it can either create a Transient Journal entry for each row deleted from a table or create only one Transient Journal entry for the entire transaction.

The following DELETE is a single request followed by another request to commit the transaction. It writes a Transient Journal entry for each deleted row, so its performance is poor, particularly for large tables.

     DELETE FROM table_1;
     COMMIT;

The following multistatement request contains the same two statements as the transaction above, but because they are packaged as a multistatement request, both statements are seen by the system together. The system does not write a Transient Journal entry for each row deleted from the table, but instead writes a single Transient Journal entry. The multistatement request’s performance is very good.

     DELETE FROM table_1
     ;COMMIT;

In the first case, the system knows what the next request is and that it needs to be prepared to roll back the transaction. In the second case, the system can see that the delete is to be committed, and therefore the system knows that it does not need to roll back the transaction.