[BUGS] INSERT deadlocks (bug or feature?)

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[BUGS] INSERT deadlocks (bug or feature?)

evgeny gridasov
Hi everybody!

Recently, I've discovered an interesting feature (or a bug?) of PostgreSql (checked 7.4.x and 8.0.x):

One may define such tables:

create table ref(
id serial primary key,
name text);

create table dat(
id serial,
ref_id int references ref(id),
comment text);

Let us fill the ref table:

insert into ref(name) values('feature');
insert into ref(name) values('bug');

The test case:
For example we have 2 concurrent transactions (tr1 and tr2):

tr1: begin;
tr2: begin;
tr1: insert into dat(ref_id, comment) values (1, 'all ok');
tr2: insert into dat(ref_id, comment) values (2, 'all ok');
tr1: insert into dat(ref_id, comment) values (2, 'lockup');
tr2: insert into dat(ref_id, comment) values (1, 'deadlock');


... and we recieve a deadlock!
Easy to understand why: each insert statement generates query like:
SELECT 1 FROM ONLY "public"."ref" x WHERE "id" = $1 FOR UPDATE OF x


So, is this behaviour of postgresql is a bug or feature?

Thanks,
Eugene.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] INSERT deadlocks (bug or feature?)

Tom Lane-2
evgeny gridasov <[hidden email]> writes:
> ... and we recieve a deadlock!
> Easy to understand why: each insert statement generates query like:
> SELECT 1 FROM ONLY "public"."ref" x WHERE "id" = $1 FOR UPDATE OF x
> So, is this behaviour of postgresql is a bug or feature?

An extremely well-known bug ... which is fixed in CVS tip.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]