Обсуждение: 'Official' definition of ACID compliance?

Does anyone know where I can find the 'official' definition of what it
meant by ACID compliance?

We're having a discussion about it that we could do with resolving. In
particular, the key point is what it meant by the 'C' part. I maintain
that MySQL is not ACID compliant because it will (among other things)
swallow integers that don't fit into a column silently and just
truncate it, while our DBA (while agreeing that this is not good
behaviour) maintains that this is not what the C part means: he says
that's just about transaction states (succeed or fail etc).

Anyone have a link?

Thanks.

--

Russ

Russ Brown wrote:
> Does anyone know where I can find the 'official' definition of what it
> meant by ACID compliance?
>
> We're having a discussion about it that we could do with resolving. In
> particular, the key point is what it meant by the 'C' part. I maintain
> that MySQL is not ACID compliant because it will (among other things)
> swallow integers that don't fit into a column silently and just
> truncate it, while our DBA (while agreeing that this is not good
> behaviour) maintains that this is not what the C part means: he says
> that's just about transaction states (succeed or fail etc).
>
> Anyone have a link?
>
> Thanks.
>


Pretty good overview, though not "official":

http://en.wikipedia.org/wiki/ACID

"The ACID concept is described in ISO/IEC 10026-1:1992 Section 4."



--
Alan Garrison
Cronosys, LLC <http://www.cronosys.com>
Phone: 216-221-4600 ext 308

On Thu, 2006-01-05 at 08:58, Russ Brown wrote:
> Does anyone know where I can find the 'official' definition of what it
> meant by ACID compliance?
>
> We're having a discussion about it that we could do with resolving. In
> particular, the key point is what it meant by the 'C' part. I maintain
> that MySQL is not ACID compliant because it will (among other things)
> swallow integers that don't fit into a column silently and just
> truncate it, while our DBA (while agreeing that this is not good
> behaviour) maintains that this is not what the C part means: he says
> that's just about transaction states (succeed or fail etc).

This seems a fairly good, short one:

http://databases.about.com/od/specificproducts/a/acid.htm

The C stands for consistency.  Consistency means that only valid data
can be written to the database.  MySQL fails this test precisely because
it does / can write inconsistent data to the database.  Note that even
the latest version, 5.0.xx, by default, inserts a truncated number on
overflow.  It does issue a warning, but that's little consolation now
that your data has been rendered inconsistent.  There is a switch you
can throw that tells it to only accept proper values, but get this,
users can turn it off.  So, there's no way to ensure that the database
enforces proper constraint on the values being inserted.

And why, after all this work, does MySQL still not have check
constraints.  They'd have to be easier to implement than some of the
other features they've implemented so far.

Alan Garrison wrote:
> Russ Brown wrote:
>> Does anyone know where I can find the 'official' definition of what it
>> meant by ACID compliance?
>>
>> We're having a discussion about it that we could do with resolving. In
>> particular, the key point is what it meant by the 'C' part. I maintain
>> that MySQL is not ACID compliant because it will (among other things)
>> swallow integers that don't fit into a column silently and just
>> truncate it, while our DBA (while agreeing that this is not good
>> behaviour) maintains that this is not what the C part means: he says
>> that's just about transaction states (succeed or fail etc).

I personally read "C" to disallow MySQL's truncation behavior. Suppose
you have a simple/stupid banking database with only one table listing
the amount in the bank for each customer. The manager sums up the
balances to find out how much is in the bank. Meanwhile Larry owes Jane
some money so he has it transferred to her account. The balance the
manager gets should be identical whether she runs the query before,
during or after the transaction.

Now Jane is a good saver so when Larry transfers the money, MySQL
truncates her account at the max amount allowed by the column-type. The
manager's reports will be incorrect as will Jane's account balance.
Unless Larry and Jane bank at Enron S&L, this is bound to violate some
rule or integrity constraint.

But if you and the DBA are in agreement that MySQL behaves badly, why
waste time arguing over which letter in some acronym is pertinent?

Cheers,
Steve

