Do we fear the serializable isolation level more than we fear subtle bugs?

> According to the paper, “Of the 22 vulnerabilities, five were level-based, meaning that the default weak isolation level led to the anomalies behind the vulnerabilities. The remaining 17 were scope-based, meaning that the database accesses were not properly encapsulated in transactions and concurrent API requests could trigger the vulnerability independent of the level of isolation provided by the database backend.”

I don't want to commit to a real opinion, but the cynic in me sees a bitter lesson you could take from this is that the database should default to a low isolation level--the damn developers aren't even using transactions right, so why waste performance handling transactions in the strictest possible way?

Was curious about the Flexcoin hack, but the article wasn't loading, so here's an archive: https://web.archive.org/web/20240423000007/https://hackingdi... Supposedly it was this simple:

  mybalance = database.read("account-number")
  newbalance = mybalance - amount
  database.write("account-number", newbalance)
  dispense_cash(amount)   // or send bitcoins to customer

and MongoDB didn't even have a way to do this atomically? An RDBMS with read-committed would handle this fine if you did "read for update" on that row.

I would argue that not using serialization isolation level by default is like not using a memory safe programming language by default.

Sure, sometimes it's too slow, but it should be the default.

Very few people can write correct database code at the other serialization levels. Most think they can, but it's harder than correct multi-threading, because databases do weird unintuitive things for performance.

You may not need serializable isolation level, but you must understand the concurrency model of your database and the implications of it, and realize that they are not all the same. Oracle, Postgres, MySQL, SQL Server are all different.

> Surprisingly, there are many more stories and publications about bugs caused by weak isolation levels than cases where stronger isolation levels caused impractically low performance.

I expected the article to substantiate the claim that serializable brings a large performance hit as in my experience it isn't so. The article basically makes the same point.

With serializable, you need to be a little careful not to have hot rows. Avoid them by sharding commonly written values. Another way to improve performance is to use true time for ordering non read-then-write transactions. It's a little finicky if the database doesn't provide such guarantees out of the box. Take Google's Spanner as an example. It offers the serializable isolation level and it's pretty performant (as long as you account for hot spots).

I only recently learned about serializable transactions and it seems bonkers that this is not the default. It makes a lot of sense combined with the event sourcing pattern. I believe it allows you to query for state in the decide function and then emit events safely without having to implement aggregates or versioning (aka you have "dynamic consistency boundaries"). The crucial part is that if any of the queried information changes before the event is emitted the transaction fails and business logic has to be retried until you get a conclusive answer.

Was curious about the Flexcoin hack, but the article wasn't loading, so here's an archive: https://web.archive.org/web/20240423000007/https://hackingdi... Supposedly it was this simple:

  mybalance = database.read("account-number")
  newbalance = mybalance - amount
  database.write("account-number", newbalance)
  dispense_cash(amount)   // or send bitcoins to customer

and MongoDB didn't even have a way to do this atomically? An RDBMS with read-committed would handle this fine if you did "read for update" on that row.

I would argue that not using serialization isolation level by default is like not using a memory safe programming language by default.

Sure, sometimes it's too slow, but it should be the default.

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.

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.

> Surprisingly, there are many more stories and publications about bugs caused by weak isolation levels than cases where stronger isolation levels caused impractically low performance.

I expected the article to substantiate the claim that serializable brings a large performance hit as in my experience it isn't so. The article basically makes the same point.

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

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.

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.

I've definitely experienced the opposite, where indexes run rampant and are NN times the size of data (for the entire database!)

That being said also "required indexes ahead of time" is impossible - because query patterns change and things get released unless you mean something like "the required indexes for our obvious query patterns we just freaking released.

I dont think most columns need to be indexed because that is mostly nonsense, most columns never have a where clause applied to them or are sorted on themselves, so an index provides ??? value.

You are supposed to define indexes based on how you query the data, not ahead of time.

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

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.

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.

Right.

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

You are supposed to define indexes based on how you query the data, not ahead of time.

"Supposed to" is doing a lot of heavy lifting here.

As a consultant I come across a lot of CotS software, in-house or otherwise bespoke software, etc. Roughly 40% of the former has 100% of the minimum required number of indexes and approximately 5% of the latter. By "minimum", I mean the indexes required to avoid full scans of tables that will become large enough for this to be a problem in production.

"Disciple doesn't scale." is one of my favourite sayings now, for a reason!

1) Developers almost always work with toy data, and are hence insulated from poor indexing decisions. Problems turn up 'x' years from now. It is well established that humans learn poorly when consequences are delayed... by mere hours, let alone years!

