Transaction Question
Dave asked whether log-table inserts inside a transaction could be committed even if the transaction rolls back. Keith Simmons said nested transactions are not possible and suggested writing a failure record separately. Art Kagel suggested a second connection with WITH CONCURRENT TRANSACTIONS. Claus Samuelsen and Zachi suggested a RAW table, which is not rolled back.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello All, We have the following transaction situation. Is it possible within a transaction, "begin work" ......"commit work" to insert records to a log table and have those records committed regardless of whether the transaction gets rolled back or committed. In fact it would be especially valuable to have those records in the log table when the transaction get rolled back. Any ideas?? --Dave
On 24 Apr 2007 07:28:58 -0700, Dave <In4MixDBA@gmail.com> wrote: > Hello All, > > We have the following transaction situation. Is it possible within a > transaction, "begin work" ......"commit work" to insert records to a > log table and have those records committed regardless of whether the > transaction gets rolled back or committed. In fact it would be > especially valuable to have those records in the log table when the > transaction get rolled back. > > Any ideas?? > > --Dave > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Dave It is not possible to have two levels of transaction, what you could do is write a successful log record within the transaction and then write a failure log record in a separate transaction as part of your failure/rollback routine. If you rollback a transaction then everything within that transaction (even two-stage commit on a remote database) is rolled back. Keith
Dave wrote: > Hello All, > > We have the following transaction situation. Is it possible within a > transaction, "begin work" ......"commit work" to insert records to a > log table and have those records committed regardless of whether the > transaction gets rolled back or committed. In fact it would be > especially valuable to have those records in the log table when the > transaction get rolled back. Write the log records in a separate connection. If you open the connections with the 'WITH CONCURRENT TRANSACTIONS' clause you can switch between them during the transaction in one and write to the log table in the other. Art S. Kagel
Dave wrote: > Hello All, > > We have the following transaction situation. Is it possible within a > transaction, "begin work" ......"commit work" to insert records to a > log table and have those records committed regardless of whether the > transaction gets rolled back or committed. In fact it would be > especially valuable to have those records in the log table when the > transaction get rolled back. > > Any ideas?? > > --Dave > If your using IDS 9.2 or (preferable much) later create you log table as a RAW table. Everything that goes into raw tables are outside the transaction and cannot be rolled back. Claus
On Apr 24, 10:28 am, Dave <In4Mix...@gmail.com> wrote: > Hello All, > > We have the following transaction situation. Is it possible within a > transaction, "begin work" ......"commit work" to insert records to a > log table and have those records committed regardless of whether the > transaction gets rolled back or committed. In fact it would be > especially valuable to have those records in the log table when the > transaction get rolled back. > > Any ideas?? > > --Dave Informix does not support nesting transactions. You can write these records to a raw table, or use a different connection for these records Zachi.