getting dead locks with 2 functions - Mailing list pgsql-general

From amir
Subject getting dead locks with 2 functions
Date
Msg-id 200408052141.CDF46004@ms7.netsolmail.com
Whole thread Raw
List pgsql-general

I have the following 2 functions and I’m getting deadlocks when I call them from multiple threads.  The first, I’m not sure why because I’m doing a select for update.  The second I’m doing an insert on, and I thought insert will automatically do a lock as it inserts:

 

 

-------------FUNCTION 1: -------------

 

CREATE OR REPLACE FUNCTION public.select_pend_visitation_for_unvisited_links(int4)

  RETURNS SETOF record AS

'

DECLARE

urlrow RECORD;

BEGIN

FOR urlrow in EXECUTE \'SELECT * FROM "URL" WHERE visited=1::int2 LIMIT \' || $1::int4 || \'FOR UPDATE\'

LOOP

            UPDATE "URL" SET visited=2 WHERE "URLID"::int8 = urlrow."URLID"::int8;

            RETURN NEXT urlrow;

END LOOP;

RETURN;

END;

'

  LANGUAGE 'plpgsql' VOLATILE;

 

-------------FUNCTION 2: -------------

 

 

CREATE OR REPLACE FUNCTION public.add_link_to_url_table(varchar, int8, int4, int2, bool, int2)

  RETURNS void AS

'

INSERT INTO "URL"

("rootlessURLString","rootURLID","rootURLIDPartition","visited",

"createdAt","updatedAt","isValid","URLType")

VALUES ($1, $2, $3, $4, now(), now(), $5, $6 );

'

  LANGUAGE 'sql' VOLATILE;

 

Thanx for the help,

amir

 

 

pgsql-general by date:

Previous
From: Oscar Tuscon
Date:
Subject: Sequence question
Next
From: "Scott Marlowe"
Date:
Subject: Re: shared_buffers Question