What Are The Main Characteristics Of Transactions in Teradata?
What is a transaction?
Transactions define atomic work. The rollback scope of an individual failure depends on session mode and error type; a failed statement does not universally imply the same rollback response in ANSI and BTET.
What is the difference between Teradata Mode and ANSI Mode?
Transactions in Teradata Mode can be started and ended explicitly. Transactions in ANSI mode are always implicit.
Transactions in ANSI mode start with the first SQL statement executed in a session or the first request executed after a previous transaction.
ANSI mode transactions end when a COMMIT statement is executed, or a ROLLBACK or ABORT occurs.
As you can see, no explicit BEGIN TRANSACTION is executed in ANSI mode, and each transaction ends (if there is no error) with a COMMIT statement.
In Teradata Mode, a distinction is made between explicit transactions and implicit transactions.
Explicit transactions are limited by BEGIN TRANSACTION and END TRANSACTION:
BEGIN TRANSACTION;
DELETE FROM Customer;
UPDATE Sale SET Price = Price + 1;
END TRANSACTION;
If no explicit transaction is defined in Teradata mode, each request is an implicit transaction.
How can I see what belongs to a transaction?
Transactions can be seen in the Execution Plan:
EXPLAIN SELECT * FROM Customer WHERE CustomerId= 1;
3) We do an all-AMPs RETRIEVE step from DWHPRO.Customer by way of an all-rows scan with a condition of
(” DWHPRO.Customer. CustomerId= 1″) into Spool 1 (group_amps), built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be two rows (1460 bytes). The estimated time for this step is 0.22 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
Is there another method to perform explicit transactions in Teradata Mode?
A multistatement request outside BT/ET is one implicit transaction in BTET. Explicit transactions use BEGIN TRANSACTION/END TRANSACTION. BTEQ semicolon layout controls request submission and is not universal SQL-client behavior.
How does Teradata enable an aborted transaction to be completely reversed?
Teradata uses locks on tables or Rowhash. Together with the Transient Journal, this ensures that transactions can be reversed.
Each changed row receives a copy of the original row in the Transient Journal. This makes a rollback possible.
Can I, as a user, cancel a transaction via SQL?
LOCKING TABLE Customer FOR WRITE NOWAIT
UPDATE Customer SET OpenDate = OpenDate – 1;
If there is already an incompatible lock on the table, the transaction is aborted:
*** Failure 7423 Object already locked and NOWAIT.
Transaction Aborted. Statement# 1, Info =0
The Teradata Mode For Transactions
Various techniques exist for composing SQL statements and applying transaction handling. Each approach has unique benefits, drawbacks, and characteristics worth considering.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookLet’s examine two sequential statements as an illustration:
DELETE FROM TestTable;
INSERT INTO TestTable SELECT * FROM TestTable2;
Using Teradata Mode With Explicit Transaction
These statements can be executed as explicit transactions.
BEGIN TRANSACTION;
DELETE FROM TestTable;
INSERT INTO TestTable SELECT * FROM TestTable2;
END TRANSACTION;
Using Multi-Statement Request As Explicit Transaction
A Multi-Statement Request can achieve the same result.
DELETE FROM TestTable
;INSERT INTO TestTable SELECT * FROM TestTable2;

If an error occurs, the entire transaction is reversed in both cases.
Executing The Statements Independently
DELETE FROM TestTable;
INSERT INTO TestTable SELECT * FROM TestTable2;

Note that executing the statements independently differs from executing them as a multi-statement request solely due to the position of the semicolon.
The outcome remains constant without errors, regardless of the chosen solution.
What happens if an error occurs while executing the INSERT statement, specifically the second statement?
In BTET, BT/ET explicitly brackets a transaction. A multistatement request outside BT/ET is an implicit transaction, not another explicit transaction form. BTEQ semicolon placement controls submission boundaries; other clients have their own request APIs.
Executing the DELETE and INSERT statements separately results in two implicit transactions. While the DELETE statement is committed successfully, the INSERT statement fails, leaving the “TestTable” empty.
A committed empty table between independent DELETE and INSERT requests is not a dirty read. It is an application-visible intermediate committed state. Dirty reading refers to uncommitted changes.
Transaction Types And Restartability
Always consider the risk of inconsistent data when executing your ETL process.
Your ETL process job must be designed to support restart-ability by choosing the appropriate transaction type and implementing effective error handling.
The BTET fixture confirmed that a duplicate-key error 2801 rolled back the DELETE inside BT/ET, preserving (1,99). With separate implicit requests, DELETE committed and the failed INSERT left zero rows. ANSI rollback behavior and a multistatement-request variant were not exercised; error scope depends on mode and error class.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
Same for the INSERT/SELECT, when it’s a single request transaction it will be a FastPath Insert/Select avoiding Transient Journal, too. Thus both DELETE & INS/SEL will be much faster.