DWHPro Insights

Teradata Rollbacks: Understanding the Impact on Performance and How to Avoid Them

By Roland Wenzlofsky ·

Teradata Rollbacks: Understanding the Impact on Performance and How to Avoid Them

What is the impact of a Teradata rollback on performance?

Rolling back many rows can negatively impact performance because it requires a lot of CPU. Furthermore, the table on which the rollback occurs can be locked for a long time. We know cases where the table was locked for days! Rollback must balance the duration of the lock against the resources used.

How can rollbacks in Teradata be avoided?

Many rows should be deleted by multi-load since no transient journal is used here. If a table is populated from several source tables, using a multistatement INSERT/SELECT is better. With the Multi statement INSERT/SELECT, the Transient Journal only notes that the target table is empty.

How to Abort the Teradata Rollback

Rollback cancellation can invalidate the table and is a DBA recovery action, not routine tuning. Follow a documented recovery procedure and verify a rebuild/restore path; skew on one AMP does not inevitably stop the entire system.

Watch: what happens when you abort a big DELETE

This video shows why an aborted DELETE keeps the table locked while Teradata rolls it back, how the fastpath DELETE and the empty-table INSERT … SELECT avoid the rollback, and what CANCEL ROLLBACK costs.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Careful consideration is crucial, as the table in question may become inconsistent and practically unusable. Therefore, a ROLLBACK should only be terminated in the following cases:

  • The ROLLBACK is expected to take longer than restoring the table
  • It is a table that is only used temporarily and is therefore insignificant and can be rebuilt at any time

A practical example of Teradata Cancel Rollback

Assuming the execution of the following SQL statement on the DWHPRO.NUSI_TEST table, with a total of 100 million rows:

UPDATE DWHPRO.NUSI_TEST SET COL2 = 1 WHERE COL2 <= 80

After an additional hour of waiting, the task remains incomplete. Upon reviewing the Viewpoint, it becomes apparent that the ROLLBACK process is imbalanced and only completed on one AMP.

Teradata Cancel Rollback – Checklist

The console sequence is a historical recovery illustration, not an instruction to execute broad cancellation. Recovery and reuse require the documented procedure for the exact object and release; no cancellation was performed in this audit.

Teradata Cancel Rollback in detail

On the Linux console, we execute the following command to see if a ROLLBACK is in progress at all:

awtmon 1 100
The first parameter is the interval (in seconds) in which the AMPs are tested. The second parameter determines how often the AMPs are tested in a loop (100 times).
awtmon

Starting the Database Window

To do this, execute the following command in the Linux shell:
xdbw

Starting the Recovery Managers (rcvmanager)

Click on the icon “rcvmanager” to open the window to cancel the ROLLBACK (in the graphic above, it is the icon on the left side). The following window opens:rcvmanager

List the tables in ROLLBACK status

Type list rollback tables; (where it says “Enter a command”). Don’t forget the semicolon.

You get a list with TableID, database, and table name of the tables with the status “ROLLBACK”. Ours has the TableId “0000:0FF8”.

You can also see how many rows are still in the transient journal (TJ rows left) and how many have already been rolled back (TJ rows done), as well as an estimate of the expected runtime (to be used with caution, especially with significant skew)

rollbacktables

Termination of the ROLLBACK

To cancel the ROLLBACK, you have to execute the following command:
cancel rollback on table 0000:0FF8

Confirm with ‘y’ that you want to cancel the rollback, and you will get the message “Rollback on “DATABASE”.”TABLE” is canceled.

You will also be reminded that the table is invalidated and only limits its use to a few cases.

cancelrollback

What activities can be performed on the table after canceling the ROLLBACK?

  • DELETE DWHPRO.NUSI_TEST
  • Rebuild the table headers
  • DROP TABLE DWHPRO.NUSI_TEST
  • Restore the table from an archive
  • Dump the table into an archive

If you try to query the table, you will get this error message:

SELECT * FROM DWHPRO.NUSI_TEST;
SELECT Failed: [7562] Invalid operation on table DWHPRO.NUSI_TEST.
DELETE FROM DWHPRO.NUSI_TEST;
SELECT * FROM DWHPRO.NUSI_TEST;
SELECT completed. 0 Rows returned. Elapsed time = 00:00:00

Final Considerations

Teradata CANCEL ROLLBACK is a way of ending a ROLLBACK in an emergency.

Do not use this option casually, as it will make the table inoperable. Prior to taking this severe action, consult the recovery manager to ascertain the remaining rows in the Transient Journal and the rate at which they are decreasing.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam