Thread: '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
Attachment
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