Seems like a frequent surprise is that Postgres and MySQL don't default to serializable (so not fully I in ACID). They do read-committed. I didn't see this article mention that, but maybe I missed it. The article says read-committed provides "slightly" better performance, but it's been way faster in my experience. Forget where, but I think they said they chose this default for that reason.
Using read-committed ofc means having to keep locking details in mind. Like, UNIQUE doesn't just guard against bad data entry, it can also be necessary for avoiding race conditions. But now that I know, I'd rather do that than take the serializable performance hit, and also have to retry xacts and deal with the other caveats at the bottom of https://www.postgresql.org/docs/current/transaction-iso.html
Recent versions of MySQL and MariaDB default to repeatable-read for InnoDB tables, not read-commited :
https://dev.mysql.com/doc/refman/8.4/en/set-transaction.html...
https://mariadb.com/docs/server/reference/sql-statements/adm...
I don't know about MyISAM though (who uses it anyway ;-) ).
The issue with SERIALIZABLE, aside from performance, is that transactions can fail due to conflicts/deadlocks/timeouts, so application code must be prepared to recognize those cases and have a strategy to retry the transactions.
Right. So my code had a helper to run some inner func in a serializable xact, in rw or ro mode, which would retry with backoff. Like the TransactionRunner in Spanner. But even with no retries occurring, it was very slow.
VoltDB took this to an extreme - the way you interact with it is by sending it some code which does a mix of queries and logic, and it automatically retries the code as many times as necessary if there's a conflict. Because it all happens inside the DBMS, it's transparent and fast. I thought that was really clever.
I'm using the past tense here, but VoltDB is still going. I don't think it's as well-known as it deserves to be.
> Postgres and MySQL don't default to serializable
Oracle and SQL Server also default to read committed, not serializable. Serializable looks good in text books but is rarely used in practice.
Yeah, the only examples I know of it being default are Spanner and Cockroach, which are for a different use case.