[SoftTalker]

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.

[ameliaquining]

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.

[stubish]

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).

[zadikian]

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.

[anarazel]

> 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.

That's true only for the latter (and even then only at a isolation level that's not too strict).

[zadikian]

Oh I misremembered, yeah just tested and the second INSERT errors.

[hun3]

Right.

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