Re: 'Official' definition of ACID compliance?

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