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

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:e1dsa.6$zt6.107_at_news.oracle.com...
>
> "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
> > Don't you think that this just highlights why transactions are
> troublesome?
> >
> > AS OF "30-APR-2002 12:00"
> > the max bid in the auction closing as 12:00 was $10
> >
> > AS OF "30-APR-2002 12:15"
> > the max bid in the auction closing as 12:00 was $12

> I disagree. If auction is closing at 12:00, then organizers issue
> AS OF "30-APR-2002 12:00"
> query and get the exact snapshot of what bids were placed at 12:00. Quering
> AS OF "30-APR-2002 12:15"
> doen't make any sence at all. What is so special about 12:15, as you never
> listed it in the problem statement?

12:15 is some arbitrary time after the auction closes and after the row was committed.

Maybe you need to try it in your favourite DBMS. I don't have Oracle, so can only simulate it's AS OF function:

CREATE TABLE Bid
( Auction_Item INTEGER NOT NULL
, Bid_Amount DECIMAL(15,3) NOT NULL
, Bid_Time TIMESTAMP NOT NULL WITH DEFAULT

     CHECK(TIME(Bid_Time) < TIME('12:00:00')) , PRIMARY KEY(Auction_Item, Bid_Time)) ;
CREATE TRIGGER bid_trig NO CASCADE BEFORE UPDATE ON Bid

    REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL SET N.Bid_time = CURRENT TIMESTAMP
;
COMMIT;

• At real time 11:55 do ...

insert into bid (auction_item, bid_amount) values (1, 50.00);

COMMIT;

• and immediately also do

update bid set bid_amount = 60.00 where auction_item = 1

• Now wait untill 12.05 in real time, then

COMMIT; So
 SELECT * FROM bid AS OF "30-APR-2002 12:00" returns

    $50
for the acution closing at 12:00

but the current database (as of 12:05 or more) now contradits itself  SELECT * FROM bid
returns

    $60
for the acution closing at 12:00

So sometime *after* the auction closed, the maximum bid altered from $10 to $12.

My business rule that bids must be made before the auction closes (12:55 in this case) cannot be enforced by a DBMS (like DB2 and I'm sure Oracle also) that does not equate COMMIT time with STATEMENT time.

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...

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Fri May 02 2003 - 07:21:36 CDT