LOCK TABLE and FUNCTIONS

From: Amir Zicherman <amir(dot)zicherman(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: LOCK TABLE and FUNCTIONS
Date: 2004-08-16 05:47:52
Message-ID: 27a5b7d104081522472da4fd66@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm having a problem with using LOCK TABLE within a function (stored
procedure) or outside a function. The lock is not working for me when
i run multiple inserts in parallel by calling a function.

I tried using the lock inside the function body and that didn't lock.
I'm not sure why because I thought a function body is considered to be
a transaction:

---------------------------------------------
CREATE OR REPLACE FUNCTION public.my_func()
RETURNS void AS
'
BEGIN

LOCK TABLE "mytable" IN ROW EXCLUSIVE MODE;
INSERT INTO "mytable" ("col1","col2") VALUES (1,3);

END;
'
LANGUAGE 'plpgsql' VOLATILE;
---------------------------------------------

I also tried looking outside of the function within a transaction
which didn't lock either:

---------------------------------------------
BEGIN TRANSACTION;
LOCK TABLE "mytable" IN ROW EXCLUSIVE MODE;
select * from my_func();
END TRANSACTION;
---------------------------------------------

when i do a regular insert statement without a function or a
transaction block, i get no deadlocks because the INSERT statement
automatically gets an ACCESS EXCLUSIVE LOCK from what i understand.
is that true? How do i get it to work with the stored procedure? I
need to get it working in a stored procedure because i want to do more
inside the function.

thanx for the help, amir

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message George Weaver 2004-08-16 12:27:47 Re: PGSQL 8-beta For WinXP Home Edition Instructions
Previous Message Steve McAllister 2004-08-15 22:29:41 ecpg - Poor fetch performance