6 ms·

Demystifying Database Systems: An Introduction to Transaction Isolation Levels

202 points by freels 7y ago

bennybolide 7y ago
> If any of my readers are aware of any real lawsuits that came from application developers who believed they were getting a SERIALIZABLE isolation level, but experienced write skew anomalies in practice Doubt this ever really happened. Can hardly imagine debating serializability in a court of law Oracle v. Google was bad enough.
- jchrisa 7y ago
  A quick search shows data integrity lawsuits do happen: https://www.healthcareinfosecurity.com/1-billion-lawsuit-focuses-on-ehr-data-integrity-concerns-a-10463 https://www.healthcareinfosecurity.com/1-billion-lawsuit-foc... But I'd like to hear from engineers who have seen write skew bugs and other transaction anomalies cause business issues.
  - jhugg 7y ago
    This is some nice work on the issue from Peter Bailis: http://www.bailis.org/blog/understanding-weak-isolation-is-a-serious-problem/ http://www.bailis.org/blog/understanding-weak-isolation-is-a...
abadid 7y ago
Glad to see to see this post on HN. I'm the author and happy to respond to questions in this thread.
kerblang 7y ago
It seems grossly irresponsible to encourage the use of serializable without even mentioning deadlocks. I guess you won't have deadlocks if your db interprets as serializable as "lock the whole database on transaction start", but that means literally no concurrency whatsoever, which also seems like a grossly irresponsible recommendation. I dunno, maybe I didn't get the memo that the database of the future will be single-threaded.
- dmm 7y ago
  In postgresql at least, serializable cannot deadlock. It uses predicate locks to provide serializable consistency with high levels of concurrency. You can have serialization failures though.
  - basetop 7y ago
    I'm not too familiar with postgresql, but isn't that serializable snapshot rather than serializable? I'm pretty sure all RDMBs have deadlocks in serializable. But in serialiable snapshot, a transaction doesn't deadlock, but simply fails.
    - convolvatron 7y ago
      a transaction system in which its impossible for transactions to ever fail kind of isn't a transaction system. see above comment from abadid - its entirely possible to impose a global ordering of all transactions either up front or retroactively without admitting deadlocks.
    - freels 7y ago
      Serializable Snapshot an implementation strategy for providing Serializable isolation. In PostgreSQL if you ask for Serializable you get SSI.
- abadid 7y ago
  As mentioned in the post: "There are several ways to achieve [serializability] — such as via locking, validation, or multi-versioning." Deadlock happens under some, but not all implementations of serializability via locking. There have been several database systems developed in my lab that use locking to achieve serializability, but yet never deadlock. Examples include: (1) Calvin: http://www.cs.umd.edu/~abadi/papers/calvin-sigmod12.pdf http://www.cs.umd.edu/~abadi/papers/calvin-sigmod12.pdf (2) Orthrus: http://www.cs.umd.edu/~abadi/papers/orthrus-sigmod16.pdf http://www.cs.umd.edu/~abadi/papers/orthrus-sigmod16.pdf (3) PWV: http://www.cs.umd.edu/~abadi/papers/early-write-visibility.pdf http://www.cs.umd.edu/~abadi/papers/early-write-visibility.p... Bottom line: serializability does not necessarily mean deadlock. Deadlock can be avoided via non-locking implementations, or even in well-designed locking implementations. One of the points in the conclusion of the post warrants being reiterated at this point: "If you find that the cost of serializable isolation in your system is prohibitive, you should probably consider using a different database system earlier than you consider settling for a reduced isolation level."
havkom 7y ago
In many real world applications using common databases you do not always need “transaction safety”, in particular when reading data for statistical purposes. Performance gains and not blocking the database for many other readers and writers will in many scenarios outweigh the possibly of reading uncommitted/dirty data. Use: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (whether you are are querying from within a transaction or not) and you may be surprised how this may solve many of your performance problems in your application/service.
- freels 7y ago
  This advice is highly implementation specific and unnecessarily dangerous. For read only transactions, a well designed system will be able to provide SNAPSHOT isolation with little negligible perf impact compared to READ UNCOMMITTED. When combined with SERIALIZABLE write transactions, reads at SNAPSHOT are equivalent to SERIALIZABLE. For transactions which must write, the entire point of this article is that anything less than SERIALIZABLE leaves you subject to anomalies whose affects on your data can be very difficult to predict beyond trivial scenarios. If you must lower consistency for performance's sake, then so be it, but I would strongly argue that should be the exception, not the rule, and not be a decision made lightly.
- stubish 7y ago
  It can certainly solve performance problems, in much the same way as turning of fsync. Even a contractor contracted specifically to fix a performance problem shouldn't do this without understanding how this will affect the particular application, or they open themselves to professional malfeasance (if noticed; otherwise they will likely get the contract to fix the data corruption problem since they did such a good job last time...)

