Re: Debugging deadlocks

"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.

Regards,
Qingqing