Session Mode Transaction Processing Case Studies | VantageCloud Lake - Teradata Session Mode Transaction Processing Case Studies - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

This topic presents case studies concerning Teradata session mode transaction semantics. Also see ANSI Session Mode Transaction Processing Case Studies).

Failed Teradata Session Mode Transaction Example

BTEQ -- Enter your DBC/SQL request or BTEQ command:
BEGIN TRANSACTION;
*** Begin transaction accepted.
 Beginning of an explicit transaction. 
BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO employee
SELECT *
FROM customer_service.employee;
*** Insert completed. 26 rows added.
 Single request.

WRITE locks held.

 
BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FRM employee
WHERE empnum = 401;
*** Failure 3706 Syntax error; SELECT * must have a FROM clause.
 Invalid syntax is a failure.

Transaction rolled back.

All previous requests in the transaction are also rolled back.

All locks released.

 
BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM employee;
*** Query completed. No rows found.
 Single request.

Implicit transaction.

 
BTEQ -- Enter your DBC/SQL request or BTEQ command:
END TRANSACTION;
*** Failure 3510 Too many END TRANSACTION statements.
 Request to end the transaction causes a failure response because the transaction begun with the BEGIN TRANSACTION request had already rolled back.

Teradata Session Mode Requests

DELETE FROM table_1
WHERE PI_col=2;
INSERT INTO table_1
VALUES (2,3,4);
UPDATE table_1
SET col_3=4;
 These 3 statements are separate requests and implicit transactions. The implications of this are as follows:
  • The requests are performed serially in the order received.
  • Their locks are applied and released separately.
  • The success or failure of each has no effect on the success or failure of the others.
 
BEGIN TRANSACTION;
DELETE FROM table_1
WHERE PI_col=2;
INSERT INTO table_1
VALUES (2,3,4);
UPDATE table_1
SET col_3=4;
END TRANSACTION;
 These 5 statements are separate requests within a single explicit transaction. The implications of this are as follows.
  • The requests are performed serially in the order specified in the transaction.
  • Locks are held, and possibly upgraded, throughout the duration of the transactions, only being released when either an END TRANSACTION statement commits the work or a ROLLBACK statement, ABORT statement, failure, logoff, or system restart rolls back the work.
  • The success or failure of each has a direct effect on the success or failure of the others.
 
DELETE FROM table_1
WHERE PI_col=2
;INSERT INTO table_1
VALUES (2,3,4)
;UPDATE table_1
SET col_3=4;
 These 3 statements form a single multiple-statement request and implicitly a single transaction. The implications of this are as follows:
  • The most restrictive lock held by the transaction, a table-level WRITE lock, is applied to table_1.
  • The work done by the transaction is atomic: all committed or all rolled back.
 
CREATE MACRO mac_1 AS (
DELETE FROM table_1
WHERE PI_col=2;
INSERT INTO table_1
VALUES (2,3,4);
UPDATE table_1
SET col_3=4;
);
 This macro contains 3 separate requests contained within the same macro, and behave identically to a multiple-statement request that contains the same three requests in the same order. 
EXEC mac_1;
 The result of running the macro is atomic in exactly the same way its equivalent multiple-statement request is atomic. 
EXPLAIN EXEC mac_1
EXPLAIN DELETE FROM table_1
WHERE PI_col=2
;INSERT INTO table_1
VALUES (2,3,4)
;UPDATE table_1
SET col_3=4;
 The EXPLAIN reports generated for these 2 requests are identical.

Mixing DDL and DML Statements in a Multiple-Statement Request

You cannot mix DDL and DML statements within the same macro or multiple-statement request in Teradata session mode. An attempt to perform such a request is a failure.

For example, the following multiple-statement request mixes DML (2 SELECT requests) with DDL (a CREATE TABLE request):

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

The equivalent macro text causes the same failure when you try to create the macro.

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

A DDL statement within a Teradata session mode transaction must be the last action statement in the transaction. Otherwise, the transaction fails and all its work is rolled back. For example:

BEGIN TRANSACTION;
*** Begin transaction accepted.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE TABLE table_19 (
  col_1 INTEGER);
*** Table has been created.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO table_3
VALUES (1);
*** Failure 3932 Only an ET or null statement is legal after a DDL statement.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
SHOW TABLE table_19;
*** Failure 3807 Table/view/trigger/procedure ‘table_19’ does not exist.

Teradata Session Mode DELETE Performance for Different Transaction Structures

Depending on structure, a transaction that contains a DELETE request creates a Transient Journal entry for each row deleted from a table or creates only one Transient Journal entry for the entire transaction.

The following DELETE request, a single implicit transaction, does not write a Transient Journal entry for each deleted row, so its performance is good.

DELETE FROM table_1;

The following explicit transaction contains only BEGIN TRANSACTION and END TRANSACTION requests and the DELETE request. Because of its structure, the transaction writes a Transient Journal entry for each deleted row and performs poorly, especially for large tables.

BEGIN TRANSACTION;
DELETE FROM table_1;
END TRANSACTION;

The following multiple-statement request contains the same three requests as the previous transaction, but packaged as a multiple-statement request, and therefore treated as an implicit transaction. Vantage does not write a Transient Journal entry for each row deleted from the table, and its performance is identical to that of the single-statement implicit transaction version.

BEGIN TRANSACTION
;DELETE FROM table_1
;END TRANSACTION;

In the first case, the system knows what the next request is and must 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 transaction need not be rolled back.