Skip site navigation (1) Skip section navigation (2)

Re: Logging conflicted queries on deadlocks

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Logging conflicted queries on deadlocks
Date: 2008-02-28 12:40:16
Message-ID: 20080228124016.GE4764@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
ITAGAKI Takahiro wrote:

> I wrote a small patch to log conflicted queries.
> With my patch, deadlock message will change as following:
> 
> | ERROR:  deadlock detected
> | DETAIL:  Process 3468 waits for ShareLock on transaction 451; blocked by process 4068.
> |         STATEMENT: UPDATE test SET i = i WHERE i = 1;    <- ***conflicted query***
> |         Process 4068 waits for ShareLock on transaction 450; blocked by process 3468.
> | STATEMENT:  UPDATE test SET i = i WHERE i = 2;

Cute.

> There are some open issues. One of the issues is that the killed query is
> logged by log_min_error_statement, but conflicted query is logged in DETAIL.
> Killed query is logged only on server but conflicted queries are
> sent to both server and client. In addition, if log_min_error_statement
> is less than ERROR level, only the conflicted queries are logged.
> -- it's inconsistent and unsymmetric.

Perhaps it could be shown in CONTEXT, like so:

| ERROR:  deadlock detected
| DETAIL:  Process 3468 waits for ShareLock on transaction 451; blocked by process 4068.
|         Process 4068 waits for ShareLock on transaction 450; blocked by process 3468.
| STATEMENT:  UPDATE test SET i = i WHERE i = 2;
| CONTEXT: process 3468: UPDATE test SET i = i WHERE i = 1;

I think it's useful to show the PID of each statement, for the case
where there are more than two processes deadlocked.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to

Responses

pgsql-hackers by date

Next:From: Aaron SpiteriDate: 2008-02-28 12:52:14
Subject: Silly Newbie question
Previous:From: Dave PageDate: 2008-02-28 12:34:26
Subject: Re: OSSP can be used in the windows environment now!

pgsql-patches by date

Next:From: Alvaro HerreraDate: 2008-02-28 13:34:27
Subject: Re: Logging conflicted queries on deadlocks
Previous:From: Zdeněk KotalaDate: 2008-02-28 11:54:11
Subject: Re: lc_time and localized dates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group