Geek Answers Handbook

PostgreSQL gets and releases a LOCK inside a stored function

I have a function that should perform a long update on several large tables. During the update, every 2-3 tables must be locked in EXCLUSIVE mode.

Since not all tables should be locked at the same time, ideally, I would like to LOCK only those tables that I update at that time, and then remove the lock as soon as we finish.

Eg.

-- Lock first pair of tables LOCK TABLE tbl1_a IN EXCLUSIVE MODE; LOCK TABLE tbl1_b IN EXCLUSIVE MODE; -- Perform the update on tbl1_a and tbl1_b -- Release the locks on tbl1_a and tbl1_b -- HOW??? -- Proceed to the next pair of tables LOCK TABLE tbl2_a IN EXCLUSIVE MODE; LOCK TABLE tbl2_b IN EXCLUSIVE MODE;

Unfortunately, there is no equivalent to the UNLOCK statement in plpgsql. The usual way to delete a LOCK is a COMMIT transaction, but this is not possible in a function.

Is there any solution for this? Is there some way to explicitly release the lock before executing the function? Or run some kind of sub-transaction (perhaps by running each update in a separate function)?

UPDATE

I agreed that there is no solution. I will write each update in a separate function and coordinate from outside db. Thanks to everyone.

+8
sql plpgsql postgresql
source share
3 answers

There is no way . Postgres functions are atomic (always inside a transaction), and locks are released at the end of the transaction. And no offline transactions (yet).

You might be able to get around this with advisory locks . But this is not the same thing. All competing transactions must play together. Parallel access that does not know about advisory locks will ruin the party.

Sample code on dba.SE:

Or you can somewhere with a "trick" of autonomous transactions with dblink:

  • How to make big non-blocking updates in PostgreSQL?
  • Does Postgres support nested or offline transactions?

Or you overestimate your problem and divide it into several separate transactions.

+6
source share

Impossible. From the documentation: after the acquisition, the lock usually remains until the end of the transaction. But if a lock is obtained after setting the save point, the lock will be immediately disabled if the backup is returned. This is consistent with the principle that ROLLBACK cancels all command effects from the moment it is saved. The same applies to the locks received in the PL / pgSQL exception block: the error exits the block locks received in it.

http://www.postgresql.org/docs/9.3/static/explicit-locking.html

+1
source share

In pg11 you now have PROCEDURE which allows you to release locks through COMMIT . I just transformed a bunch of parallel functions executing ALTER TABLE... ADD FOREIGN KEY... with a lot of deadlock issues, and it worked fine.

https://www.postgresql.org/docs/current/sql-createprocedure.html

0
source share

More articles:


All Articles