[ahoka]

"a database which uses optimistic concurrency in serializable isolation level. Postgres is often configured this way, though it's not the only way it can be configured."

It's not the default (read committed is) and I never saw serializable being set in actual production systems. You can do it, but then you have to be able to retry all of your transactions, including read.

What if the task you do take 5 minutes? 30 minutes? 10 hours? Do you create long transaction, blocking all reads?

[anarazel]

> It's not the default (read committed is) and I never saw serializable being set in actual production systems.

It's not the common mode of deployment, but it's definitely in prod use.

> You can do it, but then you have to be able to retry all of your transactions, including read.

Pure read transactions shouldn't need to be retried in postgres due to serialization errors. You need to have read-write dependencies for that.

That's not to say that effectively read only transactions aren't affected by serializable, you do need to record the necessary metadata for the serialization logic to work.

FWIW, if you know your transaction is read only and long running, you can start a transaction with START TRANSACTION READ ONLY DEFERRABLE, which makes the start transaction slower, but then does not need to do any work related to serializable while the transaction is running.

[stickfigure]

> I never saw serializable being set in actual production systems

Every major prod system I've worked on in the last 15 years ran in serializable, including my current charge which processes tens of billions of dollars annually. YMMV but this is quite common in serious production systems. Google's Spanner only runs in serializable.

It doesn't matter though. I could write the sequence out with a SELECT FOR UPDATE and the second request will block instead of retry. The client experience is the same; the "second" request blocks. @pdonis wanted an example so I picked one.