[GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)
От
Gavin Wahl
Тема
[GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)
Дата
Msg-id
CAAAf5g-amPWT9MpTJCUZ61rnGNUmt8OQNPx2cT9=Xas00KsTkg@mail.gmail.com
Список
Дерево обсуждения
[GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE) Gavin Wahl <gwahl@fusionbox.com>
Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE) "David G. Johnston" <david.g.johnston@gmail.com>
Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE) Gavin Wahl <gwahl@fusionbox.com>
Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE) "David G. Johnston" <david.g.johnston@gmail.com>
I have a table that stores user notifications:
CREATE TABLE notifications (
user_id INT,
type CHAR(1),
PRIMARY KEY (user_id, type)
);
When a user edits their notifications, I need to atomically replace the old set
with the new set. My first instinct is to do this:
BEGIN;
DELETE FROM notifications WHERE user_id = 1;
INSERT INTO notifications (user_id, type) VALUES (1, 'a'), (1, 'b');
COMMIT;
This of course doesn't work when two transactions run concurrently though --
one of them will get a unique constraint violation.
My next thought was to use upsert:
BEGIN;
DELETE FROM notifications WHERE user_id = 1;
INSERT INTO notifications (user_id, type) VALUES (1, 'a'), (1, 'b') ON
CONFLICT DO NOTHING;
COMMIT;
This doesn't give an error for concurrent transactions, but doesn't do
the right thing. Consider if one transaction runs to replace the set with
{'a', 'b'} and another runs with {'b', 'c'}. The result should either
be {'a', 'b'} or {'b', 'c'}, but they actually get merged together and
the user ends up with notifications {'a', 'b', 'c'}.
Is there any way to do this correctly without SERIALIZABLE transactions? It
would be nice to avoid having to retry transactions. Ideally I'd like to avoid
explicit locking as well.
В списке pgsql-general по дате отправления
От: Gavin Wahl
Дата:
От: David G. Johnston
Дата: