Do we fear the serializable isolation level more than we fear subtle bugs (2024)

My experience just differs from you so much. Two of my employers extensively use serializable isolation and the code to handle retries is automatic. It helps that there are other reasons for retrying the transaction, like load shedding. The only bug I’ve observed is when a colleague forgot that the transaction could retry and the code wrote some logs to disk multiple times; it wasn’t even a correctness bug just a performance bug.

> At every commit, you need handle the possibility of a serialization exception and retry the transaction.

Yeah, but it seems so strange to me. Imagine if a database simply executed all transactions serially. Then there would be no serialization anomalies (though it would be slow, yada yada). So it seems serializable isolation presents a facade of serial execution, but only like, half a facade. You have to deal with the leak in the abstraction yourself and it's surprising to everyone who hears about it for the first time. I wonder why this choice was made.

A bigger issue is scalability. Note that this blog post claims YugabyteDB uses serializable as the default isolation level, but if we look at the latest version of their docs it seems they backed off that and at some point the default became snapshot isolation, and then they backed off again and for new databases the default is read committed i.e. the same as Postgres and Oracle:

https://docs.yugabyte.com/stable/explore/transactions/isolat...

So they do allow write skew anomalies.

This happens because serializability requires the database to track every read and intersect them with every write, meaning every node in a distributed cluster must be aware of what every other node is doing. That hurts scalability and performance badly; it's one reason Oracle doesn't support true serializability (the other being that almost no existing DB backed apps can tolerate transaction aborts, as you observe).

There are other issues too. With pessimistic locking you can get deadlocks, but if you deadlock your app the database can detect that and get it moving again by rolling one of the transactions back. It surfaces to the user as a bug that they can easily monitor for and diagnose. With optimistic locking+retry, which serializability basically requires, you can get livelock. Livelock is terrible, the cluster appears to be busy doing useful work but just gets slower and slower. There's not good ways to automatically detect it, at least not in any app framework I've ever encountered.

Disclosure: in the Oracle DB group, opinions are my own and not that of the company. I actually argue with my manager about this topic sometimes, lol.

In SQLite all writes are serialisable by default and it scales really well. I think having a single writer is what makes the big difference here.

I recently encountered a query that deadlocked on itself because it used a parallel execution plan and updated multiple indexes in a manner that the different threads could conflict with each other.

Naively one would expect that no individual UPDATE or INSERT statement could deadlock in isolation… but there you go.

If that is possible, the possibilities across multiple concurrent data mutating queries are beyond human comprehension!

Serializable should absolutely be the default!

Similarly, all columns should be automatically indexed to at least some degree, like Postgres BRIN indexes at a minimum.

Time and experience have shown that the vast majority of developers are pathologically unable to properly define all required indexes ahead of time.

If you're going to use serialisable isolation level, why bother using a traditional RDBMS at all? At that point you're better off using a simpler datastore.

In my experience, the performance hit is so bad that it's not feasible to use that way. It's also not strictly safer behavior-wise because retries can trip people up.

My experience is that there’s a percentage of developers who get it right and intuitively understand the serializable isolation level.

And database performance is a crapshoot. By “crapshoot”, I mean you mix some developers, data, and database technologies together and you get all sorts of weird performance profiles. Why sacrifice safety for performance, when you’re going to get performance problems anyway? It’s a lot easier to turn down the isolation level for a transaction, and it’s a lot harder to fix an unknown amount of inconsistent data in your database.

(The problem is that there are also a big cohort of developers who aren’t prepared to make their transactions retryable, and they’re writing their code in languages which don’t enforce transaction safety. By “languages which don’t enforce transaction safety”, I mean “languages other than Haskell”.)

That’s the same mentality C and C++ take. Even if it was only 20%, that’s a non trivial amount of vulnerabilities that have nothing to do with the code.

Too many people discovered databases with MySQL back in the day and didn't even know transactions were possible. And now 25 years later one of those people is likely a senior dev and 'the db guy' on a team of people who learn SQL by example. Or now never learnt it, relying on an LLM trained on decades of dodgy PHP code.

The need for retries is a not a small performance hit.

This is a rabbit hole worth going down, but it shouldn't be the default. This is a classic case of Chesterton's Fence.

> With serializable, you need to be a little careful not to have hot rows. Avoid them by sharding commonly written values

Unfortunately, serializable isolation requires detecting or preventing read-write conflicts (i.e. one transaction writing a row that a concurrent transaction has read). This is the performance impact of serializability: you need to be very careful what you read, because if you read too many rows you prevent any concurrent transactions from updating those same rows. Read-only transactions are OK (because MVCC), and read-only tables are OK (because there's no read-write conflict if a table is mostly read only), but tables that are both written and heavily read are where you get performance problems.

With snapshot isolation (e.g. Oracle's serializable, Postgres repeatable read), only write-write conflicts matter. There it doesn't matter what a transaction reads, and reads never need to block (or abort) writers. So what you say is true for snapshot, but not for serializable.

Interestingly, serializable's lack of need to detect write-write conflicts means that (in some implementations) it can be faster than snapshot for blind writes (i.e. anything that's not a read-modify-write under the covers).

Right.

As an example: Oracle and PostgreSQL don't have dirty reads: READ UNCOMMITTED does nothing. MySQL's concurrency model depends on the engine.

Tbh I always forget the specifics soon after reading them. Basically you can do an atomic UPDATE WHERE if there are no subqueries involved. 90% of the time that's good enough, and for anything else I end up refreshing on features like SELECT FOR UPDATE.

Well also I know Postgres UNIQUE indexes provide additional locking. Like you can do an INSERT... WHERE NOT EXISTS or INSERT... ON CONFLICT that is guaranteed to succeed.

I think the argument is that thinking through the exact implications of your particular database's concurrency model for each query you write is too much to ask of generalist software engineers; if it's not kept down to a small auditable surface, mistakes will occur pervasively. Therefore, serializable (i.e., do it the obvious way without allowing tricky edge-case states) should be the default, and should be departed from only when performance demands it in a specific case, with careful analysis in those few cases to ensure correctness. (This is pretty closely analogous to the argument for memory-safe programming languages.)

The one complication is that this does mean applications need to be prepared for queries to need to be retried, and while this is reasonably straightforward in most cases (and can in principle be enforced with static analysis), it's not always done today, which makes it hard to change the defaults.

And this is why most of the popular database libraries default to 'read committed'. Any higher and your library and scripts became DB specific rather than working with most of the engines supporting ODBC (although even that was tricky, given the different SQL dialects out there).

The isolation level that the DB engine defaults to doesn't matter. That is for backwards compatibility with the 90s. What matters is what isolation level the connection library you are using defaults to. Unfortunately, that too is often backwards compatible with the 90s (aka ODBC).