Cancel Rollback on a table with referential integrity

UDA
Enthusiast

Cancel Rollback on a table with referential integrity

We attempted a large load on an empty table that was highly ineficient and had to abort the session after hours of running.

Now the rollback has been running for over 24hrs and is still locking the target table.

We tried Recovery Mananager with the "cancel rollback on table" command but gave us the "has referential integrity constraint" error.

We also tried to restart the database but the lock still persits.

Since the table was empty, we are fine in somehow dropping it forcefully but I'm not aware of a way that would help us do that.

Can someone provide me with some options?

Thanks,

Noel

13 REPLIES
Enthusiast

Re: Cancel Rollback on a table with referential integrity

Hi,

Have you tried :

SELECT SYSLIB.AbortSessions(1,'your_user',0,'Y','Y');

Cheers,

Raja

Teradata Employee

Re: Cancel Rollback on a table with referential integrity

Hi Noel,

If you have found a solution for this, then please kindly share.  

The documentation says that it is not allowed.

Regards,

Vlad.

Enthusiast

Re: Cancel Rollback on a table with referential integrity

Hi Noel,

Just to add : I am curious if it is hard RI or soft RI too(it can be strange with data volume), can you please share this info for me .

Vlad,

If you dont mind, can you please share the document you refer to my mail id : ktraj1@gmail.com.

Thanks and regards,

Raja

Teradata Employee

Re: Cancel Rollback on a table with referential integrity

Enthusiast

Re: Cancel Rollback on a table with referential integrity

Hi,

I used to kill jobs, either from Linux, unix prod as a last resort. It's usually fine. Of course, with other DBs, the index becomes unusabe or invalid most of the time.

Thank you very much for the link Vlad.

Cheers,

Raja

Enthusiast

Re: Cancel Rollback on a table with referential integrity

We are using 13.10 and the table does have hard RI ("with check option").

The following will initiate the abort but I'm looking at cancelling the abort:

SELECT SYSLIB.AbortSessions(1,'your_user',0,'Y','Y'); 

The link talks about the "cancel rollback on table" command and it also mentions the exact issue I'm encountering:

"You cannot cancel rollback on tables that have any referential integrity constraints."

Not sure why a referential integrity constraint would disallow you from cancelling the rollback, I suspect the utility simply does not handle it and should ideally be enhanced to handle the scenario in a future release.  Maybe someone from Teradata utility development can comment.

Thanks,

Noel

Enthusiast

Re: Cancel Rollback on a table with referential integrity

Raja, you mentioned that you killed jobs, do you mean kill specific Teradata processes at the OS layer?  If so, how would you determine which one is performing the rollback?  Note that I even tried restarting the database and the rollback simply continued from where it left off.

Thanks,

Noel

Teradata Employee

Re: Cancel Rollback on a table with referential integrity

You didn't mention how you were doing the loading?

An Insert Select into an empty table should be a fast rollback. A tpump style load should be a short rollback because each PACK is committed.

Re the questions about restart to get rid of the rollback - that will not help because the journal is kept over restart events in order to make sure we maintain database consistency over an event like that. As the system restarts, it scans the journal for uncommitted transactions and starts rollbacks for each one before allowing the system to open for business, including locking the table(s) appropriately. All that a restart accomplishes is to make the rollback take longer due to the rollback and journal scan.

To the question about aborting the Teradata process doing the rollback: Don't go there! Forcibly aborting Teradata system tasks/threads will crash the system and cause a restart at best and can also hang the system requiring services intervention.

Enthusiast

Re: Cancel Rollback on a table with referential integrity

Good question and great feedback Todd.

Yes, you are right, it is a bulk insert (insert select) but it has a where not exists clause on the target table to only insert applicable records which is part of the performance and rollback problem (even if the target table is empty).

This works nicely for smaller tables but awful for larger tables so it has been decided to NEVER use this pattern for larger table loads and we have instead implemented other alternitves.   Over time, we may accidentally encounter this type of issue again since we do bulk insert loads for many of our tables.  I would like to have an action plan if we encounter this type of issue again.

Thanks,

Noel