Postgresql lock for insert only

Question

I have two tables in postgresql:

table A: has the definitions for a certain object
table B: has the instances of the objects defined in table A

Table A has columns total_instances and per_player_instances, which can both be null, and which I need to prevent the instance count in table B from going above if they're set. The code handles most cases, but I was getting duplicates from concurrent inserts.

Table A does not need to be locked as it rarely changes, and if we do it we can do it in planned downtime when no inserts will be happening in table B.

I wrote a trigger to count the existing instances and return an error if the count has gone over, but I must have gotten the locking wrong as now I am getting deadlock_detected errors. My trigger function is this:

CREATE OR REPLACE FUNCTION ri_constraints_func() RETURNS trigger AS $$
DECLARE
    max_total          INTEGER   := NULL;
    max_per_player     INTEGER   := NULL;
    total_count        INTEGER   := 0;
    per_player_count   INTEGER   := 0;
BEGIN
    -- prevent concurrent inserts from multiple transactions

    SELECT INTO max_total, max_per_player
           awardable_total, awardable_per_player
    FROM   t1
    WHERE  id = NEW.t1_id;

    LOCK TABLE t2 IN EXCLUSIVE MODE;

    IF max_total IS NOT NULL THEN
        SELECT INTO total_count
               count(1)
        FROM   t2
        WHERE  t1_id = NEW.t1_id;

        IF total_count >= max_total THEN
            RAISE EXCEPTION 'awardable_total_exceeded';
        END IF;
    END IF;

    IF max_per_player IS NOT NULL THEN
        SELECT INTO per_player_count
               count(1)
        FROM   t2
        WHERE  t1_id = NEW.t1_id
        AND    awarded_to_player_id = NEW.awarded_to_player_id;

        IF per_player_count >= max_per_player THEN
            RAISE EXCEPTION 'awardable_per_player_exceeded';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Basically what I need to do is prevent inserts into the table between counting the instances and doing the insert. I thought that using LOCK TABLE t2 IN EXCLUSIVE MODE; would accomplish that. I'm doing more research into table locking, but if anyone knows what locking level to use to accomplish this I'd appreciate hearing it.

Also, I'm not married to this particular approach, so if getting this working requires re-writing this function, I'm open to that too.

Postgresql version is 11.

There is no need to explicitly lock anything. – Laurenz Albe Mar 18 '21 at 03:35 — Laurenz Albe, Mar 18 '21 at 03:35

score 0 · Answer 1 · answered Mar 17 '21 at 20:41

I believe that what you are doing is unnecessary and it can probably be accomplished by using proper transaction management.

For the transaction that needs to look the entire table, set the isolation level to SERIALIZABLE. This will disallow ghosts which is why you are trying to lock the entire table.

You don't need to do locks manually.

This might help: https://youtu.be/oI2mxnZbSiA

I recommend you read about isolation levels in postgresql.

Postgresql lock for insert only

1 Answers1