
Demystifying Database Systems: An Introduction to Transaction Isolation Levels - freels
https://fauna.com/blog/introduction-to-transaction-isolation-levels
======
abadid
Glad to see to see this post on HN. I'm the author and happy to respond to
questions in this thread.

------
bennybolide
> 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
A quick search shows data integrity lawsuits do happen:
[https://www.healthcareinfosecurity.com/1-billion-lawsuit-
foc...](https://www.healthcareinfosecurity.com/1-billion-lawsuit-focuses-on-
ehr-data-integrity-concerns-a-10463)

But I'd like to hear from engineers who have seen write skew bugs and other
transaction anomalies cause business issues.

~~~
jhugg
This is some nice work on the issue from Peter Bailis:
[http://www.bailis.org/blog/understanding-weak-isolation-
is-a...](http://www.bailis.org/blog/understanding-weak-isolation-is-a-serious-
problem/)

------
kerblang
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.

~~~
abadid
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.p...](http://www.cs.umd.edu/~abadi/papers/early-write-
visibility.pdf)

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."

~~~
kerblang
What I'm talking about here is not academic database technologies in
laboratories, but the typical mysql/postgres/oracle/sqlserver production world
regular programmers live in. So yes, I know pragmatic strategies for avoiding
deadlocks, but in all the databases I've used, you have to proactively employ
such a strategy. Many if not most of these programmers have at best a
rudimentary knowledge of transactions and/or whether they are even using them
(thanks to ORMs). My concern is that someone reads a blog like this, concludes
that serializable is best, and proceeds towards disastrous consequences.

~~~
ztorkelson
You appear to suggest that programmers with “rudimentary knowledge of
transactions” should prefer lower isolation levels which sacrifice correctness
for performance. If anything is “grossly irresponsible” here, it’s that.

Such isolation levels are notoriously difficult to reason about—even for
experienced practitioners—and their misuse can and does introduce persistent
data anomalies that can be costly to remediate.

Generally speaking, performance issues are significantly easier to diagnose
and resolve than data anomalies, and they may be address in a targeted fashion
as the need arises.

There’s no substitute for thinking. But if I had to prescribe general advice,
it’d be this:

(1) When given the choice, select a modern database system which supports
scalable and efficient serializable and snapshot transaction isolation levels.

(2) Use serializable isolation, by default, for all transactions.

(3) In the event that your transactions are not sufficiently performant, stop
and investigate. Profile the system to identify the bottleneck.

(4) If the bottleneck is contention due to the transaction isolation level,
stop. Assess whether the contention is inherent or whether it is incidental to
the implementation or data model.

(5a) If the contention is incidental, do not lower the isolation level.
Instead, refactor to eliminate the contention point. Congratulations; you are
now done.

(5b) Otherwise, lower the isolation level—only for one or more of the
transaction(s) in question—by a single step. Carefully assess the anomalies
you have now introduced and the ramifications on the system as a whole. Look
for other transactions which could intersect concurrently in time and space.
Implement compensatory controls as necessary to accommodate the new behavior.

(6) Repeat only as necessary to achieve satisfaction.

------
havkom
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
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.

