Hermitage: Testing the “I” in ACID • Hacker News Board

Re: Hermitage: Testing the “I” in ACID

#31

Post by jbergens » Wed, Nov 26, 2014, 10:49 PM UTC

jeffdavis wrote: post #21

wbkang wrote: post #17

Earlier quoted context omitted.

I 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.

But that will not happen in postgres on any version in any configuration with any isolation level. It just can't happen.

Maybe 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.

Re: Hermitage: Testing the “I” in ACID

#32

Post by jeffdavis » Thu, Nov 27, 2014, 2:16 AM UTC

bmm6o wrote: post #30

jeffdavis wrote: post #23

I strongly rebut the following claim, which is central to the article: "Internet commenters, in their infinite wisdom, were quick to point out that if you’re dealing with money, you had better use an ACID database. But there was a major flaw in their argument. Most so-called ACID databases — for example Postgres, MySQL, Oracle or MS SQL Server — would not have prevented this race condition in their default configurat…

I strongly rebut the following claim, which is central to the article I would disagree that offering an alternate solution that avoids the problem is a rebuttal of the fact that there are quirks in isolation level implementations across RDBMSs that can surprise even seasoned developers.

The author specifically claimed that using a safer system like postgres would not have prevented the problem. But the evidence only says that it may not have prevented the problem.

It still looks to me like they would have stood a better chance with poatgres.

Re: Hermitage: Testing the “I” in ACID

#33

Post by jeffdavis » Thu, Nov 27, 2014, 4:26 PM UTC

jasonwatkinspdx wrote: post #29

jeffdavis wrote: post #22

Earlier quoted context omitted.

I 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'…

I 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. Po…

Did you actually try in postgres? If so, please provide an example.

Re: Hermitage: Testing the “I” in ACID

#34

Post by jasonwatkinspdx » Sun, Nov 30, 2014, 6:33 AM UTC

jeffdavis wrote: post #33

jasonwatkinspdx wrote: post #29

Earlier quoted context omitted.

I 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. Po…

Did you actually try in postgres? If so, please provide an example.

Read the papers.