On Thu, 05 Jan 2006 08:22:01 -0800
Steve Crawford <scrawford@pinpointresearch.com> wrote:

> Alan Garrison wrote:
> > Russ Brown wrote:
> >> Does anyone know where I can find the 'official' definition of
> >> what it meant by ACID compliance?
> >>
> >> We're having a discussion about it that we could do with
> >> resolving. In particular, the key point is what it meant by the
> >> 'C' part. I maintain that MySQL is not ACID compliant because it
> >> will (among other things) swallow integers that don't fit into a
> >> column silently and just truncate it, while our DBA (while
> >> agreeing that this is not good behaviour) maintains that this is
> >> not what the C part means: he says that's just about transaction
> >> states (succeed or fail etc).
>
> I personally read "C" to disallow MySQL's truncation behavior.
> Suppose you have a simple/stupid banking database with only one table
> listing the amount in the bank for each customer. The manager sums up
> the balances to find out how much is in the bank. Meanwhile Larry
> owes Jane some money so he has it transferred to her account. The
> balance the manager gets should be identical whether she runs the
> query before, during or after the transaction.
>
> Now Jane is a good saver so when Larry transfers the money, MySQL
> truncates her account at the max amount allowed by the column-type.
> The manager's reports will be incorrect as will Jane's account
> balance. Unless Larry and Jane bank at Enron S&L, this is bound to
> violate some rule or integrity constraint.
>

Yes, I agree entirely. Actually, reading the wikipedia definition it
looks like what he was talking about is actually covered by the 'A'
part of ACID.

> But if you and the DBA are in agreement that MySQL behaves badly, why
> waste time arguing over which letter in some acronym is pertinent?
>

Oh, that's a long story. We're a MySQL house that I've been trying to
convert to PostgreSQL one way or the other for ages (with no success as
yet). Note that the argument isn't about which letter the type
truncation applies to, but whether it actually has anything to do
with ACID at all in the first place. The key for me is that the result of this argument has an
effect on the question: "Is MySQL ACID compliant". If I'm right, it's
not (which has political strategic benefits to me).

Thanks.

--

Russ

* Russ Brown (pickscrape@gmail.com) wrote:
> Oh, that's a long story. We're a MySQL house that I've been trying to
> convert to PostgreSQL one way or the other for ages (with no success as
> yet). Note that the argument isn't about which letter the type
> truncation applies to, but whether it actually has anything to do
> with ACID at all in the first place. The key for me is that the result of this argument has an
> effect on the question: "Is MySQL ACID compliant". If I'm right, it's
> not (which has political strategic benefits to me).

An even better thing to point out is that a DBA recommending MySQL isn't
a DBA at all. :)

    Enjoy,

        Stephen

On 1/5/06, Stephen Frost <sfrost@snowman.net> wrote:
> * Russ Brown (pickscrape@gmail.com) wrote:
> > Oh, that's a long story. We're a MySQL house that I've been trying to
> > convert to PostgreSQL one way or the other for ages (with no success as
> > yet). Note that the argument isn't about which letter the type
> > truncation applies to, but whether it actually has anything to do
> > with ACID at all in the first place. The key for me is that the result of this argument has an
> > effect on the question: "Is MySQL ACID compliant". If I'm right, it's
> > not (which has political strategic benefits to me).
>
> An even better thing to point out is that a DBA recommending MySQL isn't
> a DBA at all. :)
>

or is one that _loves risk_   ;)


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

At 16:38 05/01/2006, Stephen Frost wrote:
>* Russ Brown (pickscrape@gmail.com) wrote:
> > Oh, that's a long story. We're a MySQL house that I've been trying to
> > convert to PostgreSQL one way or the other for ages (with no success as
> > yet). Note that the argument isn't about which letter the type
> > truncation applies to, but whether it actually has anything to do
> > with ACID at all in the first place. The key for me is that the result
> of this argument has an
> > effect on the question: "Is MySQL ACID compliant". If I'm right, it's
> > not (which has political strategic benefits to me).
>
>An even better thing to point out is that a DBA recommending MySQL isn't
>a DBA at all. :)
>
>         Enjoy,
>
>                 Stephen

