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

1–10 of 34 posts

Re: Hermitage: Testing the “I” in ACID

#4

Post by martinkl »

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.

Re: Hermitage: Testing the “I” in ACID

#5

Post by Animats »

A key concept here is that real databases have transactions, there's some concurrency between transactions, and that in the more efficient modes, transactions can deadlock, fail, and be rolled back.

In MySQL, a statement within a transaction can return "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction". This can be forced by the following sequence of events. (This assumes an InnoDB table in Repeatable Read mode.)

    Process A does a START TRANSACTION.
    Process B does a START TRANSACTION.
    Process A does a SELECT which reads row X.
    Process B does a SELECT which reads row X.  
    Process A does an UPDATE which writes row X.
    Process B does an UPDATE which writes row X.  - Deadlock error.
Process B gets a report that the transaction failed, and everything done in B's transaction is rolled back. The entire transaction has to be retried. Transactions are atomic if they commit, but can fail in a deadlock situation.

A SELECT does lock parts of the database. "Repeatable read" means that if you read the same data item twice within the same transaction, you get the same result, even if someone else is changing the data. This requires locking. If you try to update the data in conflict with another process, you'll get a deadlock error, but if you COMMIT a select-only transaction, you won't. You do have to COMMIT select-only transactions, or you'll fill memory with locks and stall out updates.

Ref: http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.htm...

Re: Hermitage: Testing the “I” in ACID

#7

Post by martinkl »

Animats wrote: post #5

A key concept here is that real databases have transactions, there's some concurrency between transactions, and that in the more efficient modes, transactions can deadlock, fail, and be rolled back. In MySQL, a statement within a transaction can return "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction". This can be forced by the following sequence of events. (This assumes an Inno…

What you describe is the Lost Update (P4) anomaly. It's in the test suite: https://github.com/ept/hermitage/blob/master/mysql.md#lost-u...

MySQL in repeatable read mode actually doesn't prevent this anomaly (it doesn't deadlock). You need to be in serializable mode to get the deadlock.

Re: Hermitage: Testing the “I” in ACID

#8

Post by wwilson »

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.

Here's one way you could run this test vs. FoundationDB:

https://gist.github.com/MMcM/f00108c5943e919f73d1

Re: Hermitage: Testing the “I” in ACID

#9

Post by Animats »

martinkl wrote: post #7
Animats wrote: post #5

A key concept here is that real databases have transactions, there's some concurrency between transactions, and that in the more efficient modes, transactions can deadlock, fail, and be rolled back. In MySQL, a statement within a transaction can return "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction". This can be forced by the following sequence of events. (This assumes an Inno…

What you describe is the Lost Update (P4) anomaly. It's in the test suite: https://github.com/ept/hermitage/blob/master/mysql.md#lost-u... MySQL in repeatable read mode actually doesn't prevent this anomaly (it doesn't deadlock). You need to be in serializable mode to get the deadlock.

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 database treated the update as a no-change transaction.)

Re: Hermitage: Testing the “I” in ACID

#10

Post by martinkl »

Animats wrote: post #9
martinkl wrote: post #7

Earlier quoted context omitted.

What you describe is the Lost Update (P4) anomaly. It's in the test suite: https://github.com/ept/hermitage/blob/master/mysql.md#lost-u... MySQL in repeatable read mode actually doesn't prevent this anomaly (it doesn't deadlock). You need to be in serializable mode to get the deadlock.

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.
Post reply on HN