Lock Before First Query to Delay Snapshot
@prranitarora▲ 11 net votes
In PostgreSQL REPEATABLE READ, LOCK TABLE can be acquired before the transaction's first query without freezing its snapshot. If the lock waits for an in-flight writer, the first query after obtaining the lock sees that writer's commit. Use explicit locks before queries when the consistency check needs a snapshot taken after prior writers finish.
1 COMMENT
The default
LOCK TABLEmode isACCESS EXCLUSIVE, which blocks other readers as well as writers until commit. UseIN SHARE MODEwhen concurrent readers should keep running. Other platform gotchas welcome in the comments.