Sorry for the noob question, but what is the correct table level lock mode to allow reading from a table but not writing to it?

The scenario that I have is that I want to prevent two tables from being written to while a third table is receiving an update. As soon as that update completes it's okay for the other two tables to unlock and receive additional updates.

If it isn't possible to prevent those tables from being written to while still allowing them to be read from then I could also just lock them entirely so they can't be written to or read from during the lock.

Here is the documentation page, I just don't understand what it's saying: https://www.postgresql.org/docs/current/explicit-locking.html

  • What is the actual problem you are trying to solve here? This sounds like a X-Y Problem
    – user1822
    Commented May 15, 2020 at 6:24

2 Answers 2


I agree the docs are not the easiest to understand when you don't already know what they are saying. But once you know that the answer is lock table foobar in exclusive mode, I think it is pretty clear in hindsight.


Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

Although there are probably better ways to do whatever it is you are trying to do, like maybe increasing your isolation level. Although this may be the easiest one to understand, and that has some value.


Don't do that. Explicitly locking tables is almost always the wrong thing to do. If you describe the use case in more detail, I am confident that a better solution can be found.

Locking tables frequently will keep autovacuum from doing its important work on the table, and eventually an anti-wraparound autovacuum job will come along and block you, bringing processing to a standstill.

  • After your comment I rethought this process. I'm going to try something that doesn't require row locking. I've up voted this post too. Thanks.
    – James
    Commented May 15, 2020 at 23:12
  • 1
    Row locking is fine. I am cautioning you against using explicit table locking. Commented May 18, 2020 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.