pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDeadlocks
7+ messages / 5 participants
[nested] [flat]
* Deadlocks
@ 2001-01-18 18:49 Gena Gurchonok <gena@rt.mipt.ru>
0 siblings, 1 reply; 7+ messages in thread
From: Gena Gurchonok @ 2001-01-18 18:49 UTC (permalink / raw)
To: pgsql-admin
Hello.
It seems to me that I discovered strange PSQL behaviour
I have table logs:
-------------
CREATE TABLE "logs" (
"id" int4 NOT NULL,
"type" int4 NOT NULL,
"dt" timestamp NOT NULL,
"addr" character varying(20)
);
-------------
with indexes:
-------------
CREATE INDEX "logs_id_key" on "logs" using hash ( "id" "int4_ops" );
CREATE INDEX "logs_type_key" on "logs" using hash ( "type" "int4_ops" );
CREATE INDEX "logs_dt_key" on "logs" using hash ( "dt" "timestamp_ops" );
-------------
Each HTTP request in my site inserts one row to this table.
PROBLEM:
Sometimes it writes error message
"NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause."
and row of course is not inserted.
If I drop all indices on this table then such notice is not appearing.
But I need indices on this table.
I have tried this on 7.0.3 and CVS versions.
Gena
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Deadlocks
@ 2001-01-18 21:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gena Gurchonok <gena@rt.mipt.ru>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2001-01-18 21:03 UTC (permalink / raw)
To: Gena Gurchonok <gena@rt.mipt.ru>; +Cc: pgsql-admin
Gena Gurchonok <gena@rt.mipt.ru> writes:
> CREATE INDEX "logs_id_key" on "logs" using hash ( "id" "int4_ops" );
> CREATE INDEX "logs_type_key" on "logs" using hash ( "type" "int4_ops" );
> CREATE INDEX "logs_dt_key" on "logs" using hash ( "dt" "timestamp_ops" );
> Sometimes it writes error message
> "NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause."
> and row of course is not inserted.
> If I drop all indices on this table then such notice is not appearing.
> But I need indices on this table.
Don't use hash indexes; use the default index type (btree). hash
indexes are subject to deadlock under concurrent insertions. Besides,
they do nothing that you can't do with a btree.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Deadlocks ?
@ 2008-05-13 19:18 kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 1 reply; 7+ messages in thread
From: kevin kempter @ 2008-05-13 19:18 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Deadlocks ?
@ 2008-05-13 19:30 Tino Schwarze <postgresql@tisc.de>
parent: kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tino Schwarze @ 2008-05-13 19:30 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Deadlocks ?
@ 2008-05-13 23:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tino Schwarze <postgresql@tisc.de>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2008-05-13 23:00 UTC (permalink / raw)
To: Tino Schwarze <postgresql@tisc.de>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Deadlocks ?
@ 2008-05-13 23:33 kevin kempter <kevin@kevinkempterllc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: kevin kempter @ 2008-05-13 23:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tino Schwarze <postgresql@tisc.de>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Deadlocks ?
@ 2008-05-14 00:49 Alvaro Herrera <alvherre@commandprompt.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2008-05-14 00:49 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Tino Schwarze <postgresql@tisc.de>; 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-05-14 00:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-18 18:49 Deadlocks Gena Gurchonok <gena@rt.mipt.ru>
2001-01-18 21:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-05-13 19:18 Deadlocks ? kevin kempter <kevin@kevinkempterllc.com>
2008-05-13 19:30 ` Re: Deadlocks ? Tino Schwarze <postgresql@tisc.de>
2008-05-13 23:00 ` Re: Deadlocks ? Tom Lane <tgl@sss.pgh.pa.us>
2008-05-13 23:33 ` Re: Deadlocks ? kevin kempter <kevin@kevinkempterllc.com>
2008-05-14 00:49 ` Re: Deadlocks ? Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox