CODEWITHSUNDEEP
sqlpostgresqltransactions
Ender
Ender

Reputation: 27283

Atomically replace one set of rows with another set of rows in Postgres

Assume we have a table that describes the contents of a user's basket of fruit. This is essentially just a mapping of user -> list of fruit.

Periodically, we query a remote data source and get a new list for a particular user. Then, we try to wholly replace all of that user's current rows with a new set of rows. For example:

Before:

user     fruit        freshness
-------------------------------
...
47       'apple'      0.1
47       'pear'       9.5
47       'pear'       2.8
...

After:

user     fruit        freshness
-------------------------------
...
47       'apple'      93.9034
47       'banana'     0
...

Given a new set of rows, is there a way to do the replacement that is atomic in postgres?

The following does NOT work:

BEGIN
DELETE FROM basket WHERE user=47
INSERT INTO basket ...
COMMIT

If we have multiple transactions going on simultaneously, postgres will happily order commands like this (with no locking issues):

BEGIN
BEGIN
DELETE
DELETE
INSERT
INSERT  <---- data inserted twice here
COMMIT
COMMIT

This will result in twice as many rows as there should be.

A lot of answers to similar questions claim that the first DELETE will lock the rows in question and the second transaction will have to wait, but this isn't true (for whatever reason). Both transactions happily step on each other's toes here.

Notes:

Upvotes: 3

Views: 2161

Answers (1)

klin
klin

Reputation: 121474

You can lock the user_id that you are going to modify. If you have a users table then select the corresponding row for update:

BEGIN;
SELECT user_id FROM users WHERE user_id = 47 FOR UPDATE;
DELETE FROM basket WHERE user_id = 47;
INSERT INTO basket ...
COMMIT;

Alternatively you can use advisory locks, e.g.:

BEGIN;
SELECT pg_advisory_lock('basket'::regclass::int, 47);
DELETE FROM basket WHERE user_id = 47;
INSERT INTO basket ...
SELECT pg_advisory_unlock('basket'::regclass::int, 47);
COMMIT;

Read about Explicit Locking in the documentation.

Upvotes: 5

Related Questions