5 ms·
I thought the same thing as the parent post, assuming you have a column "account_id", can't you just do: select * from transactions where account_
by reese_john 5y ago
I thought the same thing as the parent post, assuming you have a column "account_id", can't you just do:
select *
from transactions
where
account_id=$user_account_id
for update;
Wouldn't all rows for the particular user be locked ? Then you can check the balance, append, commit and release the lock
- daenney 5y agoInteresting. I'm not a 100% sure how this works, but I suppose that could do it.
- Nullabillity 5y agoTechnically that wouldn't prevent new INSERTs from happening between the SELECT and the COMMIT (since the locks only get applied to existing rows, they don't prevent new rows that match the condition), although the snippet should at least prevent racing with itself.
- throwdbaaway 5y agoI 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.