Обсуждение: Deadlocks ?

Поиск
Список
Период
Сортировка

Deadlocks ?

От
kevin kempter
Дата:
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.
 

Re: Deadlocks ?

От
Tino Schwarze
Дата:
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

Re: Deadlocks ?

От
Tom Lane
Дата:
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

Re: Deadlocks ?

От
kevin kempter
Дата:
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


Re: Deadlocks ?

От
Alvaro Herrera
Дата:
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