202 points by freels 7y ago

> If any of my readers are aware of any real lawsuits that came from application developers who believed they were getting a SERIALIZABLE isolation level, but experienced write skew anomalies in practice Doubt this ever really happened. Can hardly imagine debating serializability in a court of law Oracle v. Google was bad enough.

jchrisa 7y ago
A quick search shows data integrity lawsuits do happen: https://www.healthcareinfosecurity.com/1-billion-lawsuit-focuses-on-ehr-data-integrity-concerns-a-10463 https://www.healthcareinfosecurity.com/1-billion-lawsuit-foc... But I'd like to hear from engineers who have seen write skew bugs and other transaction anomalies cause business issues.
- jhugg 7y ago
  This is some nice work on the issue from Peter Bailis: http://www.bailis.org/blog/understanding-weak-isolation-is-a-serious-problem/ http://www.bailis.org/blog/understanding-weak-isolation-is-a...

abadid 7y ago

Glad to see to see this post on HN. I'm the author and happy to respond to questions in this thread.

kerblang 7y ago

It seems grossly irresponsible to encourage the use of serializable without even mentioning deadlocks. I guess you won't have deadlocks if your db interprets as serializable as "lock the whole database on transaction start", but that means literally no concurrency whatsoever, which also seems like a grossly irresponsible recommendation. I dunno, maybe I didn't get the memo that the database of the future will be single-threaded.

dmm 7y ago
In postgresql at least, serializable cannot deadlock. It uses predicate locks to provide serializable consistency with high levels of concurrency. You can have serialization failures though.
- basetop 7y ago
  I'm not too familiar with postgresql, but isn't that serializable snapshot rather than serializable? I'm pretty sure all RDMBs have deadlocks in serializable. But in serialiable snapshot, a transaction doesn't deadlock, but simply fails.
  - convolvatron 7y ago
    a transaction system in which its impossible for transactions to ever fail kind of isn't a transaction system. see above comment from abadid - its entirely possible to impose a global ordering of all transactions either up front or retroactively without admitting deadlocks.
  - freels 7y ago
    Serializable Snapshot an implementation strategy for providing Serializable isolation. In PostgreSQL if you ask for Serializable you get SSI.
abadid 7y ago
As mentioned in the post: "There are several ways to achieve [serializability] — such as via locking, validation, or multi-versioning." Deadlock happens under some, but not all implementations of serializability via locking. There have been several database systems developed in my lab that use locking to achieve serializability, but yet never deadlock. Examples include: (1) Calvin: http://www.cs.umd.edu/~abadi/papers/calvin-sigmod12.pdf http://www.cs.umd.edu/~abadi/papers/calvin-sigmod12.pdf (2) Orthrus: http://www.cs.umd.edu/~abadi/papers/orthrus-sigmod16.pdf http://www.cs.umd.edu/~abadi/papers/orthrus-sigmod16.pdf (3) PWV: http://www.cs.umd.edu/~abadi/papers/early-write-visibility.pdf http://www.cs.umd.edu/~abadi/papers/early-write-visibility.p... Bottom line: serializability does not necessarily mean deadlock. Deadlock can be avoided via non-locking implementations, or even in well-designed locking implementations. One of the points in the conclusion of the post warrants being reiterated at this point: "If you find that the cost of serializable isolation in your system is prohibitive, you should probably consider using a different database system earlier than you consider settling for a reduced isolation level."

havkom 7y ago

In many real world applications using common databases you do not always need “transaction safety”, in particular when reading data for statistical purposes. Performance gains and not blocking the database for many other readers and writers will in many scenarios outweigh the possibly of reading uncommitted/dirty data. Use: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (whether you are are querying from within a transaction or not) and you may be surprised how this may solve many of your performance problems in your application/service.

freels 7y ago
This advice is highly implementation specific and unnecessarily dangerous. For read only transactions, a well designed system will be able to provide SNAPSHOT isolation with little negligible perf impact compared to READ UNCOMMITTED. When combined with SERIALIZABLE write transactions, reads at SNAPSHOT are equivalent to SERIALIZABLE. For transactions which must write, the entire point of this article is that anything less than SERIALIZABLE leaves you subject to anomalies whose affects on your data can be very difficult to predict beyond trivial scenarios. If you must lower consistency for performance's sake, then so be it, but I would strongly argue that should be the exception, not the rule, and not be a decision made lightly.
stubish 7y ago
It can certainly solve performance problems, in much the same way as turning of fsync. Even a contractor contracted specifically to fix a performance problem shouldn't do this without understanding how this will affect the particular application, or they open themselves to professional malfeasance (if noticed; otherwise they will likely get the contract to fix the data corruption problem since they did such a good job last time...)