4 ms·
"...able withdraw more money than they had in their account...Most so-called ACID databases — for example Postgres, MySQL, Oracle or MS SQL Server — would not h
by jeffdavis 12y ago
"...able withdraw more money than they had in their account...Most so-called ACID databases — for example Postgres, MySQL, Oracle or MS SQL Server — would not have prevented this race condition in their default configuration."
The author didn't really show that this was true. He obviously understands isolation well, so I would tend to believe him, but it would be nice to see an example.
In a simple case (not sure that it matches the exchange's case), postgresql in any configuration will prevent this problem:
CREATE TABLE account(
id int8,
balance numeric, check (balance >= 0)
);
No matter what concurrent activity you have going on, it's impossible (as far as I know) to end up seeing a balance less than 0. That's actually true in any isolation mode that postgres supports (read committed, snapshot isolation, and truly serializable).
Does someone have a counterexample, or more details about the case at the exchange that would not be solved by postgres?
I'd also like to point out that the postgres's implementation of true serializability performs quite well and there isn't much of a cost to using it over snapshot isolation.
- wbkang 12y agoI think this could happen if you do not use 'compare-and-swap' and simply update account set balance=balance-9 twice at the same time when the account balance is 10. The account balance won't go below zero but you ended up withdrawing twice.
- jeffdavis 12y agoBut that will not happen in postgres on any version in any configuration with any isolation level. It just can't happen.
- jbergens 12y agoMaybe he is talking about an application that reads the old value first and then sends the sql code. That could happen from two users at the same time and there would be a double redraw unless you do some kind optimistic concurrency control which I think a lot of developers forget.
- jrapdx3 12y agoThe key phrase is the line quoted is "default configuration". According to PostgreSQL docs, the default isolation level is "READ COMMITTED". Your example check constraint indeed appears to work as advertised. Trying UPDATE account SET balance = -1.00 where id = 1; gives an ERROR message re: new row for relation "account" violates check constraint ... Still it's not clear exactly what the author sees re: where postgres fails. I'll have to read the article again. With any luck someone more knowledgeable than I am will shed light on the question.
- jasonwatkinspdx 12y agoIn short: prior to version 9.2, postgres's "serializable" level still allowed some transaction patterns that would be rejected by a strictly serial execution. Later versions prevent it. This paper: http://dl.acm.org/citation.cfm?id=1376690 http://dl.acm.org/citation.cfm?id=1376690 outlines the general issue. This paper discusses Postgres's solution: http://drkp.net/papers/ssi-vldb12.pdf http://drkp.net/papers/ssi-vldb12.pdf
- jasonwatkinspdx 12y agoIt's typically called Write Skew. Wikipedia discusses it on the snapshot isolation page (http://en.wikipedia.org/wiki/Snapshot_isolation http://en.wikipedia.org/wiki/Snapshot_isolation). Also, Peter Bailis's blog has info and links to papers if you want more detail (http://www.bailis.org/blog/when-is-acid-acid-rarely/ http://www.bailis.org/blog/when-is-acid-acid-rarely/)
- jeffdavis 12y agoI know, in general, about various kinds of serialization anomalies, and I know (in moderate detail) how all of the isolation modes are implemented in postgres. What I don't know is about the particular problem the exchange encountered, and why postgres's isolation would not have prevented it. Based on the very brief descriptions of the problem that I've seen, it seems like postgres would have prevented it, but I don't have enough information to say for sure.
- jasonwatkinspdx 12y agoI don't know first hand, but could take a guess: Think of a transaction that inserts a row representing a withdrawal, updates a materialized total balance, and checks that it's positive. Under snapshot isolation, two concurrent instances of this transaction could commit. The materialized balance would reflect only one of the debits however, and would be inconsistent vs queries that recompute the aggregate in full. Postgres pre version 9.2-ish would allow this situation even in "serializable" mode. Later versions wouldn't.
- jeffdavis 12y agoDid you actually try in postgres? If so, please provide an example.
- jasonwatkinspdx 12y agoRead the papers.