On Wed, Sep 26, 2012 at 4:07 AM, Daniel Kinzler daniel@brightbyte.dewrote:
On 26.09.2012 12:06, Asher Feldman wrote:
On Wednesday, September 26, 2012, Daniel Kinzler wrote:
I see your point. But if we have the choice between lock contention and silent data loss, which is better?
This isn't really a choice - by default, when a statement in mysql hits a lock timeout, it is rolled back but the transaction it's in is not.
Uh. That sounds evil and breaks the A in ACID, no? Why isn't the entire transaction rolled back in such a case?
There's a distinction (possibly misguided) between cases where a statement can be retried with an expectation of success, and cases that aren't which trigger an implicit rollback. Deadlocks are considered the latter by mysql, they result in a transaction rollback. Oracle behaves the same way as mysql with regards to lock timeouts - it's up to developers to either retry the timed-out statement, or rollback. The results can definitely be evil if not handled correctly, but it's debatable if it's a violation of atomicity.
If lock timeout throws an exception that closes the connection to mysql, at least that will result in a rollback. If the connection is pooled and reused, it can likely result in a commit.
Mysql does offer a "rollback_on_timeout" option that changes the default behavior. We can enable it at wmf, but since that may not be an option for many installs, it's better to work around it.
That
can also lead to data loss via partial writes in real world cases if not properly accounted for by the application.
How could we detect such a case?
I can't think of a way that's actually good. Better to account for the behavior.
That doesn't mean that we should give up on consistancy or
that we shouldn't try to do better, but not in exchange for more lock contention.
Well, improving consistency and avoiding data loss is going to be hard without the use of locks... how do you propose to do that?
We could try to identify cases where consistency is extremely important, vs. where it isn't. In the cases where a very important lock holding transaction will be entered, can we defer calling hooks or doing anything unrelated until that transaction is closed at its intended endpoint? If so, perhaps everything else can be subject to current behavior, where unrelated code can call commit.