List and kill all dead lock queries in PostgresSQL

Question

I want to clean my DB processes in PostgreSQL because few queries are in deadlock situation and as a result whole database is slowing down.

So I want to know the best practice to list all deadlock queries and clean them regular basis.

I got this view from PgAdmin.

Define what makes a session deadlocked in your view. Normal deadlocks are resolved automatically. — Laurenz Albe, Mar 03 '19 at 20:00
How do you know it are deadlocks if you are unable to detect (and list) them? — wildplasser, Mar 03 '19 at 20:05
If a deadlock occurs, Postgres will automatically kill one of the transactions involved. What you are seeing are "normal" locks, not deadlocks — , Mar 03 '19 at 22:03

score 2 · Answer 1 · answered Mar 03 '19 at 23:31

As commented by a_horse_with_no_name, what you are seeing in pgadmin are regular locks, not deadlocks.

From the Postgres documentation:

PostgreSQL provides various lock modes to control concurrent access to data in tables. These modes can be used for application-controlled locking in situations where MVCC does not give the desired behavior. Also, most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes.

The screen in pgadmin is probably based on the pg_lock system view.

Locks happen in the normal process of any database. You don't want to kill sessions that generate locks on a regular basis, because that can affect your application and your database in various ways. Instead you probably want to identify (maybe using the pg_lock view), analyze and optimize the queries which are causing issues.

When it comes to the concept of deadlock: that's a specific, abnormal situation that may happen when using lock, where two sessions are mutually waiting for the other. They usually indicate issues in the application logic.

Most RDBMS automatically identify and resolve such blocking situation, and Postgres is no exception:

PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)

score 0 · Answer 2 · answered Mar 03 '19 at 20:37

Here are some queries that should help you: https://wiki.postgresql.org/wiki/Lock_Monitoring

The queries might need a bit of trivial tweaking to work. The queries are a little long, so I won't paste them here, but just know that lock and query status information are available in the db tables pg_catalog.pg_locks and pg_catalog.pg_stat_activity.

You can query and join them just like a normal table to build what you're looking for.

Note though that the best case scenario is using this information to diagnose and trace back to the source of the "deadlocks", not forcefully cancel the queries from the database.

List and kill all dead lock queries in PostgresSQL

2 Answers2