Re: Deadlock with tsearch2 index ...

On T, 2005-05-31 at 14:41 -0300, Marc G. Fournier wrote:
> Just want to make sure that this is, in fact, what is expected:
>
> client1: begin;
> client1: update articles set some_col = <foo> where id = <bar>;
> client2: update articles set some_col2 = <foo2> where id = <bar>;
> client1: update articles set some_col3 = <foo> where id = <bar>;
> client1: ** deadlock **
>
> client2 can't finish its 'transaction', and is therefore preventing
> client1 from continuing ... ?
>
> Assuming that this is, in fact, 'normal', is there a way of breaking the
> deadlock?
>
> If this is *not*, in fact, 'normal', is there any more information that I
> can provide to debug this?

This is the infamous 'lock for update' of foreign key master.

It should be fixed for 8.1 by creating a shared row lock, but for 8.0
and below you can either drop the FK constraint, or replace it with a
CHECK using a function that does a select.

--
Hannu Krosing <hannu(at)tm(dot)ee>