 |
 |
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. | |
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
| |
