LOCK TABLE and FUNCTIONS
От | Amir Zicherman |
---|---|
Тема | LOCK TABLE and FUNCTIONS |
Дата | |
Msg-id | 27a5b7d104081522472da4fd66@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: LOCK TABLE and FUNCTIONS
|
Список | 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
В списке pgsql-novice по дате отправления: