Re: Catch the commit

On Thu, 30 Jun 2005 00:06:42 +0200
"Andreas 'ads' Scherbaum" <adsmail(at)wars-nicht(dot)de> wrote:

> is it possible in a module to receive an event, get a trigger fired,
> get a function called or something like this when the current
> transaction is about to be committed?
>
> Background:
> In a module (tablelog) i need the latest possible timestamp before
> committing the data for the case, that there is more then one started
> transaction. If this happens and the second transaction is commited
> first, i have for the time the first transaction is going on invalid
> data in my log.

To imagine the problem a little bit:

tablelog() does logging of all changes on a table. Here an example:

- start of transaction 1
- insert in transaction 1
- start of transaction 2
- insert in transaction 2
- commit transaction 2
- commit transaction 1

In the real world, if i look into the table between commit 2 and 1, i
only would see the second insert. But since i have no idea, how to get
the time of the commit, for now i log the current timestamp ( now() ).
If i use the logged data later and restore the state of the table
between the two commits, i see both inserts. Which is actually wrong.

The problem only occurs if there are concurrent transactions and only,
if a timestamp between the commits is requested. But since it exist, i
cannot always be sure to return the correct data.

Has anybody an idea about this?

Kind regards

--
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
(Ferenc Mantfeld)