6 ms·
Isn't it true that using an UPDATE statement referencing the existing column's value also works? Pseudo-code: UPDATE account WHERE ... SET balance = balan
by mangeletti 11y ago
Isn't it true that using an UPDATE statement referencing the existing column's value also works?
Pseudo-code:
UPDATE account WHERE ... SET balance = balance - 5
If both sides of the transfer are handled this way, and then the balance of the transferrer is checked after to ensure it's greater than 0 (rollback otherwise), won't that suffice to handle the issue without having to use SELECT ... FOR UPDATE?
---
To further simplify this, you could include a WHERE balance > [transfer amount] clause to the transferrer UPDATE query. If the number of rows updated is 1, UPDATE the transferee's row. If the number of rows updated is 0, you're done (tell the user they don't have sufficient funds). Isn't that right?
- barrkel 11y agoThe issue (as I understand it) was transferring balance from one card to another. So two rows need updating, one with an increment, the other with a decrement.
- jlgaddis 11y agoI'm not a developer (I generally only write shell/Perl/Python scripts to make my own job easier) or a database expert but wouldn't this issue be pretty easy to avoid if the whole process were wrapped up in a transaction? E.g.: BEGIN TRANSACTION; SELECT balance AS balance1 FROM giftcards WHERE gift_card_id = 1; SELECT balance AS balance2 FROM giftcards WHERE gift_card_id = 2; UPDATE giftcards SET balance = balance1 - 5 WHERE gift_card_id = 1; UPDATE giftcards SET balance = balance2 + 5 WHERE gift_card_id = 2; END TRANSACTION; Obviously, this is somewhat simplified and you'd have various checks to make sure balance1 was actually >= 5, etc. Again, I'm not a developer, so what am I missing?
- tlarkworthy 11y agoNot all databases have transactions (eg mongo). Not all developers understand when to use them.
- jlgaddis 11y agoIs it not a safe assumption that one would use an ACID-compliant RDBMS for something involving money?
- derefr 11y agoYou might throw up a little bit if you ever saw a piece of Bitcoin exchange software.
- __z 11y agoI guess Mtgox originally sent passwords in the query string... http://i.imgur.com/xMeW43a.jpg http://i.imgur.com/xMeW43a.jpg https://bitcointalk.org/?topic=444.0 https://bitcointalk.org/?topic=444.0
- fleitz 11y agoYou know what they say about assumptions, of course not, they use message queues for transactions and have a great queuing system built on acid databases
- ryanjshaw 11y agoTransactions do not necessarily get processed serially. For example, if there were two concurrent requests and request #1 had just completed the first UPDATE when request #2 jumps in and performs the first two SELECTs, then request #2 will incorrectly update the balance for card #2. Different database systems offer different ways to serialize transactions, usually with a cost of performance and complexity. Note that the post says: > The only right way to do it is a pessimistic lock (FOR UPDATE clause). This is not true. Banks deal with this problem all the time. You don't have to use a database engine as the serializer, despite what all the books tell you. My preference would be to explicitly serialize transactions rather than rely on database tricks - i.e. write accounting entries to ledgers and have a service that processes those entries on a single thread. For many scenarios this is more than good enough. If you needed lower latency, you could process this all in memory and use the database purely to replay the transaction log on restart for unprocessed transactions. In either implementation you could implement optimizations to process entries on different threads.
- mangeletti 11y agoIf you make 2 updates, check the balance to ensure > 0, and rollback if < 0, and you do all of this in a transaction, doesn't concurrency no longer matter? If another transaction beats you to the punch, won't the balance check query reflect that?
- TheLoneWolfling 11y agoNope - this can happen with cards with a balance above zero too. Look at what happens if you start two transfers of 1/2 the money from A to B.
- jellicle 11y agoYou're not missing anything. This is what transactions are for. If you need to make multiple changes to a database and you need all of them or none of them to happen, you need a transaction. Don't know what they're teaching the kids nowadays that they would let money be moved around without using transactions.
- barrkel 11y agoThe semantics of the SQL you wrote depends on the transaction isolation level and flavour of transaction implementation. Read committed vs repeatable read (common terms for specific semantics) makes a difference as to what selects can see with respect to concurrent transactions. The way the SQL is written can also affect how reads and writes interleave. Repeatable read isn't super-expensive in modern databases with MVCC support - these should prevent the situation in the article if the SQL is as simple as you write. There's a strong hint that it isn't, though; there appears to be a two-phase state machine involved, with two requests. No sane developer will write a transaction that starts in one request and commits in another.
- mangeletti 11y agoExactly; each of these operations require only 1 query (you don't need to SELECT and then UPDATE), and then a third SELECT query afterwards to ensure the transferrer's balance wasn't reduced to below 0, in which case the save point is rolled back.