2) DBAs and developers often have an adversarial relationship. A common consequence of this workplace dynamic is that developers aren't granted the required access to tune indexes, especially in production, which is where the issues manifest.

3) I've heard anecdotes, including here, along the lines of "XYZ cloud native / webscale database is so much faster than ABC traditional RDBMS!". Very often the difference is just that XYZ auto-indexes by default. CosmosDB, Google Firestore, Kusto, Elastic, Druid, and many columnar formats are in this category of "magically" faster!

I'm now 99% convinced that RDBMS needs to be reinvented for the modern fast-paced, vibe-coded, "I'm a fullstack(lol) dev" world where people simply don't have the bandwidth to pay attention to minutiae like on-disk sort order and filtered secondary indexes. A better fit for today's world would be a system that is: columnar by default like SAP HANA, compressed[1] by default, indexed by default (thanks to being columnar!), serializable by default, and "include batteries" like native queue capabilities so that nobody has to figure out cross-RDBMS complications like distributed transactions, outbox patterns, or deal with the consequences of a DBA rolling back one of two databases to a backup.

I've definitely experienced the opposite, where indexes run rampant and are NN times the size of data (for the entire database!)

I dont think most columns need to be indexed because that is mostly nonsense, most columns never have a where clause applied to them or are sorted on themselves, so an index provides ??? value.

I mean you're not really making use of MVCC etc. at that point. Foreign keys are far less relevant because your transactions are all fully atomic, so it doesn't really matter if your data is in an inconsistent state in the middle of a transaction, and conversely you've got no risk of e.g. adding a reference to a row that another transaction deleted concurrently. Why not just use e.g. Redis at that point?

"Supposed to" is doing a lot of heavy lifting here.

"Disciple doesn't scale." is one of my favourite sayings now, for a reason!

people mostly use RDBMS because they want the advanced querying that SQL provides, not because of the isolation levels

but ignoring that, serializable isolation level means the database acts AS IF the transactions are serial. but most databases in fact will execute them concurently, with careful tracking to make sure they appear serial

people mostly use RDBMS because they want the advanced querying that SQL provides, not because of the isolation levels

TBH I think I've seen more database use than not specifically because it serves as the central race-resolver in a system, because doing that anywhere else is many, many times harder and more mistake-prone. Fancy querying has been much less needed (and is sometimes a significant code smell), and is often fully offloaded into a data warehouse style system to reduce the risk.

Sometimes though, yes definitely. It's hard to claim anything universal at all about databases.

(Unless you mean "being able to choose between different isolation levels", then yes, completely agreed. Very very few use anything but the default, somewhere below serializable, and it always concerns me unless they can describe exactly what they're intentionally allowing and why it's okay for their system. Most cannot.)

Sometimes though, yes definitely. It's hard to claim anything universal at all about databases.

9 min read

Jul 2, 2024

We don’t understand how applications are affected by lower isolation levels.

Maybe READ COMMITTED is good enough or maybe people don’t know how dirty their data actually is…

Andy Pavlo at SIGMOD 2017

Press enter or click to view image in full size

All you need is ACID

Highlights

Database transactions imply ACID properties, where “I” stands for isolation and concurrency control.
The (serializable) isolation property ensures that the result of concurrently executed transactions is the same as if they had been executed in some serial order.
Maintaining the serializable isolation level is not free in terms of performance.
Many DBMSs provide weaker isolation levels for performance reasons, leaving it up to the application developer to choose the proper one. Moreover, a weaker isolation level is often the default in monolithic databases, e.g., “read committed” in PostgreSQL and MySQL. Meanwhile, stronger defaults prevail in distributed databases: “repeatable read” in YugabyteDB and TiDB and “serializable” in CockroachDB and [YDB]YDB.
Weaker isolation levels might cause subtle concurrency bugs. These bugs might introduce security vulnerabilities.
Millions of dollars were stolen, particularly from BTC exchanges, because of concurrency bugs in database-related application logic. We will provide details on multiple cases in the next sections.

In this post, we try to answer two important questions:

Do weaker isolation levels cause concurrency bugs in real applications often enough?
Is the performance penalty of the serializable isolation level reasonable, or is it overrated?

After answering these questions, we concluded that using weaker isolation levels by default is a form of premature optimization and evil. Consider using serializable isolation as the default unless your DBMS is CockroachDB or YDB, where serializable isolation is already the default.

Isolation levels subtleties

Suppose a table with a single column named “color” contains strings “black” or “white”. One user wants to change all “white” colors to “black”, while another user concurrently tries to change “black” to “white”. In other words, there are two concurrent transactions:

--- Transaction 1 Transaction 2
UPDATE t SET color = 'black' UPDATE t SET color = 'white'
WHERE color = 'white'; WHERE color = 'black';

