| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Order of Transactions
On Wed, 7 Oct 1998 14:20:23 -0500, "Matthew MacFarland"
<matthew_macfarland_at_dril-quip.com> wrote:
>Hello everyone,
>
>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.
The sequence numbers are assigned to the records on the moment when the record is updated/inserted/deleted, not when those transactions are commited. It would be no different if you would be using select_max_id technique instead of sequences. For example:
Time1: user1 inserts record1 -
the log_table_id number is assigned number 100 Time2: user2 inserts record2 -
the log_table_id number is assigned number 101
Time3: user2 commits
Time4: user1 commits
Although the commit for insert2 happened before the commit of insert1, it has a higher number in a log table, because the sequence number is assigned on DML operation, not on commit.
>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. Seems like some performance
>problems could result from doing this.
>
If you would be using select_max_id+1 instead of a sequence in the above example, then both inserts would be assigned *the same* log_table_id number. Eventhogh insert1 would trigger the increasing of the log_id, the insert2 would not "see" this increase because transaction1 is not commited yet! So if log_table_id is PK of the log_table, then the second commit would fail. In this case you would have to repeat the whole transaction2 (which could eventualy fail again because of the same conflict with some other transaction...). If the log_table_id is not the PK, then both insert will have the same log_table_id, allthough they were not commited simultaneously.
>Has anyone had a similar problem? How did you solve it?
>
>Thanks,
>
>Matthew MacFarland
>Dril-Quip, Inc.
>matthew_macfarland_at_dril-quip.com
HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |