Atomic aggregate check before update-CodePudding

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?

CodePudding user response：

My suggestion :

(a) Create a dedicated status_counter table with a constraint on the counter column which checks that its value remains under the limit (6000) :

CREATE TABLE status_counter (status status primary key, counter integer CHECK (counter < 6000)) ;

(b) Set up the counter :

INSERT INTO status_counter
SELECT 'VALIDATED', count(*) FROM my_table WHERE status='VALIDATED' ;

CREATE OR REPLACE FUNCTION table_insert ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
  IF NEW.status = 'VALIDATED'
  THEN
      UPDATE status_counter
         SET counter = counter   1
       WHERE status='VALIDATED' ;
  END IF ;
  RETURN NEW ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER table_insert BEFORE INSERT ON table
FOR EACH ROW EXECUTE FUNCTION table_insert() ;

CREATE OR REPLACE FUNCTION table_update ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
  IF NEW.status IS NOT DISTINCT FROM 'VALIDATED' 
  AND OLD.status IS DISTINCT FROM 'VALIDATED'
  THEN
      UPDATE status_counter
         SET counter = counter   1
       WHERE status='VALIDATED' ;
  ELSEIF NEW.status IS DISTINCT FROM 'VALIDATED' 
  AND OLD.status IS NOT DISTINCT FROM 'VALIDATED'
  THEN
      UPDATE status_counter
         SET counter = counter - 1
       WHERE status='VALIDATED' ;
  END IF ;
  RETURN NEW ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER table_update BEFORE UPDATE OF status ON table
FOR EACH ROW EXECUTE FUNCTION table_update() ;

(d) Starting from now, each time a user inserts or updates a row in table, the counter is updated by trigger, and the update fails when the counter reaches the limit raising an exception and preventing the row to be inserted or updated.

The atomicity between hundred of threads should be guaranteed because they all attempt to update the same unique row in table status_counter.

demo in dbfiddle.

CodePudding user response：

"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'