skip to Main Content

I have a postgres table:

CREATE TABLE clicks (
  page_id UUID REFERENCES pages (id) ON DELETE CASCADE,
  status                TEXT        NOT NULL,
  click                 BIGINT      NOT NULL,
  PRIMARY KEY (page_id, status)
);

and two execution blocks:

code_block_1:

BEGIN;
LOCK TABLE clicks IN SHARE UPDATE EXCLUSIVE MODE;
INSERT INTO clicks (page_id, status, count)
            VALUES ($1, $2, $3 )
            ON CONFLICT (page_id, status) DO UPDATE SET count = clicks.count + EXCLUDED.count;
-- that insert query can run more than once on different rows in the table. so imagine that there is a code that generates that INSERT INTO inside a for loop inside that one transaction 
COMMIT;

code_block_2:

BEGIN;
LOCK TABLE clicks IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO clicks (page_id, status, count)
VALUES ($1, $2, $3)
ON CONFLICT (page_id, status) DO UPDATE SET
count = EXCLUDED.count;
-- that insert query can run more than once on different rows in the table. so imagine that there is a code that generates that INSERT INTO inside a for loop inside that one transaction.
COMMIT;
    

Rows that are being updated by code_block_1 might be updated in parallel by multiple threads.
But rows that are being updated by code_block_2 are guaranteed that they will not be updated in parallel by another thread.
Given that info, on which lock can I give up on (if any), if I don’t want to get deadlocks?

2

Answers


  1. You don’t need either lock. Both inserts are atomic.

    ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT — “UPDATE or INSERT”.

    Login or Signup to reply.
  2. Taking explicit table locks is unnecessary in 99% of all cases, and the heavy locks you are planning to take are not only self-excluding (different from what you think), but they also conflict with autovacuum, which will lead to table bloat and maybe worse problems.

    If your transactions affect only a single row in clicks, they can never deadlock, so you should simply do away with the explicit table locks.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search