PostgreSQL Gap lock by SELECT ... FOR UPDATE

user_2101084 0
Viewed 785

There is a query with a Gap Lock used at MySQL/InnoDB:

SELECT id, time, count
FROM table_a
WHERE time
    BETWEEN DATE_SUB(NOW(), INTERVAL 24 HOUR)
    AND NOW()
FOR UPDATE

It locks the time range and returns a recent record if present (during last 24 hrs). If not - the session still owns a lock over the duration of last 24 hours to safely insert a new record.

Is is possible to make the same gap lock for entire 24 hours duration (even if there are no records) in PostgreSQL?

locking postgresql
1 Answers

The way to do that in PostgreSQL is to use the SERIALIZABLE isolation level for all transactions.

Then you don't need the FOR UPDATE at all. PostgreSQL won't prevent rows from being inserted in the gap, but if two transactions both read and write values in the same gap simultaneously, one of them will get a serialization error and have to redo the transaction (on the second try, it will find the gap not empty).

The concept at work here is serializability: it is acceptable if someone else inserts into the gap without reading (that transaction is logically after the one with your SELECT). But if two transactions both find the gap empty and then insert something, that would create an anomaly that is prevented by SERIALIZABLE.

user_64643080
Related