From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | Daniel Verite <daniel(at)manitou-mail(dot)org> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Simple Atomic Relationship Insert |
Date: | 2015-01-17 00:07:41 |
Message-ID: | CAAXGW-wGT5Csnx4K7wJZm-nGvGKugrKvgy8JBatzLzFdWtTa-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Hicks | 2015-01-17 12:27:49 | Alternatives to a unique indexes with NULL |
Previous Message | Adrian Klaver | 2015-01-16 18:52:20 | Re: sslcompression / PGSSLCOMPRESSION not behaving as documented? |