3 ms·
I thought this was a really helpful post, as I wasn't previously familiar with the 'pg_advisory_lock', etc. functions. That said, I'm curious if the example gi
by hn_throwaway_99 5y ago
I thought this was a really helpful post, as I wasn't previously familiar with the 'pg_advisory_lock', etc. functions.
That said, I'm curious if the example given is just a simplified example, because to me this absolutely seems like a case where I wouldn't use a custom locking solution, but a case where I would use basic DB transaction guarantees to ensure that I didn't insert a row without the balance being sufficient, e.g.:
INSERT INTO ledger (tx_type, credit_holder_id, credit_delta)
SELECT 'use', 'foo', -10
FROM (SELECT sum(credit_delta) as balance
FROM ledger WHERE credit_holder_id = 'foo') foo_balance
WHERE foo_balance.balance > 10;
I'd need to double check the transaction isolation level that you'd need in PG, but generally PG is really good about efficiently supporting repeatable read isolation level.
- andreareina 5y agoAdvisory locks can be taken at the session (i.e. outside/across transactions) level, and need not be tied to a database object (if you need to operate on multiple objects, which one are you going to lock?). Simpler cases are supported by e.g. SELECT FOR UPDATE