4 ms·
I think we can lock the row in the accounts table instead, e.g. BEGIN; SELECT * FROM accounts WHERE id = $account_id FOR UPDATE; -- select/insert/u
by throwdbaaway 5y ago
I think we can lock the row in the accounts table instead, e.g.
BEGIN;
SELECT * FROM accounts WHERE id = $account_id FOR UPDATE;
-- select/insert/update/delete transactions
COMMIT;
This essentially serializes all reads/writes to data that belongs to the account. Not sure if this performs better or worse than using an advisory lock.
- reese_john 5y agoYes, though I'm not convinced that the whole process should be synchronous, unless it is a "hard" use case, such as an ATM withdrawal. For instance, you may want to do some sanity checks, to see if the account is in a valid status, i.e "ACTIVE" and not "BLOCKED". You'll also probably want to create a corresponding credit entry to match the newly created debit. I think you are better off optimistically creating a new transaction with a "STARTING" status and then settling it asynchronous (but quickly), rolling it back to an invalid state if something goes wrong, or to "COMPLETED" if everything clears.