The difference between isolation levels and consistency levels

matt_d | 110 points

The Herlihy-Wing paper defining linearizability is a good read https://cs.brown.edu/~mph/HerlihyW90/p463-herlihy.pdf and explains the difference with good examples, although it might be too mathy/formal for most people's taste. An interesting result in that paper is that the implementations of what Abadi calls isolation notions (which Herlihy and Wing call multi-object correctness criteria) are not modular - at least for (strict) serializability. For example, if two systems implement serializability using two-phase locking and multi-version concurrency control respectively, then they can't be modularly composed to get a serializable global system for transactions that span objects from both systems.

oggy | 5 years ago

> For many years, database users did not have to simultaneously understand the concept of isolation levels and consistency levels

This statement might technically be true, but it hasn't been the case for many years, including much of the time that "CAP Theorem" has been a part of popular discourse in industry. If you take a traditional RDBMS with ACID transactions and ANSI defined isolation levels such as Postgres without extensions (single master) you can still have inconsistent reads if your architecture includes async streaming replicas used for distributing reads. This feature was added in version 9.0 - 10 years ago. Microsoft SQL Server had it at least a decade before that.

jeremyjh | 5 years ago

While interviewing candidates at my last gig, I was taken aback by how many of them confused consistency with isolation in databases. Most who came from a Mongo background did not understand isolation at all and confused it with consistency.

Concepts such as Optimistic locking and Pessimistic locking are also not well-known any more, either, much to my dismay :(

cwojno | 5 years ago

[Shameless Plug] https://medium.com/open-factory/nosql-newsql-a-smorgasboard-...

The article throws some light on consistency and isolation levels in modern distributed relational databases (aka NewSQL)

thekhatribharat | 5 years ago