CODEWITHSUNDEEP
postgresqlexceptionlocking
Mukund Gandlur
Mukund Gandlur

Reputation: 869

How does a postgres lock get released on exception

I am writing a function which locks onto a table and performs update operation. And here I am using an extension to hash a string and here there is a chance of an error.

Now my question is. How do I make sure the lock is released in case of an exception?

I am using the lock in "IN ACCESS EXCLUSIVE "mode.

Upvotes: 1

Views: 1175

Answers (1)

user330315
user330315

Reputation:

A lock typically is released at the end of the transaction. As a function can't do transaction handling you can't really release the lock in the function. The code that calls the function must properly commit or rollback the transaction in order to release the lock.

Another option is to use a savepoint before acquiring the lock. In that case you can rollback to the savepoint in the exception handler to release the lock.

begin
  savepoint before_lock;
  lock table foo in exclusive mode;
  ... do stuff
exception 
   when others then 
     rollback to before_lock;
end;

Note that this will not release the lock in case everything is OK (nor will the current transaction be ended). You still need to end the transaction in the code that calls the function

Upvotes: 1

Related Questions