Re: Is this a reasonable use for advisory locks?

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(dot)baldwin(at)gmail(dot)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 <perryn(at)fresho(dot)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
>>
>