Hacker News Reader

Comment by martinkl | Hacker News Reader

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.

View on HN

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

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.

Reply on news.ycombinator.com