Hometowns get selected and possibly inserted in unpredictable ways even from multiple concurrent sessions. The only way I could figure out how to solve it was to force each INSERT hometowns to be in its own transaction.
I don't think an advisory lock would remove the deadlock.
On Sun, Jan 18, 2015 at 10:33 PM, Roxanne Reid-Bennett <rox@tara-lu.com> wrote:
On 1/16/2015 2:41 AM, Jim Nasby wrote:
On 1/15/15 10:57 PM, Roxanne Reid-Bennett wrote:
try this: (if you still get deadlocks, uncomment the advisory lock [thanks Daniel] and try again) Logically I suppose it might run faster to do the select, then insert "if". I almost always write these as insert first - because it's the more restrictive lock.
CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS INTEGER AS $BODY$ DECLARE v_id integer; BEGIN -- perform pg_advisory_xact_lock(hashtext(hometown_name)); BEGIN insert into hometowns (name) select hometown_name where not exists (select id from hometowns where name = hometown_name) returning id into v_id;
And if the advisory lock is used? That presumably creates an exclusive lock on the asset "hometown_name". [in most examples given "Portland, OR".] Would not any other process that runs (this function) on the same asset have to wait for this specific transaction to commit or roll back - blocking the race condition?