Re: getting dead locks with 2 functions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Amir Zicherman wrote:

| 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;

I guess you are more then one istance running of the FUNCTION 1,
you can investigate why, or you can add an order by in the select
in order to avoid ciclic locks dependencies.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBE0cu7UpzwH2SGd4RAqK7AKCK9+vlwi5824pWcTws4Mf4tyOOTgCfeVou
GH24DSGZuFcw0spg5Yb8PLY=
=sbWC
-----END PGP SIGNATURE-----