Aborting Teradata Transactions Teradata

Anytime an error occurs in an SQL statement, it is automatically aborted, or ended with a bad outcome (error code greater than 0).

To manually abort a transaction, the user can issue either of the next commands:

ABORT;

or

ROLLBACK;

Logging off and ending the session without performing an ET also constitutes an abort of the transaction. The caution here is that all work is rolled back. The example below starts a transaction and runs several SQL statements. Watch what happens in the end when the transaction is ABORTED.

The next commands use the Customer table to demonstrate the functionality of an explicit BTET transaction:

Customer table to demonstrate the functionality of an explicit BTET transaction

BT; Sel * from Customer_table; 5 Rows ReturnedCustomer table to demonstrate the functionality of an explicit BTET transaction
5 Rows ReturnedCustomer table to demonstrate the functionality of an explicit BTET transaction

The interesting aspect of this exercise comes after the DELETE. Because this is a multistep transaction, all before images of the deleted rows are in the Transient Journal, but not in the table. Therefore, the SELECT returns no rows as the correct status of the table. However, the ABORT tells Teradata that the transaction has failed. So, it rolls the before images from the Transient Journal back into the table. Then, they are again available for the last SELECT just as they were for the first SELECT.

A way to accidentally abort a multi-step transaction is to perform any other SQL statement after using DDL. Since the DD is locked for WRITE, Teradata demands that you commit work as the next step of a multi-statement transaction. Otherwise, the database aborts your transaction and releases the locks.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics