Re: Rollback

>Date: Tue, 04 Feb 1997 11:48:09 -0800 >From: Rick Ward <wardr@gusco.com> >X-Informix-List-Id: <list.12965> > >I have a two part question for you all: > >1) When Online detects a deadlock, does it automatically rollback the > second transaction which will create the deadlock so that it can > release the locks allowing the other process to continue, or does > it expect the program to handle the error and issue the required > rollback ? (For reference, if DB2 detects a deadlock it rolls back > the transaction with the least amount of work to rollback). There is no automatic rollback. The statement which triggered the deadlock fails (so it is rolled back), but the transaction as a whole is in the state before the statement started. You have the opportunity to recover, therefore... (Yes, it does mean that OnLine internally supports savepoints within a transaction, and yes, it would be darned useful if we could get our hands on savepoints too.) >2) Are there any SQL statements which will cause an automatic rollback > within a transaction ? I don't think so, but see below... >For example: > > BEGIN WORK > SELECT * FROM table WHERE col1="x"; > UPDATE table2 SET col2="y" WHERE col1="burp"; > INSERT INTO logtab VALUES ("burp-done");> COMMIT WORK; > > If the insert failed because of, for example, lack of disk space, > would Online rollback the transaction, or expect the program to > handle the error ? If the INSERT failed, the status returned from the INSERT would be negative and it would be up to the program to handle the error. This code seems to commit the transaction anyway, but that's because it is example code. However, if there is, for example, a deferred constraint which is checked at COMMIT time, and if that deferred constraint is violated at COMMIT time, then the transaction will be rolled back, even though it was otherwise OK until the COMMIT statement was executed. I'm not certain of this; but I believe that if you were in a transaction when you attempt to COMMIT, that transaction is always terminated after the COMMIT statement completes, though it may have been rolled back rather than succesfully committed, a sorry state of affairs indicated by some error status from the COMMIT statement. Ouch! > I ask because it is obvious in the case of a lack > of disk space that the transaction cannot complete, therefore a > rollback by the engine would enable the locks held to be released > earlier than the program code handling it. Another way in which this could happen would be with an INSERT cursor which was flushed implicitly by the COMMIT, and which found that there was no disk space for the data it had to insert. The only option available to COMMIT under the 'transaction is always terminated by COMMIT' rule is to do a rollback. If the rule I think applies doesn't, then presumably the transaction is still active after the commit and can be manually rolled back. Distributed transactions with 2-phase commit might also run into problems with remote systems being unable to commit... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>