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.

I think the argument is that thinking through the exact implications of your particular database's concurrency model for each query you write is too much to ask of generalist software engineers; if it's not kept down to a small auditable surface, mistakes will occur pervasively. Therefore, serializable (i.e., do it the obvious way without allowing tricky edge-case states) should be the default, and should be departed from only when performance demands it in a specific case, with careful analysis in those few cases to ensure correctness. (This is pretty closely analogous to the argument for memory-safe programming languages.)

The one complication is that this does mean applications need to be prepared for queries to need to be retried, and while this is reasonably straightforward in most cases (and can in principle be enforced with static analysis), it's not always done today, which makes it hard to change the defaults.

Tbh I always forget the specifics soon after reading them. Basically you can do an atomic UPDATE WHERE if there are no subqueries involved. 90% of the time that's good enough, and for anything else I end up refreshing on features like SELECT FOR UPDATE.

Well also I know Postgres UNIQUE indexes provide additional locking. Like you can do an INSERT... WHERE NOT EXISTS or INSERT... ON CONFLICT that is guaranteed to succeed.

Right.

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