I need some insights on row-level locking in Postgres.

  1. How many shared locks (FOR SHARE) can be concurrently on a particular tuple in a relation .
  2. Will have many shared locks on same tuple cause any performance/or any issue to database.
  3. If such limitations exist, how to configure them and advisable limits.

1 Answer 1


Quote from the manual

PostgreSQL doesn't remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes

(Emphasis mine)

So the only performance issue that you might get, is the number of disk writes heavy locking might cause.

While there is an "unlimited" number of row locks in the system, there is a limit on the number of objects (e.g. tables) that can participate in locking for a single transaction, which is controlled through max_locks_per_transaction

That property controls the number of objects one transaction can hold locks for, not the number of locks. A transaction can lock a million rows in a single table. However the default value of 64 for max_locks_per_transaction means that a transaction can hold locks for at most 64 tables (but e.g. a million rows in each table). So if your transactions never touch more than 64 tables, there is nothing to worry about.

A common reason to increase this value is if you are dropping a lot of tables in a single transaction e.g. through a drop owned by current_user statement to clean up a database.

  • 1
    Supposed that BEGIN; select for update 1 million; END, could you explain about locking a row might cause a disk write? Does it write to WAL or disk ? Does it write to temporary file before end transaction ?
    – Jacky
    Sep 3, 2018 at 7:05
  • The OP asks about shared locks, ie. "SELECT FOR SHARE". See the paragraph just before your quote. Sep 3, 2018 at 8:41
  • Sorry, because I was curious about "quote from the manual".
    – Jacky
    Sep 3, 2018 at 13:22

Your Answer

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

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