What Are The Main Characteristics Of Transactions in Teradata?
What is a transaction?
Transactions ensure data integrity.
All requests within a transaction must be successful. Otherwise, changes to database objects are not applied. It is an “all or nothing” principle.
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?
Yes, by using the multi-statement requests. All requests in a multi-statement request represent one transaction.
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.
Let’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?
Suppose we execute both statements within an explicit transaction (either by explicitly defining it or using a multi-statement request). In that case, the table “TestTable” content will remain unchanged from its state before the transaction. Neither the DELETE nor the INSERT statement will alter the target table.
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.
Requests using “TestTable” after the DELETE statement may result in a dirty read or an inconsistent table.
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.
Related Services
🏗️ Planning a Data Platform Migration?
Architecture-first approach: we design before a single line of code is written. Zero data loss across every migration delivered.
Our Migration Services →
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.