-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Amir Zicherman wrote:
| 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 <[EMAIL PROTECTED]> wrote: | | 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;
This function *is* thread safe. The only fault here is that this function not "designed" to be used in an multithread environment because you are not taking any "policy" for locks resources.
Lock always the line in the same order so you avoid cyclic locks dependencies.
Your select must appear like this:
SELECT * FROM "URL" WHERE visited=1 ORDER BY oid LIMIT $1 FOR UPDATE;
Normaly this shall solve your problem.
Regards Gaetano Mendola
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBE1bm7UpzwH2SGd4RAlXrAKC8a7vuDnxspfWC42/8JObgSpTcfwCeIYI0 a0z0pj9ahiyJIYOz3t8wLUY= =syCe -----END PGP SIGNATURE-----
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
