Xref: alice comp.databases.oracle.server:48277
Path: alice!news-feed.fnsi.net!netnews.com!howland.erols.net!newsfeed.cwix.com!198.69.0.83!jupiter.planet.net!pandora.planet.net!jared
From: jared@pandora.planet.net (Jared Hecker)
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle Sequences and ACID...
Date: 5 May 1999 13:23:57 GMT
Organization: Planet Access Network Inc.
Lines: 47
Message-ID: <7gpgpd$lkj@jupiter.planet.net>
References: <372EF22A.649C353E@icb.co.at>
X-Newsreader: TIN [version 1.2 PL2]

Peter Buzanits (peter@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: 

a) changes to the data dictionary, and 
b) 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@hwai.com	|  ** serving NYC and New Jersey **