CODEWITHSUNDEEP
postgresql
Ekene Madu
Ekene Madu

Reputation: 1

Does postgres guarantee complete deadlock detection?

I understand that after waiting deadlock_timeout begins deadlock detection, and aborts on of the transactions. However I have a situation where transactions appear to be stucking (waiting on a SELECT ... FOR UPDATE) for hours with no resolution. My logs do show that postgres has detected some deadlocks, but I am wondering if it's possible there are more complex deadlocks that postgres is unable to detect?

Upvotes: 0

Views: 1295

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

PostgreSQL will detect all deadlocks that are entirely in the database. If it fails to detect such a deadlock, that would be a bug that you should report (with better diagnostics than you show here).

However, PostgreSQL cannot detect a deadlock that is partly outside the database. In your case, there is another database session that holds a conflicting lock, so your SELECT ... FOR UPDATE is stuck until that other session ends its transaction. Now the process with that other database session is probably not stuck inside the database, but blocked outside the database (or you simply forgot to close the transaction, which is an application bug).

If you want to keep sessions waiting for ever behind a lock, set lock_timeout appropriately. But usually it is better to keep sessions from holding transactions open indefinitely by setting idle_in_transaction_session_timeout. But the correct solution is to fix your application.

Upvotes: 2

Related Questions