Re: Debugging deadlocks

On Sun, 27 Mar 2005, Qingqing Zhou wrote:

>
> "Michael Fuhr" <mike@fuhr.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.