Lock Management

deadlock_timeout

• What it does: Sets the amount of time a process will wait for a lock before the deadlock detection mechanism is activated to check for circular dependencies.
• Why it matters: This parameter balances between premature deadlock checks and prolonged lock waits. A lower value detects deadlocks faster but increases the overhead of frequent deadlock checking. A higher value reduces checking overhead but may allow deadlocked transactions to remain blocked longer. Proper tuning is essential for maintaining database responsiveness while minimizing unnecessary deadlock detection cycles.
• Ideal value & Best Practice: Default 1s is reasonable for most OLTP workloads. For systems with high contention or long-running transactions, consider increasing to 2-3s. For highly concurrent systems with frequent deadlocks, you might reduce to 500ms. Monitor deadlock frequency and adjust accordingly.

max_locks_per_transaction

• What it does: Sets the maximum number of object locks that can be held by each transaction, which directly determines the size of the shared lock table.
• Why it matters: This parameter controls the amount of shared memory allocated for lock management. Setting it too low can cause "out of shared memory" errors in transactions that access many objects. Setting it too high wastes memory. The lock table is sized as max_locks_per_transaction * (max_connections + max_prepared_transactions).
• Ideal value & Best Practice: Default 64 is sufficient for most applications. Increase to 128 or 256 for applications that access many tables in single transactions (e.g., ORMs with complex queries or partitioning setups). Monitor lock usage and increase only when necessary to avoid memory waste.

max_pred_locks_per_page

• What it does: Sets the maximum number of individual tuple locks allowed per page before predicate locking escalates to a page-level lock.
• Why it matters: This parameter optimizes predicate locking memory usage for serializable transactions. When many tuples on the same page are locked, upgrading to a page-level lock reduces memory consumption and management overhead. This is particularly important for Serializable isolation level where predicate locking is used extensively.
• Ideal value & Best Practice: Default 2 is generally appropriate. Increase to 4-8 for workloads with very focused updates on specific pages where page-level locking would be too restrictive. Lower values conserve memory but may cause more false-positive serialization failures.

max_pred_locks_per_relation

• What it does: Sets the maximum number of predicate locks (page and tuple locks combined) allowed per relation before escalating to a relation-level lock.
• Why it matters: Controls memory usage for predicate locking by limiting the granularity of locks on individual relations. When a transaction locks many pages or tuples in the same relation, this mechanism prevents excessive memory consumption by upgrading to a single relation-level lock.
• Ideal value & Best Practice: Default 32 works for most scenarios. For large tables with widespread access patterns, consider increasing to 64-128. For very large systems using serializable isolation, monitor predicate lock usage and adjust accordingly to balance memory usage and concurrency.

max_pred_locks_per_transaction

• What it does: Sets the maximum number of predicate locks each transaction can hold, determining the size of the shared predicate lock table.
• Why it matters: This parameter directly controls the memory allocated for predicate locking in serializable transactions. The predicate lock table is sized as max_pred_locks_per_transaction * (max_connections + max_prepared_transactions). Insufficient capacity can cause serialization failures even when no actual conflicts exist.
• Ideal value & Best Practice: Default 64 is sufficient for most applications not using serializable isolation. For serializable isolation workloads, increase to 128-256 based on transaction complexity. Monitor predicate lock usage and adjust gradually, as excessive values can consume significant shared memory.

---

Try pghealth Free Today 🚀

Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial — no installation required.

👉 Start Free Trial