Re: Simple Atomic Relationship Insert

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