Re: Debugging deadlocks

On Sun, 27 Mar 2005, Qingqing Zhou wrote:

>
> "Michael Fuhr" <mike(at)fuhr(dot)org> writes
> > To make sure the referenced key can't change until the transaction
> > completes and the referencing row becomes visible to other transactions
> > (or is rolled back) -- otherwise other transactions could change
> > or delete the referenced key and not know they'd be breaking your
> > referential integrity. The current implementation supports only
> > exclusive row-level locks (SELECT FOR UPDATE), but I think Alvaro
> > might be working on shared row-level locks for a future release.
> >
>
> The code to process RI could be optimized a little bit. See the following
> case:
>
> user -1-
> test=# begin;
> BEGIN
> test=# delete from a where i = 2;
> DELETE 1
>
> user -2-
> test=# update a set i = 3 where i = 1;
> ERROR: update or delete on "a" violates foreign key
> constraint "b_i_fkey" on "b"
> DETAIL: Key (i)=(1) is still referenced from table "b".
> test=# update a set i = 2 where i = 1;
> /* User 2 will be blocked here */
>
> Blocking user 2 is strange and not necessary? Since the sever should first
> check the where-clause (this is true in ordinary UPDATE). If not, just
> report an error as the fomer statement.

Well, that's not the foreign key necessarily. I don't have a machine to
test on at the moment (machine currently dead), but I think the same
happens without a foreign key constraint due to the unique/primary key
constraint on a.i.