2

We having this strange behaviour with ExclusiveLock in one of our database running PostgreSQL 13. I am not able to dig what leads to this lock as lock info is from a monitoring tool.

From what I see from docs ExclusiveLock only acquired by refreshing materialised view, however we don't have any materialised views in our database. Bit of research I ended up in this blog https://blog.heroku.com/curious-case-table-locking-update-query and the case this guy shares similar to mine , as I can see a few RowExclusiveLock during this period and few queries updating same row concurrently. However I coudn't find any official docs on PostgreSQL behaviour on lock escalation just like other databases do.

Does Postgres escalate lock in rare cases? What cases can lead to escalations?

Improve this question

1 Answer 1

Reset to default
3

PostgreSQL doesn't escalate locks (OK, I am lying — predicate locks on index pages get escalated to table locks when the index is dropped, but that is not what's going on here). PostgreSQL avoids the need for lock escalation by storing row locks on the row itself rather than in the shared memory locking table.

If you look at the pg_locks entry more closely, you will see that the exclusive lock is not on a table, but on a transaction. Every transaction has an exclusive lock on its own transaction number. If a session encounters a conflicting row lock, it waits for the locking transaction to end by waiting for a share lock on the transaction number. That's what you must be seeing.

Perhaps this article can help you understand the problem.

Improve this answer
2
  • Oh got it, I knew about this row locking but still got confused. I tried to simulate this locking scenario using multiple threads but couldn't make a ExclusiveLock on table. Commented Oct 30, 2024 at 3:42
  • Right. As you said, only few operations take an exclusive lock on a table. That's why I felt safe in guessing that it must have been a transaction lock. Commented Oct 30, 2024 at 3:44

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.