[daenney]

I think you’re confusing row and table in your comment. Rows are never changed, so I’m not sure how they would use a row level lock.

They have 1000s of concurrent transactions on that table for different credit holders. Locking the whole table every time means everyone is stalled, even though you only want transactions for a single credit holder to be serialised, not every single mutation of that table.

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