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
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 |