Hi Steve, Thanks for your thoughts!
I was thinking to avoid using locks on the customer rows because there is a lot of other unrelated access to that table. In particular I don’t want writes to that table queueing up behind this process. However, does the fact that you are suggesting row locks mean you think advisory locks are a unsuitable? (Thanks for the mention of network issues, but I am confident that we have appropriate mechanisms in place to ensure fault tolerant and idempotent processing - I’m specifically wanting to address the race condition) Cheers Perryn On Thu, 14 Apr 2022 at 6:38 pm, Steve Baldwin <steve.bald...@gmail.com> wrote: > Hi Perryn, > > I don't know why you think advisory locks are the solution. It seems > regular row locks would ensure you have exclusive access to the customer. > > Maybe something like this: > > begin; > select * from customer where id = $1 for update skip locked; > if the query returns no rows it means something else already has a lock on > the customer so rollback and exit > otherwise call the external api (assume synchronous) > if successful insert a row into the ledger table and commit else rollback > > There are some tricky aspects to this but nothing that can be helped by > advisory locks over row locks. For example, if the external call takes too > long and you time out, or your network connection drops, how do you know > whether or not it was successful? You also need to work out what happens if > the insert into the ledger fails. If you haven't already, maybe check out > the 'saga' pattern. > > Cheers, > > Steve > > On Thu, Apr 14, 2022 at 5:11 PM Perryn Fowler <per...@fresho.com> wrote: > >> 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 >> >
