Teradata Rollback – Abort as an Emergency Solution

DWH Pro Admin

February 19, 2020

minutes reading time

What is a Teradata Rollback?

A rollback reverses an incomplete transaction. Transactions can be unfinished for various reasons, such as user termination or database restart. Rollback ensures database integrity and is an essential aspect of an RDBMS. Rollbacks are implemented in Teradata using the DBC.TransientJournal table. A Before Image is stored in this table, i.e., before any changes are made.

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 takes place can be locked for a long time. We know cases where the table was locked for days! Rollback needs to find a middle ground between the duration of the lock and 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, it is better to use a multistatement INSERT/SELECT. With the Multi statement INSERT/SELECT, the Transient Journal only notes that the target table is empty.

How to Abort the Teradata Rollback

Sometimes, a DML statement on a large table causes a ROLLBACK to be executed, which runs for a long time. You often have to decide to wait until the ROLLBACK is finished or abort the ROLLBACK.

Canceling a rollback has the advantage that no further resources are wasted. It is advantageous if the ROLLBACK does not allow the system to run in parallel (high skew), and therefore, the entire workload is affected.

Nevertheless, you have to think carefully about what you are doing because the affected table will end up in an inconsistent state and practically unusable. Therefore, the termination of a ROLLBACK is only desirable 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

Suppose the SQL statement below is executed on the DWHPRO.NUSI_TEST table, which contains 100 million rows:


Finally, after waiting another hour, it is still not finished, and in Viewpoint, we see the reason: The ROLLBACK is skewed and is done on a single AMP!

The table is temporary and, therefore, not essential to us. But the parallelism of the Teradata system is destroyed by the ROLLBACK on an AMP. We, therefore, decide to cancel the ROLLBACK.

Teradata Cancel Rollback – Checklist

Here are the individual steps you need to take:

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).
Teradata Rollback - Abort as an Emergency Solution 1

Starting the Database Window

To do this, execute the following command in the Linux shell:
xdbwTeradata Rollback - Abort as an Emergency Solution 2

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:Teradata Rollback - Abort as an Emergency Solution 3

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 solid skew)

Teradata Rollback - Abort as an Emergency Solution 4

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.

Teradata Rollback - Abort as an Emergency Solution 5

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

  • The rebuilding of the table headers
  • 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 Failed: [7562] Invalid operation on table DWHPRO.NUSI_TEST.

After deleting all rows, it can be used as usual again:

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.

Under no circumstances should it be used lightly, as it renders the affected table unusable. You should check with the recovery manager how many rows are left in the Transient Journal (TJ rows left) and how quickly the number of rows decreases before you take this drastic measure.

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

You might also like