Re: Order of Transactions

From: Igor V. Podolsky <igoryok_at_soft-review.kiev.ua>
Date: Thu, 8 Oct 98 20:33:44 +0200
Message-ID: <AAsGAIb0pst@soft-review.kiev.ua>

> Hello everyone,

Hi

> I am trying to find a good way to determine the order in which rows in
> specific tables are inserted, updated, and deleted. I started with and
> Oracle sequence but found that the order of the sequence number assigned to
> my log table by triggers does not always match the order that the rows got
> committed.

  ^^^^^^^^^

It really does not match order that the rows got commited, but it must match order that the rows where inserted/updated/deleted (or any another event that cause sequence reads). I'm dont know how to determine 'commit' order. :( There no 'commit' triggers. May be You can use some construction based on Oracle8 deferrable constraints.

> I was thinking of replacing the Oracle sequence with my own
> version that reads a number from a table adds one and updates the number for
> next time. Since all users share the same table and the trigger code that
> reads and updates the number is in the same transaction this should yield
> truly sequential number assignment for the log.

You'll have problems with transaction isolation - different transactions can got same numbers from Your table. You'll need to lock table to prevent it. And You got insert/update/delete enumeration instead of commit again.

--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)

Igor V. Podolsky (igoryok_at_soft-review.kiev.ua) Received on Thu Oct 08 1998 - 13:33:44 CDT