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