Re: row-level deadlock problem

On Sat, 27 Nov 2004, Tom Lane wrote:

> > 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).
>
> That's a bit hard to believe; once the rows are entered in the index
> their relative order won't change anymore, so it's real hard to see how
> two indexscans could visit them in different orders.
>
> IIRC you said that these commands were being done inside plpgsql
> functions, so it's possible that the planner is doing something
> different with the parameterized plans than what you see in a simple
> EXPLAIN with values already inserted. Still, it's odd that you might
> get different plans in different executions of the same function.
>
> I think there is some factor we're not seeing here. Is it possible that
> one backend has a cached plan much older than the other one, and that
> the planner's plan choice changed over time?
Theorically possible, but chances are very low.
I think condition which now use index is most selective all the time, even
if not, I don't believe planner could change plan so quickly with dataset
change. Also, connections to this database have rather short life.
I can add EXECUTE in front of UPDATE to verify cached plan
theory. I'll try that during the week, but I don't think it'd change
anything.
I agree it looks like we're missing something. Any other theories,
suggestions what should I check?
I'll try to isolate problem to test case by writing scripts to simulate actual load but it'll take same time.
Regards.
--
Kamil Kaczkowski
kamil(at)kamil(dot)eisp(dot)pl