3 ms·
If your idea in the example is that the second "debit" is created by another transaction while your transaction is in progress, then this will not work out. Fir
by Yokohiii 8y ago
If your idea in the example is that the second "debit" is created by another transaction while your transaction is in progress, then this will not work out. Firstly it requires a dirty read, which is nothing I would rely on in a transaction. Secondly, if the dirty read works, assuming the outcome of several rows is just a read operation, which forces you to rollback on the client and still leaves a window for inconsistencies if you decide to commit. Maybe SELECT .. FOR UPDATE can do a trick here, but that is like giving up life.
To round this up: RDBMS are bad for queue like read semantics. All you can do is polling. Which is even worse if you end up being lock heavy.
- hvidgaard 8y agoNo matter how you model things and no matter what technology you use, a race condition like this needs to be handled one way or another. You either handle it such that the data is always consistent, or you handle inconsistent data. You can use an SQL database with transaction and locking to ensure that you will never debit money that isn't there. Or you can save commands in a queue that only a single process at any given time (that incidentally includes the SQL scenario). Or you can use a distributed consensus algorithm with multiple stage commits. There is no way around it.