Re: "Transactions are bad, real bad" - discuss

I'm not sure what is the best point to rollback:-)

Did you agree that multiversion concurrency model and derived "flashback" query functionality allows quering the state of the auction AS OF 12:00 when physically queried at later time, say 12:15? It might seem to be no big deal, because alternatively the auction organizers must schedule their query exactly at 12:00. However, as you mentioned, if auction bidder is allowed direct access to the database, he might start transaction that [intentionally] would not meet the deadline. At 12:00, with one transaction still in progress organizers query would wait for an exclusive lock. You wont be able either to enforce the rule "Any transaction must be committed before 12:00", nor get the auction snapshot at 12:00.

Now, "read-past" transaction isolation allows quering the auction state exactly at 12:00. No transaction in progress would interfere with organizers query -- [partial] results of the transactions that were not comitted before 12:00 wouldn't be visible to the query.

Now, you suggested to use triggers in order to emulate "read-past" transaction isolation (available in Postgress and Oracle). You can't do that without logging all the events -- row insertions, field modifications, commit points, rollback commands in the log. You can log all these only if you write those event within an independent transaction. If main transaction rollbacks, you still would have transaction events in the log.

I'm not sure what transaction terminology is valid beyond "flat" transaction. Gray&Reuter book mentiones chained, nested, multilevel transactions. I don't think any of those apply in my case. Autonomous or independent transaction is just application ability to initiate another transaction before completeng previous one. We can trivially do so if an application can open more than one connection, but inside a trigger we reuse the same connection.

Autonomous transaction solves auction problem simply because bid insertion triggers a smaller transaction that end user has no control of.

Yet one more alternative solution is restricting the end user to the application that don't allow transaction control at all, of course, rendering "flashback" and "autonomous" transaction features somewhat less significant.

"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:b98rfq$56gi$4_at_gazette.almaden.ibm.com...
> "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
> news:iZysa.12$rv3.138_at_news.oracle.com...
> > "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
> > news:b8to09$2178$1_at_gazette.almaden.ibm.com...
> So with "flashback" you see uncommitted rows???
>
> > If you try simulating
> > this with triggers, a user can still rollback transaction so that the
record
> > would be lost. Then, no wonder that you can't reproduce a consistent
picture
> > what Bid Auction state was as of 12:00.
>
> I don't think I follow you, but if I do you are saying that uncommitted
> transactions should be part of the 'consistent picture' as at 12:00 ??!!
>
> > I think you have to use autonomous transaction inside the trigger in
order
> > to correctly simulate "flashback".
>
> I've no idea what a 'autonomous transaction' is. Oracle specific
thingamajig?
>
> > > Practically, I cannot use a 'flashback' query if I want to know say
the
> > > average max bid for auctions during the past year. In your scheme I
would
> > have
> > > to query each auction row AS AT the end time of each auction, and only
> > then do
> > > my yearly average calculation...
> >
> > That is easy: AS OF state can simply be archieved to the other table.
>
> What 'other table'? What would it look like? It the archive instatanous?
>
>
> Mikito, if you belie that you do have a valid point here, could I ask you
> kindly to restate it in logical terms and not in terms of Oracle quirks
and
> features.
Received on Tue May 06 2003 - 22:07:43 CEST