Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 30 Jun 2005 @ 19:14:45 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Transaction Rollback
 
From:   Dieter Noeth

Charles J Dong wrote:

  I create a SET TABLE A with no fallback, no before_journal, and no after_journal.  


Any secondary indexes?


  Then I loaded it with an INSERT statement,  


  Because of running out of space, the job failed. Table B had 395370215 rows. The whole process, from starting INSERT to end of rollback, took 1 day, 9 hours, 57 minutes, 22 seconds. I did not stop the Teradata, and did not kill the session. I did not intervene, and just let it completely rollback.  


Outch, did you submit the insert within an ANSI or a Teradata session?


  Afterwards, I added more space to the database, and reran the same SQL. It took only 1 hour, 19 minutes and 39 seconds to load 132520696 rows. Duplicate rows were not loaded into this SET TABLE.  


  Why was there a so large time difference, 34 hours vs. 1.5 hours (22 times as much for rollback).  


An insert/select into an empty table works without transient journal (there's only one entry, "table was empty"), *if* the optimizer knows that the transaction is commited:

Teradata session: there was no "begin transaction", so the ins/sel is a single transaction.

ANSI session: ins/sel followed by a commit submitted as a multistatement

A rollback will then be processed similar to a delete without where condition, almost immediatly (similar to a truncate in other DBMSes).

So in your case it probably was different, that's why it was sooo slooow.


  Is there anything Teradata can work on?  


They already speeded up rollbacks in V2R6...

Which version you're running?


  Or I should do it in an alternative way, or the system can be set up in a better "SHAPE"? Table a was empty before loading.  


That's the first pre-requisite for a fast ins/sel.


  I do not know if I can kill a transaction or not. As I was told, the server would resume rollback even if I restarted it while rollbacking in progress. I could abort the session, but I was also told the session would not terminate until rollback completed.  


That's true.


  But I could call Teradata Tech Support for rescue. That is ridiculous, I think. If it happens in a production environment, what can I do? Calling Teradata?  


Would you run that huge transaction on Oracle? At least now you know you shouldn't do that the way you did on Teradata ;-)


  Otherwise, the system had to go down for 2 days, and people could only watch it dying helplessly. Table and A and B (?) cannot be queried during the whole process.  


Table b may be selected and if you "lock row access" you still can access table a.


  I think this kind of problems happens frequently to a DBA, especially when the data is skew, or disk space is limited.  


This shouldn't happen frequently, because a dba should know in advance if a database will run out of space during a load...


  If a DBA cannot handle it NOW, NCR should provide a better solution instead of letting DBAs call for help.  


Starting with V2R5.1 a dba can cancel a rollback manually without help from NCR using rcvmanager.


Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023