February 9th, 2012 by depesz | Tags: , , , , , , | 3 comments »
Did it help? If yes - maybe you can help me?

On 26th of January, Magnus Hagander committed patch:

Adds a counter that tracks number of deadlocks that occurred in
each database to pg_stat_database.
 
Magnus Hagander, reviewed by Jaime Casanova

It's similar to previously described patch – another column in pg_stat_database:

$ select datname, deadlocks from pg_stat_database;
  datname  │ deadlocks
───────────┼───────────
 template0 │         0
 depesz    │         0
 jab       │         0
 pgdba     │         0
 postgres  │         0
 template1 │         0
(6 rows)

This is ever increasing counter, that gets incremented whenever Pg will detect deadlock. So, let's see:

psql1> create table z as select i from generate_series(1,2) as i;
SELECT 2
 
psql1> begin;
BEGIN
 
psql1> delete from z where i = 1;
DELETE 1
 
psql2> begin;
BEGIN
 
psql2> delete from z where i = 2;
DELETE 1
 
psql2> delete from z where i = 1;
DELETE 1
 
psql1> delete from z where i = 2;
ERROR:  deadlock detected
DETAIL:  Process 29419 waits for ShareLock on transaction 866; blocked by process 29555.
Process 29555 waits for ShareLock on transaction 865; blocked by process 29419.
HINT:  See server log for query details.
 
psql1> rollback;
ROLLBACK
 
psql1> select datname, deadlocks from pg_stat_database;
  datname  │ deadlocks
───────────┼───────────
 template0 │         0
 depesz    │         1
 jab       │         0
 pgdba     │         0
 postgres  │         0
 template1 │         0
(6 rows)

Counter is incremented. Thanks to this it will be simpler to see if deadlocks happen (for details you still can look in logs).

It's really good to see monitoring of Pg state simplified.

  1. 3 comments

  2. Feb 13, 2012

    TBH, I’m not too excited by this. It makes an annoying task simpler, yes, but it doesn’t really give you enough information to solve the problem. For that you’ll still have to go trolling through the log files to dig out the relevant information, and if you are going to do that, you can pretty easily put the counts into a trending solution.

  3. # Oliver Charles
    Jul 31, 2012

    @Robert: It might not help you fix the problem, but it does help with event correlation. For example, if you have been graphing the deadlock count, you might overlay when you’ve done deployments, and determine which set of changes may have introduced deadlock problems.

  4. # Robert Lichtenberger
    Aug 14, 2014

    It is a great feature for monitoring systems. Thanks.

Leave a comment