I used to work for MySQL (a job's a job after all) and I say in all honesty
that MySQL is not ACID compliant. Furthermore, MySQL is so lacked in
functionality that it should be used for anything but the simplest of
solutions. A database engine that does not support referential integrity,
triggers, stored procedures, user defined types, etc should not be taken
seriously



---

Regards
John Dean,
co-author of Rekall,
the only alternative
to MS Access

Am Donnerstag, 5. Januar 2006 17:01 schrieb Scott Marlowe:
> The C stands for consistency.  Consistency means that only valid data
> can be written to the database.  MySQL fails this test precisely because
> it does / can write inconsistent data to the database.  Note that even
> the latest version, 5.0.xx, by default, inserts a truncated number on
> overflow.

That's not at all what the C is about.  The C criterion means that a
transaction transfers the database from one consistent state to another.  To
my knowledge, MySQL does that.  On its way there, it silently alters data
that would violate this consistency criterion, but this does not affect the
fulfillment of the ACID criteria.

On 1/5/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> Am Donnerstag, 5. Januar 2006 17:01 schrieb Scott Marlowe:
> > The C stands for consistency.  Consistency means that only valid data
> > can be written to the database.  MySQL fails this test precisely because
> > it does / can write inconsistent data to the database.  Note that even
> > the latest version, 5.0.xx, by default, inserts a truncated number on
> > overflow.
>
> That's not at all what the C is about.  The C criterion means that a
> transaction transfers the database from one consistent state to another.  To
> my knowledge, MySQL does that.  On its way there, it silently alters data
> that would violate this consistency criterion, but this does not affect the
> fulfillment of the ACID criteria.
>

so the problem is that MySQL _forces_ a consistent state but in the
process it violates the integrity of the data

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Thu, 2006-01-05 at 11:39, John Dean wrote:
> At 16:38 05/01/2006, Stephen Frost wrote:
> >* Russ Brown (pickscrape@gmail.com) wrote:
> > > Oh, that's a long story. We're a MySQL house that I've been trying to
> > > convert to PostgreSQL one way or the other for ages (with no success as
> > > yet). Note that the argument isn't about which letter the type
> > > truncation applies to, but whether it actually has anything to do
> > > with ACID at all in the first place. The key for me is that the result
> > of this argument has an
> > > effect on the question: "Is MySQL ACID compliant". If I'm right, it's
> > > not (which has political strategic benefits to me).
> >
> >An even better thing to point out is that a DBA recommending MySQL isn't
> >a DBA at all. :)
> >
> >         Enjoy,
> >
> >                 Stephen
>
> I used to work for MySQL (a job's a job after all) and I say in all honesty
> that MySQL is not ACID compliant. Furthermore, MySQL is so lacked in
> functionality that it should be used for anything but the simplest of
> solutions. A database engine that does not support referential integrity,
> triggers, stored procedures, user defined types, etc should not be taken
> seriously

PHP 5.0 has most of those features now.  It's just the inability of the
DBA to force things like certain tables to be used that I hate about it.

That and even in V 5 it sill ignores row level foreign key definitions
(they have to be done at the end of the column list) silently.

I bet in another year or two MySQL will be breathing down the neck of
PostgreSQL V 6.5.3 in terms of features and proper operation.

On Thu, 2006-01-05 at 13:24, Scott Marlowe wrote:
> On Thu, 2006-01-05 at 11:39, John Dean wrote:
> > At 16:38 05/01/2006, Stephen Frost wrote:
> > >* Russ Brown (pickscrape@gmail.com) wrote:
> > > > Oh, that's a long story. We're a MySQL house that I've been trying to
> > > > convert to PostgreSQL one way or the other for ages (with no success as
> > > > yet). Note that the argument isn't about which letter the type
> > > > truncation applies to, but whether it actually has anything to do
> > > > with ACID at all in the first place. The key for me is that the result
> > > of this argument has an
> > > > effect on the question: "Is MySQL ACID compliant". If I'm right, it's
> > > > not (which has political strategic benefits to me).
> > >
> > >An even better thing to point out is that a DBA recommending MySQL isn't
> > >a DBA at all. :)
> > >
> > >         Enjoy,
> > >
> > >                 Stephen
> >
> > I used to work for MySQL (a job's a job after all) and I say in all honesty
> > that MySQL is not ACID compliant. Furthermore, MySQL is so lacked in
> > functionality that it should be used for anything but the simplest of
> > solutions. A database engine that does not support referential integrity,
> > triggers, stored procedures, user defined types, etc should not be taken
> > seriously
>
> PHP 5.0 has most of those features now.  It's just the inability of the
> DBA to force things like certain tables to be used that I hate about it.

That should be MySQL 5... ugh.  not enough coffee or sleep lately

pgsql-general-owner@postgresql.org wrote on 01/05/2006 01:59:52 PM:
<snip>
> so the problem is that MySQL _forces_ a consistent state but in the
> process it violates the integrity of the data
>
That is a contradiction in terms.  Data integrity is a requirement of
database consistency.

On 1/5/06, Richard_D_Levine@raytheon.com <Richard_D_Levine@raytheon.com> wrote:
>
>
> pgsql-general-owner@postgresql.org wrote on 01/05/2006 01:59:52 PM:
> <snip>
> > so the problem is that MySQL _forces_ a consistent state but in the
> > process it violates the integrity of the data
> >
> That is a contradiction in terms.  Data integrity is a requirement of
> database consistency.
>
>

maybe, but it seems what happen in MySQL... because it forces a
consistent state (one the fullfill the rules and constraints of the
database) but when doing it it breaks or silently change your data...

so the data can be saved because it's legal data but not correct
data... then it is consistent to the machine but not for you...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Thu, 2006-01-05 at 14:11, Jaime Casanova wrote:
> On 1/5/06, Richard_D_Levine@raytheon.com <Richard_D_Levine@raytheon.com> wrote:
> >
> >
> > pgsql-general-owner@postgresql.org wrote on 01/05/2006 01:59:52 PM:
> > <snip>
> > > so the problem is that MySQL _forces_ a consistent state but in the
> > > process it violates the integrity of the data
> > >
> > That is a contradiction in terms.  Data integrity is a requirement of
> > database consistency.
> >
> >
>
> maybe, but it seems what happen in MySQL... because it forces a
> consistent state (one the fullfill the rules and constraints of the
> database) but when doing it it breaks or silently change your data...
>
> so the data can be saved because it's legal data but not correct
> data... then it is consistent to the machine but not for you...

But it's not consistent.  Imagine we do the one where we take one from
peter and give it to paul.  If paul's account is stored in an int, and
is at 2147483647, and we add one, it does not increment, and it does not
cause an error that will force a transaction to roll back.

Here's a self contained example:

create table test (id int, nom text, bal int) engine=innodb;
insert into test values (1,'paul',2147483647);
insert into test values (2,'peter',2134);
select * from test;
select * from test;
+------+-------+------------+
| id   | nom   | bal        |
+------+-------+------------+
|    1 | paul  | 2147483647 |
|    2 | peter |       2134 |
+------+-------+------------+
begin;
update test set bal=bal-1 where nom='peter';
update test set bal=bal+1 where nom='paul';
commit;
select * from test;
select * from test;
+------+-------+------------+
| id   | nom   | bal        |
+------+-------+------------+
|    1 | paul  | 2147483647 |
|    2 | peter |       2133 |
+------+-------+------------+

We robbed peter, and we didn't even pay paul.

Now, you can turn off this behaviour by default with a startup switch,
but the user can then turn it back on for their session.

Note that one gets a warning when the second update fires.  No error, no
exception.

On Thu, 5 Jan 2006 15:11:49 -0500
Jaime Casanova <systemguards@gmail.com> wrote:

> On 1/5/06, Richard_D_Levine@raytheon.com
> <Richard_D_Levine@raytheon.com> wrote:
> >
> >
> > pgsql-general-owner@postgresql.org wrote on 01/05/2006 01:59:52 PM:
> > <snip>
> > > so the problem is that MySQL _forces_ a consistent state but in
> > > the process it violates the integrity of the data
> > >
> > That is a contradiction in terms.  Data integrity is a requirement
> > of database consistency.
> >
> >
>
> maybe, but it seems what happen in MySQL... because it forces a
> consistent state (one the fullfill the rules and constraints of the
> database) but when doing it it breaks or silently change your data...
>
> so the data can be saved because it's legal data but not correct
> data... then it is consistent to the machine but not for you...
>

See, this is why I was looking for some sort of 'official' definition
of the term, to remove the ambiguity introduced by individual
interpretation. :)

