Another solution is to not use update statements at all. Obviously this involves some changes to the tables. And it defers the balance calculation to query time.<p>Analogous to the example from the article:<p><pre><code> create table transaction (user_id integer, amount integer);
insert into transaction values (1, -100);
select sum(amount) as balance from transaction;</code></pre>
I got caught by this a couple of years ago, but only because I was using a JSON field and as far as I knew at the time, to modify a JSON value I had to take it out, modify it and write it back. The mistake was ever choosing to use a JSON field for data that was changing rapidly, and being updated from multiple sources. When I detected the issue, I added a FOR UPDATE to my SELECT.<p>It made me realize though the incredible safety I'd come to take for granted with a more traditional relational approach. Prior to that I would have had a separate table for the data in that JSON field, with each object within it, represented by its own row. Given my scenario that would have been perfectly safe, because the latest update to a specific object was always the right one.<p>The original design choice was perceived as "easier".
How are "serializable" transactions in PostgreSQL different from optimistic concurrency control? The docs say:<p>> In fact, this isolation level works exactly the same as Repeatable Read except that it monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions. This monitoring does not introduce any blocking beyond that present in repeatable read, but there is some overhead to the monitoring, and detection of the conditions which could cause a serialization anomaly will trigger a serialization failure.<p>As far as I can tell, this doesn't make transactions any more serializable; it just fails them if they wouldn't have been serializable anyway. And then clients typically retry. That sounds just like OCC. Like OCC, I'd expect that under any kind of contention, this could lead to very large numbers of failures and retries. It's not quite livelock, since at least one client will always make forward progress, but close to it.
Nitpick: while the gist is correct, the 2nd diagram illustrating how transactions don't help is subtly misleading. The diagram implies that time flows down in the diagram. If so, then the update on the right won't occur until after the commit on the left. The update on the left really does acquire "write" lock on the balance, so the 2nd update on the right cannot occur until after the lock on the left is released when the left commits.<p>That said, the effect is exactly as the author claims. The transaction on the right will ignore the change made by the one on the left.
I'm glad to see optimistic concurrency control mentioned. In my experience even seasoned developers are unaware of this pattern, which should be really the default in many database-handling code.<p>I'm not aware of ORMs handling it either (except for my in-house code generator). Is there anything out there that handles it well?
Hmm.<p>Am I the only one who would consider a CTE for this, selecting the initial balance and the proposed balance minus amount in the CTE, testing it in the query following the CTE and potentially applying it, and then returning the original and new balance to the application. If both are the same then the update was not applied (not enough balance), and if different the update was applied (enough balance for the withdrawal).<p>This way it all occurs within a single transaction, even though it is several logical steps, and is simple to reason about.
Great article. This is something I've been thinking about for a while working on Ruma, my Matrix homeserver implementation. I think this is a type of bug that a lot of application developers miss, but the consequences can be quite catastrophic depending on the nature of the data.<p>As a small aside, if anyone is interested in Matrix, Rust, Diesel (the Rust ORM), or the intersection of any of these things, you might be interested in discussing this further on this Ruma issue: <a href="https://github.com/ruma/ruma/issues/132" rel="nofollow">https://github.com/ruma/ruma/issues/132</a>
I'm guilty here too.<p>But one side of me wonders why SQL databases do not better adapt to those 'anti-patterns' in software development, when developers for a variety of reasons are more confident in having logic in their app code than their DB code.
A really good resource is the following book
<a href="http://www.apress.com/us/book/9781484207611" rel="nofollow">http://www.apress.com/us/book/9781484207611</a><p>Oracle Database Transactions and Locking Revealed
Authors: Kyte, Thomas, Kuhn, Darl<p>It's Oracle-specific though, because of one of the authors(Tom Kyte , the Tom behind asktom.oracle.com not so long ago ) I highly recommend it.
In addition to this, postgres also has Advisory Locks, if you want to handle the locking mechanism client side and don't want to fail update by a client that still wants to do the update anyway for some reason.
Essentially, how to make a bank account in SQL. The author then explains how transactions won't help solve the problem, followed by an example with transactions that actually does solve the case.<p>At university we learn that transactions should be serializable and that they are meant for these exact cases.<p>Transactions aren't actually true transactions unless they are serializable.<p>Most databases don't actually default to serializable transactions, but instead try to get close by implementing snapshot isolation. These aren't true transactions, since snapshot isolation doesn't truly isolate transactions.<p>The article had a bunch of nice ways to get better isolation than snapshot for a bunch of special cases. These are all very nice, since they can be useful for mainting performance together with integrity.<p>My professors at university still laugh at commercial databases and calls them toys for still not having implemented more efficient serializable transactions.
Please add [2014].<p>I should note that this is not Postres specific - any MVCC ACID database will behave this way. Some use locks instead of MVCC and behave differently. But it definitely belongs into "What every programmer should know about transactions, ollected works".