Postgres Pro
Downloads
Home   >   mailing lists

Thread: getting dead locks with 2 functions

getting dead locks with 2 functions

From
Amir Zicherman
Date:
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

Re: getting dead locks with 2 functions

From
Gaetano Mendola
Date:
-----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-----

Re: getting dead locks with 2 functions

From
Amir Zicherman
Date:
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@bigfoot.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-----
>
>

Re: getting dead locks with 2 functions

From
Gaetano Mendola
Date:
-----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@bigfoot.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-----