3 ms·

Im not sure why the author excludes row level locking. SELECT ... FOR UPDATE Should work here. Lock the table when the balance is confirmed, then append t

Im not sure why the author excludes row level locking. SELECT ... FOR UPDATE Should work here. Lock the table when the balance is confirmed, then append to the ledger, then commit the transaction. All following transactions then need to wait for the FOR UPDATE lock to be released before they can read from the ledger.

daenney 5y ago
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 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 ago
    Interesting. I'm not a 100% sure how this works, but I suppose that could do it.
  - Nullabillity 5y ago
    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.
  - 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 ago
      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.

daenney 5y ago

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 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 ago
  Interesting. I'm not a 100% sure how this works, but I suppose that could do it.
- Nullabillity 5y ago
  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.
- 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 ago
    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.