fbpx

Teradata Transaction Modes

By Roland Wenzlofsky

January 6, 2020


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 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 semicolon position!

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)?

Suppose we run both statements in an explicit transaction (either explicitly defining it or using a multi-statement request). In that case, the table’s content 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 might be accessed 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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • 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.

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

    You might also like

    >