Anyone know who came up with the term in the first place?

--

Russ

Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
> But it's not consistent.  Imagine we do the one where we take one from
> peter and give it to paul.  If paul's account is stored in an int, and
> is at 2147483647, and we add one, it does not increment, and it does not
> cause an error that will force a transaction to roll back.

The effects of the commands on the database are not sensible with respect to
the intent of the commands, but the state of the database is consistent both
before and afterwards with respect to the integrity constraints defined
within the database.  That's what this is all about.  ACID is about
transaction processing, not about SQL data type semantics.

Peter Eisentraut wrote:

>Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
>
>
>>But it's not consistent.  Imagine we do the one where we take one from
>>peter and give it to paul.  If paul's account is stored in an int, and
>>is at 2147483647, and we add one, it does not increment, and it does not
>>cause an error that will force a transaction to roll back.
>>
>>
>
>The effects of the commands on the database are not sensible with respect to
>the intent of the commands, but the state of the database is consistent both
>before and afterwards with respect to the integrity constraints defined
>within the database.  That's what this is all about.  ACID is about
>transaction processing, not about SQL data type semantics.
>
>
This is true, however, one can make a strong case that MySQL still has
issues with ACID complaincy.  For example, how do you have an ACID
compliant full text index in MySQL?  Basically there are features in
MySQL that depend on MyISAM tables and don't provide ACID compliance
where it might be needed.

