Is deadlock possible on first SELECT FOR UPDATE in transaction in PostgreSQL

Question

I've got a Deadlock error

deadlock detected
DETAIL: Process 14565 waits for ShareLock on transaction 1986906143; blocked by process 14567.
Process 14567 waits for ShareLock on transaction 1986906141; blocked by process 14565.
HINT: See server log for query details.
CONTEXT: while locking tuple (966,253) in relation

I have two transactions: Transaction 1 creates some objects and updates a bunch of stuff Transact 2 fails because of deadlock. Based on the Sentry error, it fails on the SELECT FOR UPDATE step. It's the first statement in a transaction, there are only plain SELECT statements before the transaction begins.

PostgreSQL logs are not useful. They show that conflicting queries are SELECT from Transaction 2 and just a COMMIT (no extra info) from the Transaction 1

I thought that for deadlock to appear both transactions should acquire a lock on rows. But in my case it looks like the deadlock appears while the Transaction 2 tries to acquire a lock.

I've read a lot of articles, but didn't find the same situation. Maybe the community could point me in the right direction?

• Melkij: I can assume that you are using deferred foreign key constraints. They are checked exactly at the commit stage and use FOR KEY SHARE, which conflicts with FOR UPDATE.
• Dmitry: Thank you for your reply, @Melkij. Yes, deferred foreign key constraints are used. I thought that FOR KEY SHARE are required if I do INSERT/UPDATE operations? But i'm my case SELECT FOR UPDATE is deadlocked. There are JOINs in SELECT FOR UPDATE, but I assume that Postgres don't do partial lock while waiting for another objects to be unlocked
• Melkij: foreign key checking uses a FOR KEY SHARE lock to make sure the target row exists at commit. That is, the scenario: tx1 commits, tx2 does select for update, tx1 takes a KEY SHARE on row 1, tx2 acquires a lock on row 2, then tx1 wants a lock on row 2, and tx2 wants a lock on row 1. Explicit deadlock, in which someone will be cancelled.
• Dmitry: @Melkij, could you take a look at my comment under this answer dba.stackexchange.com/a/326624/272933? Big thank you for your time. I understand your point and if you could just confirm what I wrote here - it will be great. Because I'm lost a little bit in this situation

Answer 1

If COMMIT is involved in a deadlock, that points to deferred constraints or deferred constraint triggers. Since a single SELECT ... FOR UPDATE can lock more than a single row, there is no reason why it shouldn't be involved in a deadlock (if one statement locks several rows, it does so one after the other rather than in an atomic fashion, so it is possible that some rows are already locked when the statement gets blocked waiting for another lock).

As a first measure, try reducing the lock level to SELECT ... FOR NO KEY UPDATE, which is enough unless you want to DELETE or modify a key column. That would get rid of conflicts with foreign keys. The next idea is to reduce the number of rows that the SELECT ... FOR UPDATE locks. Then try to reduce the duration of these transactions, which is a great way to reduce deadlocks and avoid other trouble.

If all this doesn't help, you'll have to dig deeper and make sure that all transactions acquire locks on rows in the same order.

• Dmitry: Thank you, I thought about it. But does it mean that SELECT ... FOR UPDATE can acquire a partial lock in Postgres? I mean if I do SELECT a.id, b.id FROM author a JOIN books b on b.author_id = a.id in transaction 1, but author is already locked by transaction 2, does it meant that transaction 1 will lock available books and will wait for author to be available? Or transaction 1 will not lock books until author is available? Because I see how deadlock is possible if this is situation 1 (with partial lock), but I haven't been able to reproduce it locally that's why I thought it's 2
• Laurenz Albe: The SELECT in your example has no FOR UPDATE at all, so I am not sure what you are saying. A SELECT ... FORUPDATE will lock one row after the other, so it can be that it already has some rows locked and then blocks trying to lock the next row.
• Dmitry: Ah, sorry, I forgot about FOR UPDATE in my example. So if partial lock is possible, then I understand how does the deadlock appeared. I'll try to reproduce such situation locally, I haven't been able to do it, I looks like Postgres always wait for all rows to be available. But I'm not an expert, so I can definitely be wrong