2 ms·

Payment/accounting systems are the typical example of where transactions are critical. The sending and receiving accounts need to be updated in the same transa

Payment/accounting systems are the typical example of where transactions are critical. The sending and receiving accounts need to be updated in the same transaction that correctly fails if either account would become negative as a result of the operation.

blagie 3y ago
I disagree. Transactions are the wrong way to handle this. The right way to do this is to send a query which says: "If sending account has more than $10: Decrease sending account by $10. Increase receiving account by $10. Return what happened." The database should be able to do the above (or any other query) atomically. Under normal circumstances (e.g. barring a network failure or similar), this should never fail. The reason a transaction is necessary is because the above would typically be handled by 2-3 separate queries: - Start transaction - Check sending balance - [Client-side code] - Increase one account - [Client-side code] - Decrease the other - Finish transaction Having this back-and-forth can cause this to fail, and network latency means that it often DOES fail. A database could implement this internally with transactions and retries (in which case, the application programmer doesn't need to think about it and therefore have bugs there), but that's probably the wrong way to do it. There are many more reasonable ways, such as locks in rows + ordering operations, rearranging data, or otherwise. From the perspective of the programmer, this SHOULD be atomic. There is no reason why everyone writing a financial system needs to be competent at managing these sorts of parallel consistency issues. The current model introduces potential for a lot of consistency bugs, performance issues, and other problems. Transactions also introduce a lot of unnecessary complexity to the store itself.

2 ms·

Payment/accounting systems are the typical example of where transactions are critical. The sending and receiving accounts need to be updated in the same transa

by benlivengood 3y ago

blagie 3y ago
I disagree. Transactions are the wrong way to handle this. The right way to do this is to send a query which says: "If sending account has more than $10: Decrease sending account by $10. Increase receiving account by $10. Return what happened." The database should be able to do the above (or any other query) atomically. Under normal circumstances (e.g. barring a network failure or similar), this should never fail. The reason a transaction is necessary is because the above would typically be handled by 2-3 separate queries: - Start transaction - Check sending balance - [Client-side code] - Increase one account - [Client-side code] - Decrease the other - Finish transaction Having this back-and-forth can cause this to fail, and network latency means that it often DOES fail. A database could implement this internally with transactions and retries (in which case, the application programmer doesn't need to think about it and therefore have bugs there), but that's probably the wrong way to do it. There are many more reasonable ways, such as locks in rows + ordering operations, rearranging data, or otherwise. From the perspective of the programmer, this SHOULD be atomic. There is no reason why everyone writing a financial system needs to be competent at managing these sorts of parallel consistency issues. The current model introduces potential for a lot of consistency bugs, performance issues, and other problems. Transactions also introduce a lot of unnecessary complexity to the store itself.