High Performance PostgreSQL for Rails

Exploring Lock Type Queues

Statements requesting access for a lock type are put into a queue where they wait in order.

If transaction B is waiting to acquire the lock type that transaction A holds, B must wait for A.

As described in “PostgreSQL and the Lock Queue,”^[113] the pids for the backend processes are logged, including the statement holding a lock type and the statements waiting to acquire it.

Some helpful lock parameters to set are log_lock_waits to on and setting a value for deadlock_timeout to gain visibility into locks or blocked queries. When these timeouts cause cancellations, the events and queries will be logged to postgresql.log. See the post, “Cost of log_lock_waits and deadlock_timeout,”^[114] for more.

Take a look at the following ...