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.
> 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.
> 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?
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.
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).
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.
The article doesn't mention the biggest problem with serializable isolation. At every commit, you need handle the possibility of a serialization exception and retry the transaction. Traditionally devs and frameworks don't, so your application works fine during development and staging but starts failing under load. It makes commit failures normal, rather than an 'oh shit' problem because your disk has filled or someone has tripped over a network cable.
And how do you retry transactions? Then you hit another issue when using multiple datastores, where you need to learn about two-phase commit and the joys of manually keeping datastores in sync that don't support it (eg. filesystems).
And the locks, if you dare run batch updates along with web requests. The long running transactions lock everything they read, blocking short transactions. Because that is exactly what you asked for. Again, you will miss this during development and only notice under load.
So sure, you might avoid some data consistency issues if your data model and update patterns hit the edge cases. In practice, the reason details about serializable are not well known is the cases are rare. Using it gives you safety (maybe that rare case is your case!), but everything needs to be carefully designed around it.
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:
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.
TIL the horror that is the existence of non-serializable isolation.
I'm a gamedev. I've worked alongside webdevs (frontend and backend) that build our websites and forums. Alongside coworkers who handle networking stuff while I port things on the same project. Spotted SQLIs for people and pointed them on a better path [1]. I've dabbled in my own share of SQL-adjacent queries... which is to say databases have always been on my list of things I should probably take the time to put properly into my toolkit, for increased reliability and data durability. After all, rotating file snapshots by hand, and fuzzing formats to create recoverability from corruption (if only by detecting it and reverting to previous snapshots instead of crashing or corrupting further) is clearly the work of uncultured barbarians, bereft the wonders of proper fsync-aware ACID storage technologies.
And then I read this:
---------------------------------------------
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.
---------------------------------------------
I regret the clearly undue respect and regard I've given to database technology. I knew some of this kind of nonsense had intruded with the NoSQL and sharding crowd, but I thought you at least had to ask for such ruination for most of the SQLs used in production, at least in the context of a singular database. Euhg.
14 comments
[ 1.6 ms ] story [ 25.1 ms ] threadSure, 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.
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 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?
And how do you retry transactions? Then you hit another issue when using multiple datastores, where you need to learn about two-phase commit and the joys of manually keeping datastores in sync that don't support it (eg. filesystems).
And the locks, if you dare run batch updates along with web requests. The long running transactions lock everything they read, blocking short transactions. Because that is exactly what you asked for. Again, you will miss this during development and only notice under load.
So sure, you might avoid some data consistency issues if your data model and update patterns hit the edge cases. In practice, the reason details about serializable are not well known is the cases are rare. Using it gives you safety (maybe that rare case is your case!), but everything needs to be carefully designed around it.
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.
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.
I'm a gamedev. I've worked alongside webdevs (frontend and backend) that build our websites and forums. Alongside coworkers who handle networking stuff while I port things on the same project. Spotted SQLIs for people and pointed them on a better path [1]. I've dabbled in my own share of SQL-adjacent queries... which is to say databases have always been on my list of things I should probably take the time to put properly into my toolkit, for increased reliability and data durability. After all, rotating file snapshots by hand, and fuzzing formats to create recoverability from corruption (if only by detecting it and reverting to previous snapshots instead of crashing or corrupting further) is clearly the work of uncultured barbarians, bereft the wonders of proper fsync-aware ACID storage technologies.
And then I read this:
---------------------------------------------
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.
---------------------------------------------
I regret the clearly undue respect and regard I've given to database technology. I knew some of this kind of nonsense had intruded with the NoSQL and sharding crowd, but I thought you at least had to ask for such ruination for most of the SQLs used in production, at least in the context of a singular database. Euhg.
1. https://blog.codinghorror.com/give-me-parameterized-sql-or-g...