In essence all of MySQL's data integrity issues aside, it has features
that are not ACID compliant that are ACID compliant with appropriate
add-ons in PostgreSQL.

Best Wishes,
Chris Travers

On Thu, 5 Jan 2006 22:25:21 +0100
Peter Eisentraut <peter_e@gmx.net> wrote:

> Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
> > But it's not consistent.  Imagine we do the one where we take one
> > from peter and give it to paul.  If paul's account is stored in an
> > int, and is at 2147483647, and we add one, it does not increment,
> > and it does not cause an error that will force a transaction to
> > roll back.
>
> The effects of the commands on the database are not sensible with
> respect to the intent of the commands, but the state of the database
> is consistent both before and afterwards with respect to the
> integrity constraints defined within the database.  That's what this
> is all about.  ACID is about transaction processing, not about SQL
> data type semantics.
>

That argument holds true when you consider two key points in a
transaction: before and after. But there is also a third: the
transaction itself. i.e. the actual changes that are being made to the
database. If you take the example given earlier about peter and paul,
yes the database it in a consistent state both before and after the
transaction. But it's *not* in a consistent state when compared with
the transaction itself. The transaction asked that a field value be
incremented, and after the transaction concluded this had not
happened, yet the transaction was committed. ACID
compliance requires that either all or none of the operations in the
transaction happen. In this case one of them does not.

That's how I view it anyway, but from what I can see you can only get
at the 'official' definition if you pay for it.

--

Russ

On Thu, Jan 05, 2006 at 03:00:37PM -0600, Russ Brown wrote:
> See, this is why I was looking for some sort of 'official' definition
> of the term, to remove the ambiguity introduced by individual
> interpretation. :)
>
> Anyone know who came up with the term in the first place?

