> 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?
SoftTalker 27 minutes ago [-]
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.
hun3 14 minutes ago [-]
Right.
As an example: Oracle and PostgreSQL don't have dirty reads: READ UNCOMMITTED does nothing. MySQL's concurrency model depends on the engine.
mastermedo 2 hours ago [-]
> 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).
mjb 2 hours ago [-]
> 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).
lukas221 2 hours ago [-]
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.
lmm 17 minutes ago [-]
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.
cwillu 7 minutes ago [-]
Huh?
lmm 3 minutes ago [-]
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?
jiggawatts 1 hours ago [-]
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.
63stack 39 minutes ago [-]
You are supposed to define indexes based on how you query the data, not ahead of time.
sanqui 1 hours ago [-]
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.
sublinear 50 minutes ago [-]
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.
Rendered at 23:25:15 GMT+0000 (Coordinated Universal Time) with Vercel.
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?
As an example: Oracle and PostgreSQL don't have dirty reads: READ UNCOMMITTED does nothing. MySQL's concurrency model depends on the engine.
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).
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).
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.
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.
This is a rabbit hole worth going down, but it shouldn't be the default. This is a classic case of Chesterton's Fence.