Postgresql – Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails

performancepostgresqlpostgresql-performanceserializationversion control

On the PostgreSQL Concurrency With MVCC page, it says:

know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation levels come in. Postgres basically supports two models that allow you to control how this situation should be handled. The default, READ COMMITTED, reads the row after the inital transaction has completed and then executes the statement. It basically starts over if the row changed while it was waiting. For instance, if you issue an UPDATE with a WHERE clause, the WHERE clause will rerun after the initial transaction commits, and the UPDATE takes place if the WHERE clause is still satisfied.

The docs seem to suggest that READ COMMITTED is still subject to failures and should be retried.

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE?

Best Answer

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE?

No.

READ COMMITTED doesn't retry. Neither does SERIALIZABLE. The application is expected to retry transactions that suffer deadlocks, serialization failures, etc.

That description in docs is very misleading, I'll raise it on the docs list. PostgreSQL doesn't "start over" in READ COMMITTED at all, like some other DBs (e.g. Oracle) do. Instead it waits until the row it's waiting for commits or rolls back. If the other tx commits PostgreSQL reads the updated row, checks that it still matches any WHERE clause or other predicate, and then continues execution. The details are a bit arcane, see EvalPlanQual in the sources.

In either READ COMMITTED or SERIALIZATION the application should be able to re-issue a transaction. READ COMMITTED transactions can fail in ways that will succeed on retry, including:

  • Deadlock detection due to lock upgrades, lock ordering issues, etc
  • Administrative query cancel requests
  • Administrative shutdown/restart of the DB
  • Unplanned crash/restart of the DB
  • Connectivity interruption
  • ... etc

SERIALIZABLE just adds some more failure cases.

Well written applications will cope with query failures and re-issue the transaction.

Related Question