If you need concurrency design your system for concurrency.

Have a transactions table with the payer and receiver and calculate the current balance using the transactions.

Each transaction must have a unique Id (pk)

traderj0e 2 days ago | parent | next [-]

That is actually worse, I've been there. It's good to keep logs like that, but you can't use that for locking, you need a separate balances table.

Edit: Well another option is to add a "pending" col and do three separate db xacts: 1. insert pending=true row 2. select balance with pending debits deducted (which ages out pending rows older than 1min) 3. update row to pending=false if successful. This is a useful pattern if you're waiting on an external system too, but not good in this case where you're just trying to update in one DB.

▲

cozzyd 2 days ago | parent [-]

your goal is to find if there is any combination of plausible transaction orders that results in a balance less than 0, so you can issue an overdraft fee.

	▲	traderj0e 2 days ago \| parent [-]
		The original stated goal is that we want to disallow overdraft. If you want to allow it instead, then there are some followup questions like do you want to limit how much they could overdraft. But this is meant to be an example of race conditions, not a real world bank.

▲

grebc 2 days ago | parent | prev [-]

This is the answer for any serious banking/accounting software.

Balance is calculated & stored after the fact from a known correct value.