Re: Oracle Sequences and ACID...

I would agree that it's not ACID. On the other hand it is how people build systems.

Under your example you would need to serialize all transactions using the sequence, meaning transaction A has to commit before transaction B can get its value from the sequence, just in case A rolls back. ORACLE's sequence was designed to get around this type of bottleneck You could consider it an optimistic approach to atomicity I suppose. I think you can see the benefits to system performance this approach provides, at the cost of holes in the sequence of numbers.

If you really want ACID, you can always build your application that way, store the nextval in a table and have every transaction update the table. This will guarantee you don't lose any numbers along the way. It will just be slow.

Cheers

> Do transactions using sequences meet the ACID-requirements for
> transactions?
>
> As I understand, a rollback does NOT undo an increment of a sequence
> during the transaction.
>
> BUT: Doesn't this behaviour violate the ACID-requirements of a
> transaction? A means Atomicity. A transaction is performed eighter
> completely or NO changes are made at all. If you rollback a transaction,
> sequence numbers remain increased (=updated). This can produce gaps in
> the sequence, that could be a bad surprise for a developer who relies on
> the Atomicity...
>
> I means Isolation. No change made during a transaction can bee seen
> outside. But a sequence increment done by transaction X must be seen by
> transaction Y for avoiding double-use of a number...
>
> So do transactions using sequences meet the ACID-requirements?
>
> Peter
> --
> ---------------------------------------------------
> DI Peter Buzanits
> mailto:peter@icb.co.at http://peter.icb.co.at
> AOL-IM: Buzanits Keys available on PGP-Servers
> ---------------------------------------------------
Received on Tue May 04 1999 - 09:16:53 CDT