5 ms·

When is "ACID" ACID? Rarely

63 points by pbailis 14y ago

jpitz 14y ago
Huh? You ought not be depending on the default isolation settings. If your workload needs serializability, set it. Most don't. CAP teaches us that ACID is a spectrum in a distributed system.
- MichaelSalib 14y ago
  Did you read the post? First, it covers both default isolation and maximum available. Second, note that many databases (like Oracle 11g) don't actually give you serializable semantics at ANY setting. Third, most of these products are not distributed systems. Fourth, ACID is not a spectrum for distributed systems; it is basically impossible to apply without sacrificing availability. But that's perhaps not a big deal if our non-distributed DBs don't provide real ACID anyway. The real issue here is that the database world is a cargo cult where ignorant people scream ACID to denigrate new technologies without noticing that most production databases aren't running with anything close to ACID and that major database vendors can't even support ACID.
  - jpitz 14y ago
    I did read the post. It does cover both default and maximum. I didn't dispute that. I called out the notion that anyone ought to be depending on the defaults in the first place, or that SERIALIZABLE as a default was a good choice. Yes, many don't support SERIALIZABLE. Didn't contradict that either. As to whether many of these are or aren't distributed systems: Ingres - has replication. Aerospike - distributed/fault tolerant/blah blah Persistit - nope. appears to be a library. Clustrix - clustered. Greenplum - this is shared-nothing clustered postgres. DB2 for zOS - i have no idea. let's call this one not distributed, for giggles. Informix - same MySQL - lots of replication and HA options MemSQL - replicated MSSQL - replication and federated query modes Nuodb - cloud database management? looks distributed to me. Oracle - dont they have RAC ? Berkeley (x2) - dont know. probably not. PostgreSQL - a few replication options HANA - no idea. lets call it in your favor. ScaleDB - clustered. Volt - shared nothing clustering That's a little over half, by my count. Certainly close to most. "The real issue here is that the database world is a cargo cult where ignorant people scream ACID to denigrate new technologies without noticing that most production databases aren't running with anything close to ACID and that major database vendors can't even support ACID." Some can't. Some do. I'm not screaming. My main message is this: Don't depend on defaults. They differ from vendor to vendor. Understand your workload and use the APPROPRIATE isolation for it. ( edited for formatting and clarity )
    - MichaelSalib 14y ago
      I called out the notion that anyone ought to be depending on the defaults in the first place Regardless of whether you should depend on them, many many people do. Heck, many people don't even understand that there's a choice to be made: after all, everyone knows that Oracle is ACID compliant, right? Yes, many don't support SERIALIZABLE. Didn't contradict that either. Sorry, I was confused by the bit about "If your workload needs serializability, set it" since that's physically impossible on Oracle 11g. Just because a DB has a replication package available (like MySQL) does not mean that it is a distributed system. And the file backed DBs (like Berkeley) are definitely not distributed. Sure, there are some extremely expensive massively parallel DBs in use (like Volt), but the number of deployments for those systems is a drop in the bucket compared with single-node MySQL/Postgres/Oracle/SQLServer/DB2 instances.
      - pbailis 14y ago
        Good points on both sides. One thing I'll point out is that many clustering, HA, and multi-master replication solutions either rely on a single master (what I think Michael means when he says they're not distributed) or don't provide serializability. Things get harder when you're distributed. For example, if you cluster with a sharded master-slave configuration, then, to serialize transactions that span shards/partitions, you'll need to do 2 phase commit or similar between masters for writes and, for most read/write transactions, make sure you don't read from slaves. If you cluster via master-master/active-active, then, for serializability, you'll need locking or some other concurrency control across masters for each shard/partition. Both setups are definitely do-able (if not highly available) but require non-trivial engineering.
      - jpitz 14y ago
        "Regardless of whether you should depend on them, many many people do. Heck, many people don't even understand that there's a choice to be made: after all, everyone knows that Oracle is ACID compliant, right?" Ignorance is not an excuse. It just isn't. "Just because a DB has a replication package available (like MySQL) does not mean that it is a distributed system. And the file backed DBs (like Berkeley) are definitely not distributed. Sure, there are some extremely expensive massively parallel DBs in use (like Volt), but the number of deployments for those systems is a drop in the bucket compared with single-node MySQL/Postgres/Oracle/SQLServer/DB2 instances." I do not understand what point you are trying to make here. Are these systems de-facto non-distributed systems merely because of deployment counts? Is there an objective criteria here I should be aware of?
  - ww520 14y ago
    People scream ACiD on NoSQL's because they fail the easier A, C, or D in various forms, not the harder I part. Having AC&D go a long way in ensuring data correctness.
    - pbailis 14y ago
      Unlike "C" and "A" in "CAP," "AC&D" (specifically, "C") can't be easily separated from "I". Serializability ("I") ensures that database consistency, or maintenance of integrity constraints ("C"), is not violated. While it's possible to get consistency ("C") without serializability (which would give up traditional "I" in favor of a weaker form of isolation), it's often difficult [see http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitrary-note http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitra...].
      - lucian1900 14y ago
        Things like MongoDB fail at A and D, which is what the parent was probably thinking of.
        willlll 14y ago
        It also fails at C for concurrent writes
        lucian1900 14y ago
        Sure, but A and D are the particularly embarrassing ones.
  - zzzeek 14y ago
    I think read committed and repeatable read is "pretty close" to ACID. Certainly compared to a straight up NoSQL system that has close to none of A, C, I or D.
