Here is my situation:

  • I have 2 types of actions A & B, each read or update a single ressource.
  • Actions A should be able to be executed concurrently.
  • Actions A should wait for Actions B to be finished.
  • Actions B should wait for Actions A to be finished.
  • Actions B should wait for Actions B to be finished.

This is solved perfectly using FOR SHARE for Actions A, and FOR UPDATE on Actions B

My problem is the following:

  • using this model, Actions B can be on hold indefinetely as long as new Actions A are coming through.
  • I want that once an Action B is trying to acquire its LOCK, new Action A should be on hold.

Is there any way to achieve that?

  • It it necessary that the resource be represented by a row in a table? AccessShare and AccessExclusive locks on tables already behave the way you want, so you could represent the resource as a dummy table. – jjanes Oct 31 '18 at 14:47
  • yes, it has to be a row. But wouldn't AccessExclusive have the same problem ? The AccessExclusive will never be granted if AccessShare queries are constantly being executed. – Simon-Okp Oct 31 '18 at 15:40
  • A pending AccessExclusive blocks incoming AccessShare, and waits for the existing ones to drain away. Which is very annoying in some cases, and a good thing in others. Row-level share/update locks don't work that way, presumably due to technical implementation details. Perhaps you can get what you want with the "pg_advisory_xact_lock*" functions, which behave the same way as table locks do. – jjanes Oct 31 '18 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.