[reese_john]

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

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

[Nullabillity]

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

[daenney]

Interesting. I'm not a 100% sure how this works, but I suppose that could do it.