- deleted 14y ago
  [deleted]
redwood 14y ago
When I first read this headline, I thought it had to do with purity of a certain Swiss-made variety that left its mark on San Francisco (and surprisingly, Palo Alto---e.g. Jerry Garcia and Ken Kesey). My mistake
- mutagen 14y ago
  I should have known better, especially in the context of this website. Due to a recent conversation with a friend about ph balance and the acidity of various foods, I thought it was a discussion of the chemical properties of various substances. Extraneous context affecting my initial impression.
stephenpiment 14y ago
I think it's great that someone is taking a detailed look at distinct isolation levels for different systems. It's also worth looking at distinct levels for atomicity, consistency, and durability. One could then define an overall "pH" level for systems. I would take exception to Bailis' statement that "it is impossible to simultaneously achieve high availability and serializability." At FoundationDB, we do exactly that. Stephen Pimentel foundationdb.com
- trollololol 14y ago
  Cool story bro, so you broke CAP too?
- HenryR 14y ago
  I think it's rather clear that he's talking about high availability in the CAP sense, which is precisely the kind of availability FoundationDB (rightly) doesn't claim to achieve (http://foundationdb.com/#CAP http://foundationdb.com/#CAP). BTW, the images are failing to load on that page for me.
akeefer 14y ago
While I appreciate how thorough the article is, it's a bit of a strawman. Pretty much everyone who makes use of a relational database in a professional capacity has to be aware of what transaction isolation level they're using, make their own choice about what to use, and then do things like acquire explicit update locks or do optimistic locking in order to ensure data integrity. But that doesn't mean that the ACID properties are useless merely because you have to do that; it might mean you have to think about a few things more than you'd like to, but it's still a different world than trying to mimic ACID properties in a NoSQL database, and there are still fairly hard guarantees about things like consistency that you get with other isolation levels. For example, with read committed or snapshot isolation, I still have transactionality and can be sure that if I issue a sequence of 10 updates in a single transaction and then commit it, any other single query is either seeing the results of all 10 or of none of them. That's an important guarantee in many situations, and it's a guarantee that I can use to make decisions about how I structure my application logic. The author of the post basically seems to treat any isolation level below serializability as some sort of sham perpetrated on the development community, and that's not the case: they're still useful, and they're still something that you can use to build the sorts of application guarantees you want. The mere fact that pretty much every database vendor gives you a choice as to what isolation level to use should be a pretty obvious clue that there's no one-size-fits-all answer there, so harping on the non-existence of a serializable isolation level is somewhat missing the point.
- pbailis 14y ago
  there are still fairly hard guarantees about things like consistency that you get with other isolation levels What do you mean by consistency? I agree that there are many ways to ensure that application integrity constraints are not violated--without using serializability. My point is that, without ACID serializability, you'll have to do some extra work to ensure them in general [1]. The author of the post basically seems to treat any isolation level below serializability as some sort of sham perpetrated on the development community, and that's not the case Weak isolation has a long tradition spanning decades [2] and is hardly a "sham." It's well known that weak isolation doesn't guarantee the ACID properties as traditionally defined. My point is that many databases don't provide ACID as they promise. It's still a different world than trying to mimic ACID properties in a NoSQL database In terms of current offerings, I'm sympathetic to this viewpoint, but you might be surprised how cheaply we can get properties like the transactional atomicity you mention. In general, I'm curious how easily anyone's able to take their application level requirements and map them down into an isolation like "read committed," especially given how awkwardly they're defined [3] and how many corner cases there are. [1] e.g., http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitrary-note http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitra... [2] "Granularity of Locks and Degrees of Consistency in a Shared Data Base," Jim Gray et al., 1976 http://diaswww.epfl.ch/courses/adms07/papers/GrayLocks.pdf http://diaswww.epfl.ch/courses/adms07/papers/GrayLocks.pdf [3] e.g., http://www.bailis.org/blog/when-is-acid-acid-rarely/#weak-note http://www.bailis.org/blog/when-is-acid-acid-rarely/#weak-no...
