Which explicit lock to use for a trigger?

Question

I am trying to understand which type of a lock to use for a trigger function.

Simplified function:

CREATE OR REPLACE FUNCTION max_count() RETURNS TRIGGER AS
$$
  DECLARE
    max_row           INTEGER := 6;
    association_count INTEGER := 0;
  BEGIN
    LOCK TABLE my_table IN ROW EXCLUSIVE MODE;

    SELECT INTO association_count COUNT(*) FROM my_table WHERE user_id = NEW.user_id;

    IF association_count > max_row THEN
      RAISE EXCEPTION 'Too many rows';
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER my_max_count
AFTER INSERT OR UPDATE ON my_table
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE max_count();

I initially was planning to use EXCLUSIVE but it feels too heavy. What I really want is to ensure that during this function execution no new rows are added to the table with concerned user_id.

Answer 1

If you want to prevent concurrent transactions from modifying the table, a SHARE lock would be correct. But that could lead to a deadlock if two such transactions run at the same time — each has modified some rows and is blocked by the other one when it tries to escalate the table lock.

Moreover, all table locks that conflict with SHARE UPDATE EXCLUSIVE will lead to autovacuum cancelation, which will cause table bloat when it happens too often.

So stay away from table locks, they are usually the wrong thing.

The better way to go about this is to use no explicit locking at all, but to use the SERIALIZABLE isolation level for all transactions that access this table.

Then you can simply use your trigger (without lock), and no anomalies can occur. If you get a serialization error, repeat the transaction.

This comes with a certain performance penalty, but allows more concurrency than a table lock. It also avoids the problems described in the beginning.