Atomic aggregate check before update

Question

I have a table with many records, an enumerated column, and a highly multithreaded environnement.

CREATE TYPE status AS ENUM
    ('UNCONFIRMED', 'REGISTERED', 'VALIDATED', 'PAID');

Our system allows 6000 validated statuses, precisely. This value is dynamic and will increase later but will never go down.

We have to check the number of validated records before allowing the update:

SELECT count(*) FROM table WHERE status='VALIDATED';

• If the result is below < 6000 (for a start), we change the status to validated.
• If the result is >= 6000, we do not change the status, and throw an Exception.
• We must never go above that number

I though about:

• isolation levels
• table lock
• select for update
• update where + a subquery (see below)
• another?

UPDATE table
   SET status='VALIDATED'
   WHERE (
        SELECT count(*)<6000 FROM table WHERE status='VALIDATED'
   ) AND id=:id;

Which would be the best way to do this and guarantee atomicity between hundred of threads?

Answer 1

"update where + a subquery" will not be sufficient, you would need to couple it with a table lock, an advisory lock, or an increased isolation level. Otherwise it will allow multiple updates in flight at the same time to complete and exceed the count.

"select for update" seems like a horrible idea. It would have to lock every row in the table for every attempt, which would generate a huge amount of churn. And it still might miss some depending whether rows are ever inserted with status='VALIDATED'