shariqm 14y ago
Clustrix says it provides repeatable-read because it complies with the ANSI spec. In truth we actually provide something closer to snapshot-isolation by default. Snapshot isolation in summary is a guarantee that all reads made in a transaction will see a consistent state of the database. A transaction will only fail to commit if there is a conflict of two updates. This is a strong isolation level that does have a set of anomalies but most companies don't care about them. Microsoft put out a great paper about snapshot-isolation: http://research.microsoft.com/pubs/69541/tr-95-51.pdf http://research.microsoft.com/pubs/69541/tr-95-51.pdf As the post points out, weaker isolation levels allow for more concurrency which translates to more scalability, which is why we chose our isolation level.

63 points by pbailis 14y ago

jpitz 14y ago

Huh? You ought not be depending on the default isolation settings. If your workload needs serializability, set it. Most don't. CAP teaches us that ACID is a spectrum in a distributed system.

MichaelSalib 14y ago
Did you read the post? First, it covers both default isolation and maximum available. Second, note that many databases (like Oracle 11g) don't actually give you serializable semantics at ANY setting. Third, most of these products are not distributed systems. Fourth, ACID is not a spectrum for distributed systems; it is basically impossible to apply without sacrificing availability. But that's perhaps not a big deal if our non-distributed DBs don't provide real ACID anyway. The real issue here is that the database world is a cargo cult where ignorant people scream ACID to denigrate new technologies without noticing that most production databases aren't running with anything close to ACID and that major database vendors can't even support ACID.
- jpitz 14y ago
  I did read the post. It does cover both default and maximum. I didn't dispute that. I called out the notion that anyone ought to be depending on the defaults in the first place, or that SERIALIZABLE as a default was a good choice. Yes, many don't support SERIALIZABLE. Didn't contradict that either. As to whether many of these are or aren't distributed systems: Ingres - has replication. Aerospike - distributed/fault tolerant/blah blah Persistit - nope. appears to be a library. Clustrix - clustered. Greenplum - this is shared-nothing clustered postgres. DB2 for zOS - i have no idea. let's call this one not distributed, for giggles. Informix - same MySQL - lots of replication and HA options MemSQL - replicated MSSQL - replication and federated query modes Nuodb - cloud database management? looks distributed to me. Oracle - dont they have RAC ? Berkeley (x2) - dont know. probably not. PostgreSQL - a few replication options HANA - no idea. lets call it in your favor. ScaleDB - clustered. Volt - shared nothing clustering That's a little over half, by my count. Certainly close to most. "The real issue here is that the database world is a cargo cult where ignorant people scream ACID to denigrate new technologies without noticing that most production databases aren't running with anything close to ACID and that major database vendors can't even support ACID." Some can't. Some do. I'm not screaming. My main message is this: Don't depend on defaults. They differ from vendor to vendor. Understand your workload and use the APPROPRIATE isolation for it. ( edited for formatting and clarity )
  - MichaelSalib 14y ago
    I called out the notion that anyone ought to be depending on the defaults in the first place Regardless of whether you should depend on them, many many people do. Heck, many people don't even understand that there's a choice to be made: after all, everyone knows that Oracle is ACID compliant, right? Yes, many don't support SERIALIZABLE. Didn't contradict that either. Sorry, I was confused by the bit about "If your workload needs serializability, set it" since that's physically impossible on Oracle 11g. Just because a DB has a replication package available (like MySQL) does not mean that it is a distributed system. And the file backed DBs (like Berkeley) are definitely not distributed. Sure, there are some extremely expensive massively parallel DBs in use (like Volt), but the number of deployments for those systems is a drop in the bucket compared with single-node MySQL/Postgres/Oracle/SQLServer/DB2 instances.
    - pbailis 14y ago
      Good points on both sides. One thing I'll point out is that many clustering, HA, and multi-master replication solutions either rely on a single master (what I think Michael means when he says they're not distributed) or don't provide serializability. Things get harder when you're distributed. For example, if you cluster with a sharded master-slave configuration, then, to serialize transactions that span shards/partitions, you'll need to do 2 phase commit or similar between masters for writes and, for most read/write transactions, make sure you don't read from slaves. If you cluster via master-master/active-active, then, for serializability, you'll need locking or some other concurrency control across masters for each shard/partition. Both setups are definitely do-able (if not highly available) but require non-trivial engineering.
    - jpitz 14y ago
      "Regardless of whether you should depend on them, many many people do. Heck, many people don't even understand that there's a choice to be made: after all, everyone knows that Oracle is ACID compliant, right?" Ignorance is not an excuse. It just isn't. "Just because a DB has a replication package available (like MySQL) does not mean that it is a distributed system. And the file backed DBs (like Berkeley) are definitely not distributed. Sure, there are some extremely expensive massively parallel DBs in use (like Volt), but the number of deployments for those systems is a drop in the bucket compared with single-node MySQL/Postgres/Oracle/SQLServer/DB2 instances." I do not understand what point you are trying to make here. Are these systems de-facto non-distributed systems merely because of deployment counts? Is there an objective criteria here I should be aware of?
