Обсуждение: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)
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.
Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)
От
"David G. Johnston"
Дата:
I have a table that stores user notifications:
CREATE TABLE notifications (
user_id INT,
type CHAR(1),
PRIMARY KEY (user_id, type)
);[...]
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.
Given this limited example I'd probably choose to model notifications as an array on the user table. Then just "UPDATE user SET notifications = array['a','b']::text WHERE user_id = 1;
David J.
> Given this limited example I'd probably choose to model notifications as an > array on the user table. Then just "UPDATE user SET notifications = > array['a','b']::text WHERE user_id = 1; I'm hesitant to ditch the first normal form just to get around this. Anyway, there's actually extra data in the table that makes it hard to use an array: CREATE TABLE notifications ( user_id INT, type CHAR(1), threshold INT, some_options BOOLEAN, PRIMARY KEY (user_id, type) );
Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)
От
"David G. Johnston"
Дата:
> Given this limited example I'd probably choose to model notifications as an
> array on the user table. Then just "UPDATE user SET notifications =
> array['a','b']::text WHERE user_id = 1;
I'm hesitant to ditch the first normal form just to get around this. Anyway,
there's actually extra data in the table that makes it hard to use an array:
CREATE TABLE notifications (
user_id INT,
type CHAR(1),
threshold INT,
some_options BOOLEAN,PRIMARY KEY (user_id, type)
);
A custom composite type would solve that part of the problem.
You're going to have to pick you poison here. No serializable, no locking, and no atomic data type. I don't have any other reasonable ideas that aren't any worse than any one of those three. You would need to introduce some kind of "notification set id" and make (user_id, active_notification_set_id) the linking multi-column key.
Or wait and see if anyone more clever than I has some ideas.
David J.