Is this a reasonable use for advisory locks?

From: Perryn Fowler <perryn(at)fresho(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Is this a reasonable use for advisory locks?
Date: 2022-04-14 07:10:55
Message-ID: CAK6N2dj3ind5LiLQv2P98vPCYZE6obt4+JuR0962hFofcac-_w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Baldwin 2022-04-14 08:38:34 Re: Is this a reasonable use for advisory locks?
Previous Message Zheng Li 2022-04-13 17:22:46 Re: Support logical replication of DDLs