postgresql deadlock

Question

Sometimes postgresql raise error deadlocks.

In trigger for table setted FOR UPDATE.

Table comment:

http://pastebin.com/L1a8dbn4

Log (INSERT sentences is cutted):

2012-01-26 17:21:06 MSK ERROR:  deadlock detected
2012-01-26 17:21:06 MSK DETAIL:  Process 2754 waits for ExclusiveLock on tuple (40224,15) of relation 735493 of database 734745; blocked by process 2053.
Process 2053 waits for ShareLock on transaction 25162240; blocked by process 2754.
Process 2754: INSERT INTO comment (user_id, content_id, reply_id, text) VALUES (1756235868, 935967, 11378142, 'text1') RETURNING comment.id;
Process 2053: INSERT INTO comment (user_id, content_id, reply_id, text) VALUES (4071267066, 935967, 11372945, 'text2') RETURNING comment.id;
2012-01-26 17:21:06 MSK HINT:  See server log for query details.
2012-01-26 17:21:06 MSK CONTEXT:  SQL statement "SELECT comments_count FROM content WHERE content.id = NEW.content_id FOR UPDATE"
PL/pgSQL function "increase_comment_counter" line 5 at SQL statement
2012-01-26 17:21:06 MSK STATEMENT:  INSERT INTO comment (user_id, content_id, reply_id, text) VALUES (1756235868, 935967, 11378142, 'text1') RETURNING comment.id;

And trigger on table comment:

CREATE OR REPLACE FUNCTION increase_comment_counter() RETURNS TRIGGER AS $$
DECLARE
comments_count_var INTEGER;
BEGIN
    SELECT INTO comments_count_var comments_count FROM content WHERE content.id = NEW.content_id FOR UPDATE;
    UPDATE content SET comments_count = comments_count_var + 1, last_comment_dt = now()  WHERE content.id = NEW.content_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;



CREATE TRIGGER increase_comment_counter_trigger AFTER INSERT ON comment FOR EACH ROW EXECUTE PROCEDURE increase_comment_counter();

Why it can happens?

Thanks!

Answer 1

These are two comments being inserted with the same content_id. Merely inserting the comment will take out a SHARE lock on the content row, in order to stop another transaction deleting that row until the first transaction has completed.

However, the trigger then goes on to upgrade the lock to EXCLUSIVE, and this can be blocked by a concurrent transaction performing the same process. Consider the following sequence of events:

Txn 2754                      Txn 2053
Insert Comment
                              Insert Comment
Lock Content#935967 SHARE
  (performed by fkey)
                              Lock Content#935967 SHARE
                                (performed by fkey)
Trigger
Lock Content#935967 EXCLUSIVE
(blocks on 2053's share lock)
                              Trigger
                              Lock Content#935967 EXCLUSIVE
                              (blocks on 2754's share lock)

So- deadlock.

One solution is to immediately take an exclusive lock on the content row before inserting the comment. i.e.

SELECT 1 FROM content WHERE content.id = 935967 FOR UPDATE
INSERT INTO comment(.....)

Another solution is simply to avoid this "cached counts" pattern completely, except where you can prove it is necessary for performance. If so, consider keeping the cached count somewhere other than the content table-- e.g. a dedicated table for the counter. That will also cut down on the update traffic to the content table every time a comment gets added. Or maybe just re-select the count and use memcached in the application. There's no getting round the fact that wherever you store this cached count is going to be a choke point, it has to be updated safely.