Hermitage: Testing the “I” in ACID

Re:Hermitage: Testing the “I” in ACID by jeffdavis on Tuesday November 25, 2014 @11:09PM (#8661075)

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

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jrapdx3 on Wednesday November 26, 2014 @12:50AM (#8661235)

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

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jasonwatkinspdx on Wednesday November 26, 2014 @01:18AM (#8661302)

In 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 outlines the general issue. This paper discusses Postgres's solution: http://drkp.net/papers/ssi-vldb12.pdf

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jasonwatkinspdx on Wednesday November 26, 2014 @01:09AM (#8661283)

It's typically called Write Skew. Wikipedia discusses it on the snapshot isolation page (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/)

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jeffdavis on Wednesday November 26, 2014 @02:49AM (#8661460)

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't have enough information to say for sure.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jasonwatkinspdx on Wednesday November 26, 2014 @01:53PM (#8663963)

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.

Postgres pre version 9.2-ish would allow this situation even in "serializable" mode. Later versions wouldn't.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jeffdavis on Thursday November 27, 2014 @11:26AM (#8667856)

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

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jasonwatkinspdx on Sunday November 30, 2014 @01:33AM (#8676192)

Read the papers.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by wbkang on Wednesday November 26, 2014 @12:21AM (#8661189)

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.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jeffdavis on Wednesday November 26, 2014 @02:42AM (#8661444)

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

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jbergens on Wednesday November 26, 2014 @05:49PM (#8665027)

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.

[ Parent | on HN ]