According to Date in _An Introduction to Database Systems_, 8th ed.,
the source of "ACID" is the 1983 paper "Principles of Transaction-Oriented
Database Recovery" by Theo Härder and Andreas Reuter.  Date has some
interesting things to say about ACID:

  So ACID is a nice acronym -- but do the concepts it represents
  really stand up to close examination?  In this section, we present
  some evidence to suggest that the answer to this question is, in
  general, _no_.  (485)

--
Michael Fuhr

pgsql-general-owner@postgresql.org wrote on 01/05/2006 04:00:37 PM:

> On Thu, 5 Jan 2006 15:11:49 -0500
> Jaime Casanova <systemguards@gmail.com> wrote:
>
> > On 1/5/06, Richard_D_Levine@raytheon.com
> > <Richard_D_Levine@raytheon.com> wrote:
> > >
> > >
> > > pgsql-general-owner@postgresql.org wrote on 01/05/2006 01:59:52 PM:
> > > <snip>
> > > > so the problem is that MySQL _forces_ a consistent state but in
> > > > the process it violates the integrity of the data
> > > >
> > > That is a contradiction in terms.  Data integrity is a requirement
> > > of database consistency.
> > >
> > >
> >
> > maybe, but it seems what happen in MySQL... because it forces a
> > consistent state (one the fullfill the rules and constraints of the
> > database) but when doing it it breaks or silently change your data...
> >
> > so the data can be saved because it's legal data but not correct
> > data... then it is consistent to the machine but not for you...
> >
>
> See, this is why I was looking for some sort of 'official' definition
> of the term, to remove the ambiguity introduced by individual
> interpretation. :)
>
> Anyone know who came up with the term in the first place?

Two Points: Russ is right, nobody has answered his question.  One link is a
Wikipedia entry, and the other a college student's fulfillment of an
assignment (excellent work, but not authoritative, unless there's a
bibliography that I missed containing an authoritative source.)

Second, if the integer that overflowed was a foreign key, would you agree
that consistency has been destroyed?  Answer yes.

Another point: not all database constraints are coded in the database, but
are upheld by the application using the database.  If that application's
SQL malfunctions without a rollback, consistency is shot.

On 1/5/06, Russ Brown <pickscrape@gmail.com> wrote:
> On Thu, 5 Jan 2006 22:25:21 +0100
> Peter Eisentraut <peter_e@gmx.net> wrote:
>
> > Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
> > > But it's not consistent.  Imagine we do the one where we take one
> > > from peter and give it to paul.  If paul's account is stored in an
> > > int, and is at 2147483647, and we add one, it does not increment,
> > > and it does not cause an error that will force a transaction to
> > > roll back.
> >
> > The effects of the commands on the database are not sensible with
> > respect to the intent of the commands, but the state of the database
> > is consistent both before and afterwards with respect to the
> > integrity constraints defined within the database.  That's what this
> > is all about.  ACID is about transaction processing, not about SQL
> > data type semantics.
> >
>
> That argument holds true when you consider two key points in a
> transaction: before and after. But there is also a third: the
> transaction itself. i.e. the actual changes that are being made to the
> database. If you take the example given earlier about peter and paul,
> yes the database it in a consistent state both before and after the
> transaction. But it's *not* in a consistent state when compared with
> the transaction itself. The transaction asked that a field value be
> incremented, and after the transaction concluded this had not
> happened, yet the transaction was committed. ACID
> compliance requires that either all or none of the operations in the
> transaction happen. In this case one of them does not.
>

and that is the A of ACID, Atomicity

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Russ Brown wrote:
> On Thu, 5 Jan 2006 15:11:49 -0500
> Jaime Casanova <systemguards@gmail.com> wrote:

> Anyone know who came up with the term in the first place?
>
FWIW --- as defined by ORACLE  http://www.orafaq.com/glossary/faqglosa.htm

ACID
     The basic properties of a database transaction: Atomicity, Consistency, Isolation, and Durability. All Oracle
transactions 
comply with these properties.

         * Atomicity - The entire sequence of actions must be either completed or aborted. The transaction cannot be
