Обсуждение: Deadlocks ?
Hi All;
we seem to be getting deadlock quite a lot. We have a python process that does the following and we keep getting deadlocks. Is this a real deadlock or maybe a wait condition? Any help in debugging / resolving this would be much appreciated.
Thanks in advance
cursor.execute("lock player_log_uuid in share update exclusive mode")
Traceback (most recent call last):
File "StatsParser.py", line 740, in InsertData
cursor.execute("lock player_log_uuid in share update exclusive mode")
ProgrammingError: deadlock detected
DETAIL: Process 23098 waits for ShareUpdateExclusiveLock on relation 428126 of database 427376; blocked by process 23916.
Process 23916 waits for ShareLock on transaction 46802680; blocked by process 23098.
On Tue, May 13, 2008 at 01:18:24PM -0600, kevin kempter wrote:
> we seem to be getting deadlock quite a lot. We have a python process
> that does the following and we keep getting deadlocks. Is this a real
> deadlock or maybe a wait condition? Any help in debugging / resolving
> this would be much appreciated.
An easy way to avoid deadlocks is to lock tables always in the same
order.
> cursor.execute("lock player_log_uuid in share update exclusive mode")
>
>
> >Traceback (most recent call last):
> > File "StatsParser.py", line 740, in InsertData
> > cursor.execute("lock player_log_uuid in share update exclusive
> >mode")
> >ProgrammingError: deadlock detected
> >DETAIL: Process 23098 waits for ShareUpdateExclusiveLock on
> >relation 428126 of database 427376; blocked by process 23916.
> >Process 23916 waits for ShareLock on transaction 46802680; blocked
> >by process 23098.
I've never figured out how to resolve the "lock on transaction" to
something understandable...
HTH,
Tino.
--
"What we resist, persists." (Zen saying)
www.craniosacralzentrum.de
www.forteego.de
Tino Schwarze <postgresql@tisc.de> writes:
> On Tue, May 13, 2008 at 01:18:24PM -0600, kevin kempter wrote:
> ProgrammingError: deadlock detected
> DETAIL: Process 23098 waits for ShareUpdateExclusiveLock on
> relation 428126 of database 427376; blocked by process 23916.
> Process 23916 waits for ShareLock on transaction 46802680; blocked
> by process 23098.
> I've never figured out how to resolve the "lock on transaction" to
> something understandable...
It's presumably waiting for a row lock that the other transaction
has got. We don't keep enough information about row locks in memory
to give a better error message (because we could run out of memory
if we tried :-()
regards, tom lane
On May 13, 2008, at 5:00 PM, Tom Lane wrote: > Tino Schwarze <postgresql@tisc.de> writes: >> On Tue, May 13, 2008 at 01:18:24PM -0600, kevin kempter wrote: >> ProgrammingError: deadlock detected >> DETAIL: Process 23098 waits for ShareUpdateExclusiveLock on >> relation 428126 of database 427376; blocked by process 23916. >> Process 23916 waits for ShareLock on transaction 46802680; blocked >> by process 23098. > >> I've never figured out how to resolve the "lock on transaction" to >> something understandable... > > It's presumably waiting for a row lock that the other transaction > has got. We don't keep enough information about row locks in memory > to give a better error message (because we could run out of memory > if we tried :-() > > regards, tom lane > If that's true does it make sense to play with a timeout value (I assume the timeout is configurable somewhere in postgresql.conf) in an effort to tune for this ? /Kevin > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
kevin kempter wrote: >>> On Tue, May 13, 2008 at 01:18:24PM -0600, kevin kempter wrote: >>> ProgrammingError: deadlock detected >>> DETAIL: Process 23098 waits for ShareUpdateExclusiveLock on >>> relation 428126 of database 427376; blocked by process 23916. >>> Process 23916 waits for ShareLock on transaction 46802680; blocked >>> by process 23098. > If that's true does it make sense to play with a timeout value (I assume > the timeout is configurable somewhere in postgresql.conf) in an effort to > tune for this ? If there's a deadlock, it's gonna wait until detected, so no matter how large you set the timeout, there's no getting out of it. If it were a plain lock wait, it would make sense to try to increase the timeout. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support