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?

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.

  • 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 '17 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 '17 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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