Re: DeadLocks..., DeadLocks...

In response to <tom(at)tacocat(dot)net>:
>
> On 6/14/2007, "Gregory Stark" <stark(at)enterprisedb(dot)com> wrote:
>
> >
> >
> ><tom(at)tacocat(dot)net> writes:
> >
> >> But everyone once in a long while it seems that I hit simultaneaous
> >> execute() statements that deadlock on the insertion.
> >
> >What version of Postgres is this and do you have any foreign key constraints
> >or triggers on the table you're inserting into?
>
> Version 8.2
> This table does not have foreign key constraints on it, but it is the
> source of foreign key constraints on other tables.
> No triggers.
>
> Is that insert the *only* DML
> >you're executing? No updates or deletes?
>
> At the time of the failure, no other DML.
> There are other's but they are on different tables.
> >
> >What do you mean by saying it deadlocks? Do you get a transaction abort with
> >an error about a deadlock detected? Or do you just mean it freezes?
>
> "deadlock detected"
> And the corresponding error I get is a primary key violation on the same
> table.
>
>
> The problem occurs when I have multiple processes acting on what appears
> to be the exact same set of information. I can't really control the
> issue of simultaneous/parallel processing

Put an "ORDER BY" in your SELECT.

I believe the problem is that when this runs from two different places,
the DB may order the returned values in a different order for each one,
which leads to the possibility of two similar inserts deadlocking. Unless
I misunderstand your schema, you should be able to guarantee against
deadlocking by guaranteeing that the SELECT portion will always return
rows in the same order.

--
Bill Moran
http://www.potentialtech.com