Re: Oracle Sequences and ACID...

Peter Buzanits (peter_at_icb.co.at) wrote:
: Do transactions using sequences meet the ACID-requirements for
: transactions?

A sequence generator is not, technically speaking, part of a database design; rather, it is a utility provided by the vendor. What you consider the database is content managed by the application. A transaction consists of:

1. changes to the data dictionary, and
2. changes to data-oriented objects or constructs

I submit that ACID applies to Oracle managing itself, not your data.

: the Atomicity...

A sequence being incremented is the transaction here. It either happens or it doesn't.

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

No. The data dictionary controls it. The application is unaware of it. Good physical design practice normally has a sequence as part of a primary key, from the business application's standpoint.

IOW, the application here is the database (transaction ot manage itself), and the specific business application is the outsider that has no control over it, and cannot tell if a sequence number has been skipped due to rollback or error. Seen from that perspective, a commit, a rollback, or an error result are all database transactions because a) the database content changes based on it (the data dictionary tables, to be precise) and b) the outside world has no control over it.

: So do transactions using sequences meet the ACID-requirements?

Yes.

BTW, ACID was promulgated as a means of enforcing programming practices, not design practices. Read Jim Gray's book on the subject.

Regards,
jh
--

Jared Hecker	| HWA Inc. - Oracle architecture and Administration
jared_at_hwai.com	|  ** serving NYC and New Jersey **