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
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 |