17.10 - Teradata Session Mode Transaction Processing Case Studies - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

This topic presents several 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. They are also implicit transactions. The implications of this are as follows:
  • The requests are performed serially in the order they are 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 they are 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 multistatement request. They are also 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: either it is all committed or it is 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. Because they are contained within the same macro, they behave identically to a multistatement request that contains the same three requests in the same order.
EXEC mac_1;
The result of executing the macro is atomic in exactly the same way its equivalent multistatement request above 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 Within a Multistatement Request

You cannot mix DDL and DML statements within the same macro or multistatement request in Teradata session mode. An attempt to perform such a request results in a failure response.

For example, the following multistatement request fails because it 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 results in the same failure at the time that you attempt 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

If you include a DDL statement within a Teradata session mode transaction, it must be the last action statement in the transaction. If it is not, 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 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 request is a single implicit transaction. It does not write a Transient Journal entry for each deleted row, so its performance is quite good.

DELETE FROM table_1;

The following explicit transaction contains only BEGIN TRANSACTION and END TRANSACTION requests in addition to the DELETE request. Because of the way it is structured, 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 multistatement request contains the same three requests as the previous transaction, but because they are packaged as a multistatement request, they are 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 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.