Re: Simple Atomic Relationship Insert

Is there a way to force a new private transaction in a FUNCTION? That seems
like it would be a good solution here because I could simply do the insert
in the RAISE within its own private transaction. Then on the next iteration
of the loop (as long as I don't have repeatable reads) it should be picked
up. And there should only be a quick recoverable deadlock.

On Fri, Jan 16, 2015 at 7:49 AM, Daniel Verite <daniel(at)manitou-mail(dot)org>
wrote:

> Robert DiFalco wrote:
>
> > I must be doing something wrong because both of these approaches are
> giving
> > me deadlock exceptions.
>
> Deadlocks are to be expected if the INSERTs are batched within a single
> transaction and there are several sessions doing this in parallel.
>
> Given that there's an unique constraint on hometowns(name), if this
> sequence
> happens (not even considering the "users" table to simplify):
>
> Session #1: begin;
> Session #2: begin;
> Session #1: insert into hometowns(name) values('City1');
> Session #2: insert into hometowns(name) values('City2');
> Session #1: insert into hometowns(name) values('City2');
> => Session #1 is put to wait until Session #2 commits or rollbacks
> Session #2: insert into hometowns(name) values('City1');
> => Session #2 should wait for Session #1 which is already waiting for
> Session #2: that's a deadlock
>
>
> It does not mean that the code meant to insert one user and the town
> without
> race condition is incorrect by itself. The unique_violation handler is not
> called in this scenario because the candidate row is not yet committed by
> the
> other session. This would work in an OLTP scenario when each "user" is
> commited after processing it.
>
> Avoiding deadlocks between parallel batches is a different problem than
> avoiding race conditions. If you have the above issue, I don't think you
> may
> solve it by tweaking the code of an individual process. It needs to be
> rethought at the level above, the one that initiates this in parallel and
> dictates the commit strategy.
>
> Best regards,
> --
> Daniel
> PostgreSQL-powered mail user agent and storage:
> http://www.manitou-mail.org
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>