Re: "Transactions are bad, real bad" - discuss
Date: Fri, 2 May 2003 13:21:36 +0100
Message-ID: <b8to09$2178$1_at_gazette.almaden.ibm.com>
"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 - 14:21:36 CEST