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.

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.

Yeah, I use it in queries as well. Good point!

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.

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

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.

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.

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.

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

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.