4 ms·

Head's up. Depending on the circumstances, multiple updates are vulnerable to a race condition known as "read skew". If a user's status changes while the multip

Head's up. Depending on the circumstances, multiple updates are vulnerable to a race condition known as "read skew". If a user's status changes while the multiple update transaction is executing, their status can wind up in a bad state. Here's a demonstration: > CREATE TABLE reward_members (id bigint, member_status text); > INSERT INTO reward_members (values (0, 'silver')); > BEGIN; > UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold'; > UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze'; -- Concurrently: > UPDATE reward_members SET member_status = 'gold' WHERE id = 0 AND member_status = 'silver'; > 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 If the transactions executed atomically (in the instantaneous sense, not the ACID sense), the member_status of the user should be either silver_group or gold_group depending on the order the transactions executed in. Due to the race condition, the user winds up with the member_status of gold instead. If you want to avoid this you either have to use the case statement version or use a stricter isolation level. Either repeatable read or serializable would prevent this. I've written about read skew, other related race conditions, and how to prevent them in a series of blog posts you can find here[0][1][2]. On a related note, efficiently batching disjoint updates is an interesting problem. One of my coworkers wrote about how he solved this exact problem in Redshift[3]. He found the most efficient way was to create a staging table with what old values corresponded to what new values. Then you can join the staging table with the table you are updating. [0] http://malisper.me/postgres-transactions-arent-fully-isolated/ http://malisper.me/postgres-transactions-arent-fully-isolate... [1] http://malisper.me/postgres-row-level-locks/ http://malisper.me/postgres-row-level-locks/ [2] http://malisper.me/postgres-transaction-isolation-levels/ http://malisper.me/postgres-transaction-isolation-levels/ [3] https://heapanalytics.com/blog/engineering/redshift-pitfalls-avoid https://heapanalytics.com/blog/engineering/redshift-pitfalls...

leetrout 9y ago
Thank you very much for taking the time to explain that and including all the links. I've had issues in the past with atomic transactions in Django still appearing to have race conditions and I think this explains why. Really appreciate it.
ilikepi 9y ago
Ditto, thanks for sharing the articles. For a trivially small set of mapping data, I like using VALUES to create a "virtual" staging table: UPDATE reward_members SET member_status = status_map.new_status FROM (VALUES ('gold', 'gold_group'), ('bronze', 'bronze_group'), ('platinum', 'platinum_group'), ('silver', 'silver_group') ) AS status_map (old_status, new_status) WHERE member_status = status_map.old_status; I find this to be more readable than the CASE statement version.
- infogulch 9y ago
  I was looking through the thread for this solution. It's great because it's much harder to mess up and is less verbose than a case, and it's built on common SQL features making it portable and fast and easy to grok (as long as both the engine and the dba knows how joins work). As a data analyst this technique is also great for scrubbing dirty data at query time when you don't have the ability to just fix it.
  - ilikepi 9y ago
    Yeah, I use it in queries as well. Good point!
borplk 9y ago
I suggest people use "serializable" by default and relax it carefully on a as-needed basis.
- malisper 9y ago
  That's reasonable. I usually avoid the higher isolation levels because they can cause queries to spontaneously fail. You need to be able to put in the extra work for handling the cases where they fail. If data correctness is an absolute must, you should go with serializable.
- stubish 8y ago
  I suggest read committed, and increase it to serializable only if necessary. serializable transaction isolation means your code is responsible for catching the serialization exceptions that will occur and handling them (retrying the transaction, generally). Devs just don't do that, and don't even know how to trigger the exceptions in most case. So you end up with apps that work fine during CI and staging, but start failing under load. And it might not be possible to retrofit the correct exception handling without a massive re-architecture.
