Re: Lock problem

I'm sorry Tom.

This happens (at least) both in 8.4 and 9.0. I've just tested it in 2
different databases with the same results.
I have workarounds to this, but I was wondering what could be the actual
root of the problem, in order to (try to) achieve the best solution
possible.

Following is a test case with which you can reproduces the issue.
Statements should be executed in the following order.
-- session T1 means that the following lines should be executed from pg
session 1.
-- session T2 means that the following lines should be executed from pg
session 2.

create table b (
id bigint not null,
x double precision,
constraint pk_b primary key (id));

create table a (
id bigint not null,
id_b bigint,
x double precision,
constraint pk_a primary key (id),
constraint fk_b foreign key (id_b) references b (id));

insert into b (id, x) values (1, 0);
insert into a (id, id_b, x) values (1, 1, 0);

-- session T1
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;

-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- Query returned
successfully: 1 row affected, 47 ms execution time.
rollback;

-- session T1
rollback;
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;
update a set x = x + 1 where id = 1;

-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- ERROR: could not obtain
lock on row in relation "b". SQL state: 55P03

Regarding my original question, I would like to know if this is a known
issue/feature/bug/unwanted optimization consequence/or is it just a normal
behavior that I should've had predicted.

Best regards,
Eduardo.

On Wed, Sep 7, 2011 at 9:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Eduardo Piombino <drakorg(at)gmail(dot)com> writes:
> > I don't see how a new update to the same record in A, makes the
> difference
> > to allow or deny the lock on a row on table B;
>
> I think it's probably explained by this:
>
> > PS: The only relation between A and B is that A has a two FKs to B, but
> none
> > of them are even included in the updates.
>
> IIRC there are some optimizations in the FK stuff that don't apply once
> a single transaction has updated a relevant row more than once. You
> haven't given enough details (not even a PG version) to be sure about
> it, but that's what I'd bet on.
>
> regards, tom lane
>