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:
|
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.
|
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:
|
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. Teradata Database 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.