Thread: LOCK TABLE and FUNCTIONS

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

Amir Zicherman <amir.zicherman@gmail.com> writes:
> I tried using the lock inside the function body and that didn't lock.
> ...
> LOCK TABLE "mytable" IN ROW EXCLUSIVE MODE;

Sure it did.  But ROW EXCLUSIVE is a very weak lock (the same as what
INSERT would take for itself) and it's not going to lock out other
INSERTs.

If you want to be the only writer of the table then you need EXCLUSIVE
lock, or possibly ACCESS EXCLUSIVE if you'd like to block out readers
too.  See
http://www.postgresql.org/docs/7.4/static/explicit-locking.html#LOCKING-TABLES

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

Don't know where you got that idea.  If it did that, we'd have no
concurrency at all.

            regards, tom lane