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
There are several ways of writing SQL statements and using transaction handling. Each method has its advantages, disadvantages, and behavior, which must be considered.
As an example, let’s look at two statements that have to be executed in sequence:
DELETE FROM TestTable; INSERT INTO TestTable SELECT * FROM TestTable2;
Using Teradata Mode With Explicit Transaction
The two statements listed above can, for example, 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
The same result can be achieved with a Multi-Statement Request:
DELETE FROM TestTable ;INSERT INTO TestTable SELECT * FROM TestTable2;
In both cases, the entire transaction is reversed if an error occurs.
Executing The Statements independently
DELETE FROM TestTable; INSERT INTO TestTable SELECT * FROM TestTable2;
Note that the independent execution of the two statements differs from the multi-statement request only by the semicolon position!
Important information
Whatever solution you choose, the result is the same if no error occurs.
But what happens if there is an error during executing the INSERT statement (i.e., the second statement)?
Suppose we run both statements in an explicit transaction (explicitly defining it or using a multi-statement request). In that case, the table’s content will be the same as before the transaction was executed. Neither the DELETE statement nor the INSERT statement changes the target table “TestTable”.
If we run both statements separately (two implicit transactions), the DELETE statement is committed, but as the INSERT statement failed, the table “TestTable” will be empty.
Furthermore, any request using the table “TestTable” after the DELETE statement might be accessed with a dirty read or an empty or inconsistent table.
Transaction Types And Restartability
The risk of accessing inconsistent data should always be considered in your ETL process.
Job in your ETL process should be restartable. This includes the correct selection of the transaction type and proper error handling.
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.