Re: Commit

On 26 Sep 2000, Surfer! <nevis-view@nospam.demon.co.uk> wrote: >Sonia Gillespie <sonia_gillespie@lagan.com> writes >>Is there a mechanism that Informix uses by which to automatically commit any >>transactions that take place on the database? >> >>If there are a number of stored procedures on the database that don't >>contain commit statements, how could they get committed on the database? > >If they are committed within another transaction. It will also commit >changes when a program exits unless you are correctly using 'BEGIN WORK' >and 'COMMIT WORK' or 'ROLLBACK WORK'. Informix recognizes three types of database: * unlogged * logged * mode ansi An unlogged database does not support transactions. All statements are effectively committed when they complete. A logged database does support transactions, but you have to explicitly start the transaction with BEGIN WORK. Whatever piece of code did the BEGIN WORK is responsible for issuing the matching COMMIT WORK. If the session terminates without either COMMIT or ROLLBACK, then the transaction is rolled back. A MODE ANSI database is essentially always in a transaction -- no explicit BEGIN WORK is required to start a transaction(*). Any code that initiates what it considers to be a unit of work should also commit that work. It can get a bit tricky, though; what if the code is sometimes used on its own and sometimes as part of a larger transaction? Ideally, you'd have nested transactions. Unfortunately, this is not an ideal world. You can try to use BEGIN WORK: if that succeeds, then this code started the TX and should complete it; if the BEGIN fails, then assume some other code is responsible for committing the work. The really nasty part is 'what do you do if you need to rollback your own changes'? If you do ROLLBACK, you also rollback any changes made before the code was started, and the calling code may not even be aware of this and your could easily end up with an inconsistent database. Ouch. Time to ask for nested transactions (or named savepoints and rollback to named savepoint). -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!" (*) Yes, it's an over-simplification, but the details don't matter.