Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Hermitage: Testing the “I” in ACID

martin.kleppmann.com

Post reply on HN
Print view View original

11–20 of 34 posts

Re: Hermitage: Testing the “I” in ACID

#11

Post by Animats »

martinkl wrote: post #10
Animats wrote: post #9

Earlier quoted context omitted.

You're right. MS SQL Server does reject that on repeatable read, but MySQL does not. Should MySQL have the same behavior? It detects the problem and blocks, but then does the update when the other transaction finishes, losing one update. I tried this for different values of "value" in each process, and it still fails. (The test sets the same value from each process, so you can't see who wins the race or if the databa…

I don't know what behavior it "should" have. IMHO there's scope for different databases to implement things differently — otherwise there would be no room for innovation. The important thing is just that we understand precisely which guarantees we're getting and which we're not, so that we can write applications which behave correctly under a given isolation level. And that's the whole point of Hermitage.

That's not "implementing differently" or "innovation". That's very differing semantics for the same named mode of operation.

Since you have a test suite, try getting that to the SQL committee for the next revision of the standard.

Re: Hermitage: Testing the “I” in ACID

#13

Post by jrullmann »

martinkl wrote: post #4
jrullmann wrote: post #3

Very cool - would love to see tests of FoundationDB! FYI I'm an engineer at FDB, happy to help.

Awesome, I'd love a pull request! I've been looking at FoundationDB, but haven't had time to test it. Porting the tests to another database is (hopefully) a mostly mechanical exercise.

Just submitted a pull request for FoundationDB: https://github.com/ept/hermitage/pull/1

Re: Hermitage: Testing the “I” in ACID

#14

Post by martinkl »

a-priori wrote: post #12

In case it's interesting to people, here's a blog post I made earlier this year on the topic of what exactly transaction isolation means: http://www.michaelmelanson.net/2014/03/20/transactions/

Nice example of a bug caused by weak isolation. FWIW, Postgres has an interesting implementation of "serializable" which takes far fewer locks than MySQL, so may give you better performance while retaining the same isolation level.

Re: Hermitage: Testing the “I” in ACID

#15

Post by fdr »

martinkl wrote: post #14
a-priori wrote: post #12

In case it's interesting to people, here's a blog post I made earlier this year on the topic of what exactly transaction isolation means: http://www.michaelmelanson.net/2014/03/20/transactions/

Nice example of a bug caused by weak isolation. FWIW, Postgres has an interesting implementation of "serializable" which takes far fewer locks than MySQL, so may give you better performance while retaining the same isolation level.

Yes: it is the relatively new approach by Cahill et al in 2008, known as SSI: https://courses.cs.washington.edu/courses/cse444/08au/544M/R...

Which, as far as production-common database implementations go, is lightspeed for implementing new academic work (9.1, the first version with the feature, was released in 2011).

Re: Hermitage: Testing the “I” in ACID

#16

Post by jeffdavis »

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

Re: Hermitage: Testing the “I” in ACID

#17

Post by wbkang »

jeffdavis wrote: post #16

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

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.

Re: Hermitage: Testing the “I” in ACID

#18

Post by jrapdx3 »

jeffdavis wrote: post #16

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

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.

Re: Hermitage: Testing the “I” in ACID

#19

Post by jasonwatkinspdx »

jeffdavis wrote: post #16

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

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/)

Re: Hermitage: Testing the “I” in ACID

#20

Post by jasonwatkinspdx »

jrapdx3 wrote: post #18
jeffdavis wrote: post #16

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

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 postgre…

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

Post reply on HN