partially 
successful.

         * Consistency - The transaction takes the resources from one consistent state to another.

         * Isolation - A transaction's effect is not visible to other transactions until the transaction is committed.

         * Durability - Changes made by the committed transaction are permanent and must survive system failure.

> ACID
> compliance requires that either all or none of the operations in the
> transaction happen. In this case one of them does not.

So maybe it's Durability that's violated in your example or Atomicity ;-)

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

I think there is a reason that there is no mention at all of ACID in the
ANSI/ISO SQL standard.  It is incredibly hard to achieve.  Transactions
are not enough and primary + foreign keys are not enough and check
constraints are not enough.  You can have all these things operating
correctly but if there is some flaw in the database model, it still
fails to maintain integrity.

And you may have a wonderful model that is 100% correct and all the
relationships defined correctly with every needed constraint in place.
And then someone comes along and truncates a table or does a bulk import
of crappy data and you have lost it.

I think that ACID is a goal that is impossible to guarantee.  But we
should design with that goal in mind.

Anyway, Codd's 12 rules are a much better and far clearer definition of
relational database integrity.  ACID is some sort of nebulous goal.
Codd's 12 rules are a clear definition of how to have relational
integrity.

For instance, if you fail to define a primary key for a table, you have
broken one of Codd's rules and you deserve the bad things that are going
to happen to you down the road.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Scott Ribe
> Sent: Friday, January 06, 2006 11:11 AM
> To: Russ Brown; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 'Official' definition of ACID compliance?
>
> > ACID
> > compliance requires that either all or none of the operations in the
> > transaction happen. In this case one of them does not.
>
> So maybe it's Durability that's violated in your example or Atomicity
;-)
>
> --
> Scott Ribe
> scott_ribe@killerbytes.com
> http://www.killerbytes.com/
> (303) 665-7007 voice
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

Good points. ACID is still useful to discuss wrt to database managers,
because there are databases out there which fail to provide the basics, not
just the kind of corner cases discussed re mySQL. In fact, there's a popular
Mac-derived thing popular in vertical market development, called 4th
Dimension, which fails on all 4 counts ;-)


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

On Thursday 05 January 2006 17:04, Russ Brown wrote:
> On Thu, 5 Jan 2006 22:25:21 +0100
>
> Peter Eisentraut <peter_e@gmx.net> wrote:
> > Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
> > > But it's not consistent.  Imagine we do the one where we take one
> > > from peter and give it to paul.  If paul's account is stored in an
> > > int, and is at 2147483647, and we add one, it does not increment,
> > > and it does not cause an error that will force a transaction to
> > > roll back.
> >
> > The effects of the commands on the database are not sensible with
> > respect to the intent of the commands, but the state of the database
> > is consistent both before and afterwards with respect to the
> > integrity constraints defined within the database.  That's what this
> > is all about.  ACID is about transaction processing, not about SQL
> > data type semantics.
>
> That argument holds true when you consider two key points in a
> transaction: before and after. But there is also a third: the
> transaction itself. i.e. the actual changes that are being made to the
> database. If you take the example given earlier about peter and paul,
> yes the database it in a consistent state both before and after the
> transaction. But it's *not* in a consistent state when compared with
> the transaction itself. The transaction asked that a field value be
> incremented, and after the transaction concluded this had not
> happened, yet the transaction was committed. ACID
> compliance requires that either all or none of the operations in the
> transaction happen. In this case one of them does not.

The problem here is that your asking the value to be incremented however your
definition of the columns data type also asks that it be kept lower than a
given value (based on mysql built in assumptions). Think about if you created
a bigint column in a postgresql table and then defined a trigger/rule to
modify any value larger than int acceptable to the int max. This doesn't
break ACID compliance, your database is in a consitant state and it has done
with the data what it has been defined to do. mysql just does this data
manipulation piece for you (even if you dont want it), but thats not
inherently ACID-incompliant anymore than how they handle timestamp fields.

That said see nearby posts about row level constraints and transactions
involving mixed table types that can't actually be rolled back if you want to
find ways that mysql breaks acid compliance.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL