3 ms·
Except mutex locking the rows is also vulnerable to read skew! For example, even if you lock the rows with SELECT ... FOR UPDATE: > CREATE TABLE reward_mem
by malisper 9y ago
Except mutex locking the rows is also vulnerable to read skew! For example, even if you lock the rows with SELECT ... FOR UPDATE:
> CREATE TABLE reward_members (id bigint, member_status text);
> INSERT INTO reward_members (values (0, 'silver'));
> BEGIN;
> SELECT * FROM reward_members WHERE member_status = 'gold' FOR UPDATE;
> SELECT * FROM reward_members WHERE member_status = 'bronze' FOR UPDATE;
> SELECT * FROM reward_members WHERE member_status = 'platinum' FOR UPDATE;
-- In concurrent transaction: > UPDATE reward_members SET member_status = 'platinum' WHERE id = 0 AND member_status = 'silver';
> SELECT * FROM reward_members WHERE member_status = 'silver' FOR UPDATE;
> UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold';
> UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze';
-- In concurrent transaction: > UPDATE reward_members SET member_status = 'gold' WHERE id = 0 AND member_status = 'platinum';
> UPDATE reward_members SET member_status = 'platinum_group' WHERE member_status = 'platinum';
> UPDATE reward_members SET member_status = 'silver_group' WHERE member_status = 'silver';
> COMMIT;
> SELECT * FROM reward_members;
id | member_status
----+---------------
0 | gold
What you would need instead are "predicate locks". Unfortunately Postgres does not expose predicate locks to the user. Predicate locks lock all rows that match some predicate and prevent any row where the predicate is false from being modified so the predicate is now true. If Postgres did have predicate locks, you could grab a predicate lock on "membership_status = 'gold'", before running the gold UPDATE. That would prevent any row from concurrently having it's membership_status to gold while the transaction is executing.
Stricter isolation levels are an option. I would only use them if data correctness is an absolute must. I try to avoid them because they abort any queries that would otherwise have a race condition. You then have to work around queries spontaneously failing.