January 6

1 comments

Teradata Transaction Modes

By Roland Wenzlofsky

January 6, 2020

transaction, transient journal

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 the termination of a previous transaction.

ANSI mode transactions end when a COMMIT statement is executed or when 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 in itself.

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), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 2 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 row that is changed 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 making use of transaction handling. Each method has its own advantages, disadvantages, and behavior which has to be considered.

As an example let's look at two statements which 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;
teradata transaction Teradata MODE
Two statements in one explicit transaction

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;
teradata transaction ANSI MODE
Two implicit single transactions

Note that the independent execution of the two statements differs from the multi-statement request only by the position of the semicolon!

Important information

Whatever solution you chose, 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)?

If we run both statements in an explicit transaction (either explicitly defining it or using a multi-statement request),  the content of the table will be the same as it was 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 was done might access with a dirty read 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 correct error handling.

Roland Wenzlofsky


Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

  • Avatar
    Dieter Nöth says:

    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.

    Reply

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Never miss a good story!

     Subscribe to our newsletter to keep up with the latest trends!

    >