[throwdbaaway]

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]

Yes, 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.