- ww520 14y ago
  People scream ACiD on NoSQL's because they fail the easier A, C, or D in various forms, not the harder I part. Having AC&D go a long way in ensuring data correctness.
  - pbailis 14y ago
    Unlike "C" and "A" in "CAP," "AC&D" (specifically, "C") can't be easily separated from "I". Serializability ("I") ensures that database consistency, or maintenance of integrity constraints ("C"), is not violated. While it's possible to get consistency ("C") without serializability (which would give up traditional "I" in favor of a weaker form of isolation), it's often difficult [see http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitrary-note http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitra...].
    - lucian1900 14y ago
      Things like MongoDB fail at A and D, which is what the parent was probably thinking of.
      - willlll 14y ago
        It also fails at C for concurrent writes
        lucian1900 14y ago
        Sure, but A and D are the particularly embarrassing ones.
- zzzeek 14y ago
  I think read committed and repeatable read is "pretty close" to ACID. Certainly compared to a straight up NoSQL system that has close to none of A, C, I or D.
deleted 14y ago
[deleted]

redwood 14y ago

When I first read this headline, I thought it had to do with purity of a certain Swiss-made variety that left its mark on San Francisco (and surprisingly, Palo Alto---e.g. Jerry Garcia and Ken Kesey). My mistake

mutagen 14y ago
I should have known better, especially in the context of this website. Due to a recent conversation with a friend about ph balance and the acidity of various foods, I thought it was a discussion of the chemical properties of various substances. Extraneous context affecting my initial impression.

stephenpiment 14y ago

