postgresql deadlock

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 2053Insert Comment                              Insert CommentLock Content#935967 SHARE  (performed by fkey)                              Lock Content#935967 SHARE                                (performed by fkey)TriggerLock 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 UPDATEINSERT 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.