In order to satisfy concurrency in the ACID properties, every relational database implements locking. This locking may cause one or more sessions to wait until the lock has been released by a session holding the lock. Much of this information may be available to see in real time by querying the pg_locks view, but it would not produce an accurate view. For this purpose, we could use the facility available within PostgreSQL to log all the SQL statements that are waiting due to a lock acquired by another SQL. Similar to locks, we could also log when multiple sessions are involved in a deadlock. In this recipe, we shall discuss the steps to enable logging of such waits due to locking and deadlocks in PostgreSQL.
Getting ready
Locks are logged for SQL that has been waiting for more than deadlock_timeout amount of time. deadlock_timeout is set to 1 second by default. Increasing this to larger amounts may make the deadlock checks less expensive for...
