I only recently learned about serializable transactions and it is 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.
> 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).
> 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).
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.
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 only recently learned about serializable transactions and it is 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.
> 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).
> 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).
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.
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.