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
You don’t need either lock. Both inserts are atomic.
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.