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

Re: BUG #5443: Undetected deadlock situation

From: Claudio Freire <claudio(at)livra(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5443: Undetected deadlock situation
Date: 2010-04-30 14:01:11
Message-ID: 1272636071.3432.5.camel@klauss.livra.local (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, 2010-04-30 at 08:47 -0500, Kevin Grittner wrote:

> Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>  
> > This is not a deadlock.
>  
> Agreed.
>  
> > It's just two locks on the same table
>  
> Not as I read it.
>  
> > (and maybe a bit more readable formatting).
>  
> Eliminating null columns and mangling column headers for length, I
> get this:
>  
> >>   locktype    |  tranid  | virtualx |  pid  |      mode     | gr
> >> transactionid | 39773877 | 63/15761 | 11157 | ShareLock     | f
> >> transactionid | 39773877 | 4/10902  |  6421 | ExclusiveLock | t
>  
> So it looks like two locks on the same transaction ID by different
> transactions.  How does that happen?


Exactly, I found that odd.

And too, you're right in that looking at the locks doesn't suggest a
deadlock.

But the reality of pid 6421 stopping making any progress whatsoever
makes me think about an unregistered lock - perhaps a mutex somewhere
that isn't tracked by pg_locks.

When it doesn't happen, the related queries take at most a few minutes
to update ~30k rows. When the deadlock happens, though, same number of
rows (same query in fact), it remained "working" for day and a half, no
progress, every other update on the same page blocked by that big
update. The server's load drops suggesting the big update isn't doing
much other than waiting.

If I manually cancel the small query, for instance, the big update
starts consuming resources again (suggesting progress), but quickly
deadlocks again (because my app retries the query I just cancelled). If
I manually cancel the big query, of course, everything goes back to
normal (after the little while it takes to roll back).

I can't reproduce it in a test environment because it's tied to
concurrency patterns that I couldn't reproduce yet. I will keep trying
though.

In response to

pgsql-bugs by date

Next:From: Robert HaasDate: 2010-04-30 14:03:52
Subject: Re: [9.0beta5/cvs head] build failure due to unchecked results
Previous:From: Kevin GrittnerDate: 2010-04-30 13:47:50
Subject: Re: BUG #5443: Undetected deadlock situation

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