Opened 8 years ago
Last modified 8 years ago
#27683 closed Cleanup/optimization
Change default transaction isolation level to READ COMMITTED on MySQL — at Initial Version
| Reported by: | Shai Berger | Owned by: | nobody |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | dev |
| Severity: | Normal | Keywords: | |
| Cc: | Adam Johnson | Triage Stage: | Ready for checkin |
| Has patch: | yes | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
There have been plenty of bug reports and discussions about the problems caused for users by MySQL's implementation of the REPEATABLE READ transaction isolation level, and the fact that it is the default. Django is mostly written for READ COMMITTED; of all supported backends, MySQL is the only one to use a different level by default. Things will probably run more in line with users expectations under READ COMMITTED, and reusable apps' behavior will be more similar across database backends.
Some history: This has been raised already in the 1.2 era, and possibly even before that; the question was raised in different forms again and again until one more instance caused it to be brought up on the mailing list. To be sure, it wasn't the first time the issue was brought to the list either, but this discussion seemed to conclude with a rough consensus that the default transaction isolation level for MySQL should change. As far as I'm aware, the issue has not been discussed since then.
There are two kinds of backwards-compatibility problems I already see with this change:
1) The obvious one -- Apps that are written to be correct under MySQL's REPEATABLE READ isolation level may become subtly incorrect by default. This is a very real problem, and hard to tackle because writing tests to capture the problems is very hard -- one needs to make and use at least two separate connections to the same database, and the Django ORM (and testing framework) does not make that easy. The counter-argument to that is that we likely have plenty of apps -- reusable or otherwise -- that are currently subtly wrong because MySQL's REPEATABLE READ semantics is surprising and unintuitive.
2) The less obvious one -- the way to set transaction isolation levels is with SQL executed when the connection is opened, and we need to make sure we don't interrupt with the user's own init_command if there is one.
Some very basic intro to transaction isolation levels in general and MySQL's REPEATABLE READ in particular is in my DC.EU.2016 lightning talk about this:
https://opbeat.com/community/posts/lightning-talks-day-1/ (starting around 4:00).