Hi there,
We have identified a problem that we think advisory locks could help with, but we wanted to get some advice on whether its a good idea to use them this way (and any tips, best practices or gotchas we should know about)
THE PROBLEM
We have some code that does the following
- For a customer:
- sum a ledger of transactions
- if the result shows that money is owed:
- charge a credit card (via a call to an external api)
- if the charge is successful, insert a transaction into the ledger
We would like to serialise execution of this code on a per customer basis, so that
we do not double charge their credit card if execution happens concurrently.
We are considering taking an advisory lock using the customer id to accomplish this.
OUR CONCERNS
- The fact that the key for an advisory lock is an integer makes us wonder if this is designed for taking locks per process type, rather than per record (like a customer)
- Is it a bad idea to hold an advisory lock while an external api call happens? Should the locks be shorter lived?
- The documentation notes that these locks live in a memory pool and that 'care should be taken not to exhaust this memory'. What are the implications if it is exhausted? (Eg will the situation recover once locks are released?). Are there established patterns for detecting and preventing this situation?
- anything else we should know?
Thanks in advance for any advice!
Cheers
Perryn