Repeatable Read Cannot Lock Changed Rows
@prranitarora▲ 11 net votes
In PostgreSQL REPEATABLE READ, SELECT FOR UPDATE fails with SQLSTATE 40001 if another transaction updated and committed the target row after the reader's snapshot. The stale reader must roll back and retry the complete transaction to lock the newer row version.
1 COMMENT
Under READ COMMITTED, the same
SELECT ... FOR UPDATEwaits for the other transaction and then locks the newest committed row version, so the retry is only needed at REPEATABLE READ or higher. Seen another edge case with this? Add it below.