SELECT FOR UPDATE
I think this is the perfect solution for your use case. E.g. considering a simpler dummy case of multiple incrementer threads which I believe models well the question:
CREATE TABLE "MyInt" ( i INTEGER NOT NULL )
INSERT INTO "MyInt" VALUES (0)
and then multiple parallel updaters:
SELECT * FROM "MyInt"
// set newI = i + 1 in your server code
UPDATE "MyInt" SET i = ${newI}
As it stands, many updates would be lost. But if you do instead:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM "MyInt" FOR UPDATE
// set newI = i + 1 in your code
UPDATE "MyInt" SET i = ${newI}
COMMIT
the updates won't be lost anymore, because the FOR UPDATE
locks the row from other SELECT FOR UPDATE
until the transaction commits, and other threads just wait. https://www.postgresql.org/docs/13/explicit-locking.html#LOCKING-ROWS documents:
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.
I have tested this with this test code.
Documentation quote that says that REPEATABLE READ
would also be enough
FOR UPDATE
+ READ COMMITTED
is the best approach I think, as it does the job and is a bit faster than REPEATABLE READ
on my simple benchmark (4.2s vs 3.2s). But just for completeness, the fact that REPEATABLE READ
alone also works is quite clear in the docs, just to confirm what Laurenz said further: https://www.postgresql.org/docs/14/transaction-iso.html#XACT-REPEATABLE-READ
Applications using this level must be prepared to retry transactions due to serialization failures.
UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message
ERROR: could not serialize access due to concurrent update
because a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.
When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning. The second time through, the transaction will see the previously-committed change as part of its initial view of the database, so there is no logical conflict in using the new version of the row as the starting point for the new transaction's update.
When such an error is detected, you have to run ROLLBACK
, and try again.