ignoramous 9y ago
In that case, you'd either mutex lock rows you're updating or generally choose a more stricter isolation level like RepeatableReads, or SSI?
- 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.
da_chicken 9y ago
This isn't an issue with multiple statements in a transaction. It's just an issue with using the incorrect transaction isolation level. That will cause problems with single statement transactions if you're using READ COMMITTED transaction isolation level. One of your examples already shows that you don't need multiple statements: S1> BEGIN; S1> UPDATE ints SET n = n+1; S2> BEGIN; S2> DELETE FROM ints WHERE n = 2; -- S2 blocks since the DELETE is trying to modify a row -- currently being updated. S1> COMMIT; -- S2 unblocks. S2> COMMIT; S3> SELECT * FROM ints; n --- 2 3 This is working as designed and intended. This is how the READ COMMITTED transaction isolation level works. If you want higher isolation levels like REPEATABLE READ or SERIALIZABLE, then use those. If you need snapshot isolation, then use that. READ COMMITTED is the default isolation level because it's the most usable isolation level for the vast majority of situations, but having an RDBMS that handles concurrency for you doesn't mean that you don't have to understand how that concurrency model works. The tradeoff for these higher isolation levels is that instead of getting results that you might not necessarily expect, you'll instead get deadlocks and forced transaction rollbacks, and your application will need to account for that instead and may need to resubmit queries in certain situations. This is the fundamental tradeoff between performance and isolation.

leetrout 9y ago

Thank you very much for taking the time to explain that and including all the links. I've had issues in the past with atomic transactions in Django still appearing to have race conditions and I think this explains why. Really appreciate it.

ilikepi 9y ago

Ditto, thanks for sharing the articles. For a trivially small set of mapping data, I like using VALUES to create a "virtual" staging table: UPDATE reward_members SET member_status = status_map.new_status FROM (VALUES ('gold', 'gold_group'), ('bronze', 'bronze_group'), ('platinum', 'platinum_group'), ('silver', 'silver_group') ) AS status_map (old_status, new_status) WHERE member_status = status_map.old_status; I find this to be more readable than the CASE statement version.

infogulch 9y ago
I was looking through the thread for this solution. It's great because it's much harder to mess up and is less verbose than a case, and it's built on common SQL features making it portable and fast and easy to grok (as long as both the engine and the dba knows how joins work). As a data analyst this technique is also great for scrubbing dirty data at query time when you don't have the ability to just fix it.
- ilikepi 9y ago
  Yeah, I use it in queries as well. Good point!

borplk 9y ago

I suggest people use "serializable" by default and relax it carefully on a as-needed basis.

malisper 9y ago
That's reasonable. I usually avoid the higher isolation levels because they can cause queries to spontaneously fail. You need to be able to put in the extra work for handling the cases where they fail. If data correctness is an absolute must, you should go with serializable.
stubish 8y ago
I suggest read committed, and increase it to serializable only if necessary. serializable transaction isolation means your code is responsible for catching the serialization exceptions that will occur and handling them (retrying the transaction, generally). Devs just don't do that, and don't even know how to trigger the exceptions in most case. So you end up with apps that work fine during CI and staging, but start failing under load. And it might not be possible to retrofit the correct exception handling without a massive re-architecture.

ignoramous 9y ago

In that case, you'd either mutex lock rows you're updating or generally choose a more stricter isolation level like RepeatableReads, or SSI?

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.

da_chicken 9y ago

This isn't an issue with multiple statements in a transaction. It's just an issue with using the incorrect transaction isolation level. That will cause problems with single statement transactions if you're using READ COMMITTED transaction isolation level. One of your examples already shows that you don't need multiple statements: S1> BEGIN; S1> UPDATE ints SET n = n+1; S2> BEGIN; S2> DELETE FROM ints WHERE n = 2; -- S2 blocks since the DELETE is trying to modify a row -- currently being updated. S1> COMMIT; -- S2 unblocks. S2> COMMIT; S3> SELECT * FROM ints; n --- 2 3 This is working as designed and intended. This is how the READ COMMITTED transaction isolation level works. If you want higher isolation levels like REPEATABLE READ or SERIALIZABLE, then use those. If you need snapshot isolation, then use that. READ COMMITTED is the default isolation level because it's the most usable isolation level for the vast majority of situations, but having an RDBMS that handles concurrency for you doesn't mean that you don't have to understand how that concurrency model works. The tradeoff for these higher isolation levels is that instead of getting results that you might not necessarily expect, you'll instead get deadlocks and forced transaction rollbacks, and your application will need to account for that instead and may need to resubmit queries in certain situations. This is the fundamental tradeoff between performance and isolation.