Archives of the TeradataForum
Message Posted: Thu, 30 Jun 2005 @ 19:14:45 GMT
| 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.|| |
| ||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.