Re: row-level deadlock problem

On Sat, 27 Nov 2004, Alvaro Herrera wrote:

> > You're mistaken; it takes a row lock on each row it updates.  I'm not
> > sure why the two UPDATEs are visiting the same rows in different orders,
> > but if they do the failure is certainly possible.
>
> One of them could be using an indexscan while the other is not.  If the
> heap is in reverse order compared to the scan, that would explain it.
>
> Is there a solution to this problem?  The FK deadlock problem can be
> fixed with shared row locks, but this is a different one.
>
>
In my case deadlock happens between two identical statements executed
from different transactions and they have the same execution plan(index
scan on one attribute - 'color' in schema I presented).
Also, there's no other modifing statements in logs at the time so only
thing that can change scan order is the first UPDATE called of two
involved in deadlock(or the same statement exeduted from some third
transaction, but I don't think so).
Could someone with more knowledge on internals of btree indexes provide
info on how this can happen, what causes index scan order to change?
Thinking of it, possibility of such event should be very low, but I'm
getting 2000 deadlocks during busy day.
Regards.
--
Kamil Kaczkowski
kamil@kamil.eisp.pl