getting dead locks with 2 functions

From: Amir Zicherman <amir(dot)zicherman(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: getting dead locks with 2 functions
Date: 2004-08-05 22:42:12
Message-ID: 27a5b7d1040805154264640a23@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Josué Maldonado 2004-08-05 22:50:19 Insert into sintax
Previous Message Greg Stark 2004-08-05 21:12:08 Re: most idiomatic way to "update or insert"?