I think it's great that someone is taking a detailed look at distinct isolation levels for different systems. It's also worth looking at distinct levels for atomicity, consistency, and durability. One could then define an overall "pH" level for systems. I would take exception to Bailis' statement that "it is impossible to simultaneously achieve high availability and serializability." At FoundationDB, we do exactly that. Stephen Pimentel foundationdb.com

trollololol 14y ago
Cool story bro, so you broke CAP too?
HenryR 14y ago
I think it's rather clear that he's talking about high availability in the CAP sense, which is precisely the kind of availability FoundationDB (rightly) doesn't claim to achieve (http://foundationdb.com/#CAP http://foundationdb.com/#CAP). BTW, the images are failing to load on that page for me.

akeefer 14y ago

While I appreciate how thorough the article is, it's a bit of a strawman. Pretty much everyone who makes use of a relational database in a professional capacity has to be aware of what transaction isolation level they're using, make their own choice about what to use, and then do things like acquire explicit update locks or do optimistic locking in order to ensure data integrity. But that doesn't mean that the ACID properties are useless merely because you have to do that; it might mean you have to think about a few things more than you'd like to, but it's still a different world than trying to mimic ACID properties in a NoSQL database, and there are still fairly hard guarantees about things like consistency that you get with other isolation levels. For example, with read committed or snapshot isolation, I still have transactionality and can be sure that if I issue a sequence of 10 updates in a single transaction and then commit it, any other single query is either seeing the results of all 10 or of none of them. That's an important guarantee in many situations, and it's a guarantee that I can use to make decisions about how I structure my application logic. The author of the post basically seems to treat any isolation level below serializability as some sort of sham perpetrated on the development community, and that's not the case: they're still useful, and they're still something that you can use to build the sorts of application guarantees you want. The mere fact that pretty much every database vendor gives you a choice as to what isolation level to use should be a pretty obvious clue that there's no one-size-fits-all answer there, so harping on the non-existence of a serializable isolation level is somewhat missing the point.

pbailis 14y ago
there are still fairly hard guarantees about things like consistency that you get with other isolation levels What do you mean by consistency? I agree that there are many ways to ensure that application integrity constraints are not violated--without using serializability. My point is that, without ACID serializability, you'll have to do some extra work to ensure them in general [1]. The author of the post basically seems to treat any isolation level below serializability as some sort of sham perpetrated on the development community, and that's not the case Weak isolation has a long tradition spanning decades [2] and is hardly a "sham." It's well known that weak isolation doesn't guarantee the ACID properties as traditionally defined. My point is that many databases don't provide ACID as they promise. It's still a different world than trying to mimic ACID properties in a NoSQL database In terms of current offerings, I'm sympathetic to this viewpoint, but you might be surprised how cheaply we can get properties like the transactional atomicity you mention. In general, I'm curious how easily anyone's able to take their application level requirements and map them down into an isolation like "read committed," especially given how awkwardly they're defined [3] and how many corner cases there are. [1] e.g., http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitrary-note http://www.bailis.org/blog/when-is-acid-acid-rarely/#arbitra... [2] "Granularity of Locks and Degrees of Consistency in a Shared Data Base," Jim Gray et al., 1976 http://diaswww.epfl.ch/courses/adms07/papers/GrayLocks.pdf http://diaswww.epfl.ch/courses/adms07/papers/GrayLocks.pdf [3] e.g., http://www.bailis.org/blog/when-is-acid-acid-rarely/#weak-note http://www.bailis.org/blog/when-is-acid-acid-rarely/#weak-no...

shariqm 14y ago

Clustrix says it provides repeatable-read because it complies with the ANSI spec. In truth we actually provide something closer to snapshot-isolation by default. Snapshot isolation in summary is a guarantee that all reads made in a transaction will see a consistent state of the database. A transaction will only fail to commit if there is a conflict of two updates. This is a strong isolation level that does have a set of anomalies but most companies don't care about them. Microsoft put out a great paper about snapshot-isolation: http://research.microsoft.com/pubs/69541/tr-95-51.pdf http://research.microsoft.com/pubs/69541/tr-95-51.pdf As the post points out, weaker isolation levels allow for more concurrency which translates to more scalability, which is why we chose our isolation level.