Hermitage: Testing the “I” in ACID

Re:Hermitage: Testing the “I” in ACID by jeffdavis on Wednesday November 26, 2014 @03:17AM (#8661514)

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

I hesitate because I don't really understand the details of the situation the exchange faced. But, going by the linked references here:

https://bitcointalk.org/index.php?topic=499580 http://www.reddit.com/r/Bitcoin/comments/1wtbiu/how_i_stole_...

it appears that the pattern in question, if translated very unnaturally to SQL, is something like:

    CREATE TABLE account(id int8, balance numeric);
    ...
    BEGIN;
    SELECT balance FROM account WHERE id = 123;
    -- application sees 100, subtracts 90, sees that
    -- it's still positive and does:
    UPDATE account SET balance = 10 WHERE id = 123;
    COMMIT;

Technically speaking, running that in postgres in the default configuration (read committed) is prone to a race, and you'd need to use repeatable read or serializable mode to protect you.

But that's ridiculous. Anyone using SQL would instead do:

    CREATE TABLE account(id int8, balance numeric, check(balance >= 0));
    ...
    UPDATE account SET balance = balance - 90 WHERE id = 123;

And that is not prone to a race. Try it in any version of postgres, in any configuration. You can't get double withdrawls (where only one takes effect), and you can't get it to go below zero.

So, the author is technically right: (a) if you translate the NoSQL-isms into SQL in an unnatural way; and (b) don't bother to use SERIALIZABLE mode, which costs very little in most situations.

I agree with the author that isolation is tricky, and developers should not be expected to understand the nuances. And I applaud the development of a testing framework to really understand the various kinds of isolation and how they apply to different products. But the example is a bad one, because it actually does work just fine in postgres, and probably many other systems.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by martinkl on Wednesday November 26, 2014 @05:32AM (#8661783)

Your first ("very unnatural") example is what I had in mind. And since read committed is the default isolation level in most RDBMS, it is prone to the lost update anomaly. (In MySQL, repeatable read is the default, but its implementation of repeatable read doesn't prevent lost updates.) Note I did point out that I'm referring to the default configuration, not the strongest supported isolation level.

Although your second example is probably what a human would write, an ORM framework would very likely generate a transaction looking like your first example.

Another example would be inserting a transaction into a table, and summing the transactions in the account in order to calculate the account balance. Making that safe requires preventing phantom reads, which means requiring serializability.

You say serializable costs very little in most situations. I can't claim to know what most situations are like, but all I know is that I've seen many people who have tried serializable and found it too slow for them. User a-priori gives an example elsewhere on this thread: http://www.michaelmelanson.net/2014/03/20/transactions/

My point is that weak isolation is very subtle, easy to get wrong, and you don't know that you got it wrong until it's too late. We need better understanding and better tools so that concurrency is less easy to screw up.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jeffdavis on Wednesday November 26, 2014 @01:44PM (#8663926)

Your example of poor SERIALIZABLE performance is from a MySQL user. Have you tried SERIALIZABLE in PostgreSQL? The implementation is based on fairly recent research, and performs quite well for a lot more use cases.

And the point about ORMs is valid, but still not enough to back up your unqualified claim that using postgres would not have solve the problem. It might not have, but postgres offers a lot of tools to solve this problem (we didn't even discuss SELECT ... FOR UPDATE), and even inexperienced users are at least more likely to have stumbled into one of those solutions.

I very much agree that isolation issues are subtle traps for many users, even in SQL. I would like to see SERIALIZABLE become more common, and eventually the default, in postgres. I also like the fact that you're writing real tools to check up on these in a formal way.

But please be a little careful when making statements like that, because it can turn people away from the systems most likely to help them.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by bmm6o on Wednesday November 26, 2014 @02:57PM (#8664294)

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.

[ Parent | on HN ]

Re:Hermitage: Testing the “I” in ACID by jeffdavis on Wednesday November 26, 2014 @09:16PM (#8665686)

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.

[ Parent | on HN ]