'Official' definition of ACID compliance?

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&gt;
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