Re: getting dead locks with 2 functions

From: Gaetano Mendola <mendola(at)bigfoot(dot)com>
To: Amir Zicherman <amir(dot)zicherman(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: getting dead locks with 2 functions
Date: 2004-08-06 10:01:12
Message-ID: 411356E8.9060708@bigfoot.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----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 <mendola(at)bigfoot(dot)com> 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-----

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David Garamond 2004-08-06 10:03:40 Re: case insensitive sorting & searching in oracle 10g
Previous Message Michael Glaesemann 2004-08-06 09:46:42 Re: Data version idea (please discuss)