16.10 - Noticing a Slow Rollback of an ALTER TABLE Statement - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Because Teradata ensures that all transactions must succeed or fail in their entirety, the system does not allow partial results. In most cases, if a transaction fails, Teradata rolls back all the previous actions of a transaction. This requires that the system logs the before state of every action so that the action can, if necessary, be undone later. For instance, when you delete a row, Teradata must first log a copy of the row, so that it can be restored if the transaction fails.

Sometimes, logging is extremely expensive compared to the cost of the action, for example, dropping a large table. If the system does not have to roll the table back later, the system may simply release all table data, deallocating entire cylinders with a single action. In this case, Teradata delays performing the action until it is certain that the transaction will succeed. In effect, Teradata commits the transaction before executing the deferred action.

During this special post-commit phase, the transaction is still working and holding locks, but it cannot be aborted (since it is already committed). In most cases, this post-commit phase finishes quickly and is unnoticeable.

The exception is the ALTER TABLE statement. It may take longer when used to add or remove columns of a table. ALTER TABLE requires that every row in the table be rebuilt and is, therefore, an expensive operation. It can take hours on a large table, even with the highly optimized, block-oriented method that Teradata uses.

When you notice that a rollback of an ALTER TABLE statement is taking a very long time but you cannot abort it, the system is deferring an abort and will need to complete it.