Skip to main content

Return to Question

Post Timeline

tags, minor edit
Source Link
Erwin Brandstetter
Erwin Brandstetter
  • 670.9k
  • 161
  • 1.2k
  • 1.3k

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;

iI 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?

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?

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?

Source Link
Yoneshiro
Yoneshiro
  • 119
  • 7

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?