Re: 'Official' definition of ACID compliance?

On Thu, 2006-01-05 at 14:11, Jaime Casanova wrote:
> On 1/5/06, Richard_D_Levine(at)raytheon(dot)com <Richard_D_Levine(at)raytheon(dot)com> wrote:
> >
> >
> > pgsql-general-owner(at)postgresql(dot)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.