What would be the result of these two transactions? Intuitively, all colors should either become black or white. But in database practice, the proper answer is “it depends on isolation level.”

Usually, when we say “transaction,” we suppose that the transaction meets the ACID safety properties:

Atomicity: either all parts of the transaction are committed or all parts are aborted. Martin Kleppmann suggests calling this property “Abortability” because it reflects the meaning more accurately and avoids confusion between atomic commit/abort and atomic visibility.
Consistency: historically added for a better-sounding acronym and rather application-specific than DBMS-specific.
Isolation: concurrently executed transactions are isolated from each other. The results of transaction execution should look like the transactions have been executed serially, one by one.
Durability: committed data is never lost.

While “Isolation” is supposed to originally mean “serializability”, there are weaker [isolation levels](https://en.wikipedia.org/wiki/Isolation_(database_systems) introduced as a trade-off between performance and safety:

Read uncommitted.
Read committed.
Repeatable read.

Serializable is the default isolation level at least since the SQL:1999 standard, including its recent version SQL:2023 (ISO/IEC 9075:2023). It is also the default isolation level in CockroachDB and YDB. However, many database vendors use weaker isolation levels by default, in particular:

“Read committed” in PostgreSQL and Oracle.
“Repeatable read” in MySQL/InnoDB (there is a subtlety, see below) in YugabyteDB.

Moreover, database vendors provide their own confusing naming conventions. For example, according the documentation “repeatable read” in MySQL/InnoDB provides its guarantees only to read-only transactions. That is why Hermitage notes that “repeatable read” in MySQL/InnoDB is rather “read committed” (“monotonic atomic view”). And Oracle’s “serializable” is actually not serializable, but “repeatable read (snapshot isolation)” (application developers have easy ways to work around this). For details, check this slightly old post or its newer 2022 revision and Hermitage’s page dedicated to Oracle. All this, in some sense, is similar to many Citus-like sharded Postgres solutions, which are not ACID, when it comes to multi-shard transactions.

Get Evgeniy Ivanov’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Now, going back to our initial colorful example, with the “serializable” isolation level, the result is indeed that all values become white or black. However, with “read committed” (the default isolation level in PostgreSQL), some values might change their colors, and some might not. With “repeatable read,” the values are expected to switch: blacks become whites, and whites become blacks. More interesting examples can be found here.

Enough with the arts and artificial examples. Let’s jump into a more realistic case of a possible application bug taken from Martin Kleppmann’s great book “Designing Data-Intensive Applications”. Imagine you’re writing an application to manage on-call doctors. Each shift has multiple doctors on-call, and any doctor can give up their shift if at least one other doctor remains on that shift. We use PostgreSQL 16 as the database:

CREATE TABLE shift (id int, name text, on_call boolean);

Now, both Alice and Bob want to leave the shift simultaneously:

--- Alice --- Bob
BEGIN; BEGIN;

SELECT count(*) FROM shift SELECT count(*) FROM shift
WHERE id = 1 AND on_call; WHERE id = 1 AND on_call;
count count
------- -------
2 2
(1 row) (1 row)
UPDATE shift UPDATE shift
SET on_call = false SET on_call = false
WHERE id = 1 AND name = 'Alice'; WHERE id = 1 AND name = 'Bob';
COMMIT; COMMIT;

Let’s check the result:

To state it clearly, we executed two transactions in parallel, and as a result, our constraint was broken. This happened because, by default, PostgreSQL uses the “read committed” isolation level. With “serializable,” we wouldn’t end up with an empty hospital.

In this post, we are not introducing any theory because there are many excellent database textbooks available. If you are new to the topic, we strongly advise reading the previously mentioned Martin Kleppmann’s “Designing Data-Intensive Applications” and Alex Petrov’s “Database Internals”. Additionally, the “Isolation Levels in Modern SQL Databases Series” articles written by Franck Pachot are a great source of practical knowledge. Martin Kleppmann did an excellent job testing ACID’s “I” as part of his Hermitage project.

Instead, we will try to understand the pros and cons of not having serializable as the default isolation level.

Premature optimization?

Using a weaker isolation level doesn’t always mean you have an actual trade-off between consistency and performance. It depends on the data and transactions: queries can be simple and might not require serialization. In this case, a weaker isolation level might provide better performance for free. Somehow, there is a widespread belief that weaker isolation works fine for most transactions, and application developers should be skilled enough to detect cases when stronger isolation is required and use it on a case-by-case basis.

To showcase the second school of thought, we quote Martin Kleppmann: “Unfortunately, those weaker isolation levels are quite poorly understood. Even though our industry has been working with this stuff for 20 years or more, there are not many people who can explain off-the-cuff the difference between, say, read committed and repeatable read. This is an issue because if you don’t know what guarantees you can expect from your database, you cannot know whether your code has concurrency bugs and race conditions.”

The problem with both opinions is that they’re theoretical. Performance can be measured, and bugs can be counted. So, we decided to conduct a small secondary research study to check:

Do bugs caused by weaker isolation levels happen often?
Is the performance impact of the serializable level really that significant?

In 2017, Peter Bailis and Todd Warszawski from Stanford University published a brilliant research paper titled “ACIDRain: Concurrency-Related Attacks on Database-Backed Web Applications”. They analyzed “12 popular self-hosted eCommenrce applications written in four languages and deployed on over 2M websites” and identified and verified “22 critical ACIDRain attacks that allow attackers to corrupt store inventory, over-spend gift cards, and steal inventory”. According to the paper, “Of the 22 vulnerabilities, five were level-based, meaning that the default weak isolation level led to the anomalies behind the vulnerabilities. The remaining 17 were scope-based, meaning that the database accesses were not properly encapsulated in transactions and concurrent API requests could trigger the vulnerability independent of the level of isolation provided by the database backend.”

Moreover, the paper cites several interesting cases where concurrency issues (recall that the isolation ACID property is about concurrency) caused security vulnerabilities:

Here is the story of concurrency-related attacks on Flexcoin and Poloniex bitcoin exchanges. As a result, all Flexcoin bitcoins were stolen, and Flexcoin was forced to shut down. Exactly the same thing happened with Poloniex due to exactly the same bug. The post author states a very interesting and important idea: this kind of bug is not a security flaw because neither unauthorized access nor a fault in the authorization scheme happened — the application was just broken by its design.
Another story of a concurrency exploit resulted in stolen digital money.

We were able to easily spot yet another BTC story: an attacker stole 100 BTC by exploiting a concurrency bug (namely a lost update) related to transactions. Also, there are many less dramatic stories, like this one, where bugs are not security issues but are subtle to investigate.

During our secondary research, we found another interesting recent paper. The authors defined database operations coordinated by the application as ad hoc transactions. In other words, ad hoc transaction logic implements concurrency control on the application side. They checked 8 popular open-source web applications and found 91 ad hoc transactions, 71 of which played critical roles. 53 of them had correctness issues. We believe this adheres to the point that concurrency control is sophisticated, and even seasoned developers regularly make concurrency bugs.

There is another important concern related to the weak isolation level set by default. In many DBMSs (PostgreSQL in particular) serializable transactions are serialized only with other serializable transactions. If your application has a bunch of transactions with weak isolation levels and you add a new one where you need serialization, you will have to review existing transactions to find the ones where you must “promote” the isolation level. Since applications usually have tons of transactions, it’s easy to overlook the needed ones.

Now, let’s try to understand the performance impact of serialization. Surprisingly, there are many more stories and publications about bugs caused by weak isolation levels than cases where stronger isolation levels caused impractically low performance.

This paper describes an initial serializable snapshot isolation (SSI, which is another name for “serializable”) implementation in Postgres. The authors concluded that “serializable” performs only slightly below “repeatable read.” In practice, there are situations where serialization failures cause retries and performance degradation, but these cases, unlike concurrency bugs, can be solved relatively easily.

The only reasonable comparison of “repeatable read” vs. “read committed” we have found is this outdated post by Percona. They concluded that there is almost no difference between these two modes under TPC-C load (the industry-standard OLTP benchmark). We believe that the absence of fresh publications on this topic supports this conclusion.

Conclusions

Modern research clearly shows that concurrency bugs caused by weak isolation levels are not rare. They account for about 20% of all bugs related to database transactions. Researchers have identified isolation-related concurrency bugs in many popular open-source web applications. Moreover, these bugs often cause security vulnerabilities, which attackers have already exploited.

On the other hand, we found no evidence that the strongest “serializable” level has significantly worse performance. In particular, CockroachDB and YDB use the serializable isolation level as default and demonstrate decent performance even when compared to PostgreSQL.

There is ongoing research on solid tools to simplify the detection of concurrency bugs. Conversely, performance testing is a well-established engineering area. In practice, this means that if you choose a strong isolation level by default, you can easily use performance tests to determine if optimization is required. But with weak isolation levels, you end up reasoning about correctness, and concurrency bugs in database applications are really hard to find. As noted by C.A.R. Hoare in his ACM Turing Award Lecture: “There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies.”

Thus, you might really want to consider switching to “serializable” as the default isolation level.

Hacker Times