Re: getting dead locks with 2 functions

i am running multiple threads that are calling this function at the
same time. i want to be able to do that and have the locking in
postgresql take care of locking the selected rows of each thread. why
is the function not thread safe? how do i make it so it is?

thanx, amir

On Fri, 06 Aug 2004 10:54:07 +0200, Gaetano Mendola <mendola(at)bigfoot(dot)com> wrote:
> -----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-----
>
>