2

My question is about the default isolation level: "read committed". As I understand, during a read committed transaction, when we update a record, the system puts a lock on that record, so other transactions would wait. Is this correct?

Other: and there's a limit in the config file for the maximum number of locks in a transaction: max_locks_per_transaction.

What happens when that limit is reached? I made a test and nothing happened. Does the system exchange the row-level locks for a full-table lock?

Improve this question

1 Answer 1

Reset to default
3

max_locks_per_transaction does not apply to rows, but to objects. The fact that rows do not count is made explicit in the documentation:

From https://www.postgresql.org/docs/current/static/runtime-config-locks.html

This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited.

The information necessary to row locking is written in the row itself.

Improve this answer
3
  • Thank you. So to reach this limit, I would have to for example create 65 tables and lock them in one transaction?
    – Crouching Kitten
    Aug 28, 2017 at 11:49
  • 1
    @CrouchingKitten: if your transaction is the only one running on the instance, the limit is documented to be max_locks_per_transaction * (max_connections + max_prepared_transactions), so it's much more than max_locks_per_transaction in a default configuration.
    – Daniel Vérité
    Aug 28, 2017 at 12:22
  • When running DDL in tx I see one lock per created table, one per pk, one per index, a few per foreign key and a few for toast. Adding columns seems to be free. So you might reach the default 6400 lock entries if you create 1000 non-trivial tables or drop/create 500. having said that, if you reach the global limit (shared with all transactions) you get a „out of shared memory“ (which is quite missleading) and a transaction rollback. There is no lock escalation in this area, and the max_locks_per_transaction is only a sizing estimate not a per transaction limit (unlike the name suggests).
    – eckes
    Dec 10, 2020 at 18:25

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.