Search code examples
sqlpostgresqlconcurrencysql-updatedeadlock

Deadlock when using SELECT FOR UPDATE

I noticed that concurrent execution of simple and identical queries similar to

BEGIN;  
SELECT files.data FROM files WHERE files.file_id = 123 LIMIT 1 FOR UPDATE;
UPDATE files SET ... WHERE files.file_id = 123;
COMMIT;

lead to deadlock which is surprising to me since it looks like such queries should not create a deadlock. Also: it is usually takes only milliseconds to complete such request. During such deadlock situation if I run: 

 SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query, 
 blockinga.pid AS blocking_pid, blockinga.query as blocking_query FROM pg_catalog.pg_locks blockedl
 JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
 JOIN pg_catalog.pg_locks blockingl     ON(blockingl.transactionid=blockedl.transactionid
 AND blockedl.pid != blockingl.pid)
 JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
 WHERE NOT blockedl.granted;

I see both of my identical select statements listed for blocked_pid and blockin_pid for whole duration of the deadlock.

So my question is: Is it normal and expected for queries that try to select same row FOR UPDATE to cause deadlock? And if so, what is the best strategy to avoid deadlocking in this scenario?

Solution

  • Your commands are contradicting.

    If files.file_id is defined UNIQUE (or PRIMARY KEY), you don't need LIMIT 1. And you don't need explicit locking at all. Just run the UPDATE, since only a single row is affected in the whole transaction, there cannot be a deadlock. (Unless there are side effects from triggers or rules or involved functions.)

    If files.file_id is not UNIQUE (like it seems), then the UPDATE can affect multiple rows in arbitrary order and only one of them is locked, a recipe for deadlocks. The more immediate problem would then be that the query does not do what you seem to want to begin with.

    The best solution depends on missing information. This would work:

    UPDATE files
SET    ...
WHERE  primary_key_column = (
         SELECT primary_key_column
         FROM   files 
         WHERE  file_id = 123
         LIMIT  1
     --  FOR    UPDATE SKIP LOCKED
         );

    No BEGIN; and COMMIT; needed for the single command, while default auto-commit is enabled.

    You might want to add FOR UPDATE SKIP LOCKED (or FOR UPDATE NOWAIT) to either skip or report an error if the row is already locked.

    And you probably want to add a WHERE clause that avoids processing the same row repeatedly.

    More here: