changing the default MySQL isolation level to READ COMMITTED

597 views
Skip to first unread message

Tim Graham

unread,
Jan 11, 2017, 7:34:09 AM1/11/17
to Django developers (Contributions to Django itself)
In ticket #27683 [0], Shai proposed to change Django to default to using the READ COMMITTED isolation level. Some background on the reasons are on the ticket and in another thread [1] (possible data loss is involved).

The initial approach [2] that Shai put forward is adding a deprecation warning and a system check:
- The deprecation warning says, "Django's default transaction isolation level on MySQL is going to change from REPEATABLE READ (MySQL's default) to READ COMMITTED. Specify an explicit level by setting 'isolation_level' in 'OPTIONS' in the connection settings."
- The system check warning (visible when running migrate) says, "Transaction isolation level for database connection 'default' is 'REPEATABLE-READ'. Django and many of its apps are written to work correctly under the READ COMMITTED transaction isolation level. MySQL's default level, REPEATABLE READ, may imply some surprising behaviors under concurrent loads, leading to possible data loss. It is recommended that you change the transaction isolation level."

If an isolation level isn't specified in DATABASES['OPTIONS'], Django would start setting a READ COMMITTED isolation level in Django 2.1. Both the system check (since it wouldn't trigger anymore) and the deprecation warning would be removed at that time. The reason for having both a check and a deprecation warning seems to be that deprecation warnings are silent by default.

An alternate idea is to skip the deprecation cycle and make Django start setting the default isolation in Django 1.11. I'm not sure if giving projects two release cycles to choose their isolation level adds value. In the "no thinking involved" case, it's not difficult to specify what's being used now, REPEATABLE READ. If Django changed the isolation level in 1.11, a "compatibility" system check could raise a warning if 'isolation_level' isn't specified in the database settings. This leaves projects in the same place as the first approach, where all MySQL projects have to specify an isolation level to silence a warning. That check would be removed in Django 2.0, which avoids another 8 months of new MySQL projects (started with Django 2.0) having a check and/or deprecation warning out of the box that the first approach requires. (Ideally, the compatibility system check could differentiate between existing projects and new projects in Django 1.11, but past heuristics to do this involving examining settings and comparing them to the startproject defaults haven't been accurate.)

Shai said, "The deprecation period may be required because although the warnings and settings are at the project level, there may be code changes required at the app level, and it may be better to keep the new projects aware of the issue while 3rd parties adapt."

I don't know enough to say whether or not that's the case. Anyway, I think a project could evaluate their apps and make a decision to stay with REPEATABLE READ or to switch to READ COMMITTED. How do MySQL users feel about the two approaches?

[0] https://code.djangoproject.com/ticket/27683
[1] https://groups.google.com/d/topic/django-developers/6pWbpV1_6Us/discussion
[2] https://github.com/django/django/pull/7826

Patryk Zawadzki

unread,
Jan 11, 2017, 9:50:27 AM1/11/17
to Django developers (Contributions to Django itself)
To add some context, REPEATABLE READ can break get_or_create among other things (not sure how many invalid tickets Django gets related to that).

Tim Graham

unread,
Jan 13, 2017, 8:52:23 AM1/13/17
to Django developers (Contributions to Django itself)
I guess three days is too little time to get a consensus on this. At this point I'm thinking to defer this from 1.11.

Aymeric Augustin

unread,
Jan 13, 2017, 10:05:51 AM1/13/17
to django-d...@googlegroups.com
Hello,

I think there’s been little feedback because this issue is very, very far from the concerns of most Django users — aside from some very performance-sensitive websites that already worked around Django’s bugs and will add the configuration line needed to keep whatever the isolation level they chose.

If I was trying to merge that change, I think I’d just document the backwards incompatibility and call it a day. But as everyone knows, I’m taking backwards compatibility less seriously than most.

Given the uncertainty around the consequences of this change, I don’t think Shai’s approach is out of place, even though it creates an overhead for every Django project using MySQL.

Since Shai is leading the effort and considering the general lack of feedback on this issue, I think it’s fair to let him make the final call and keep the deprecation path if he thinks that’s safer.

Regardless, I’d love if this fix made it into 1.10, let’s not delay it because we’re worried of being too cautious :-)

-- 
Aymeric.


--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/42212944-d7e7-4c33-8c34-8e235a7d515a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Adam Johnson

unread,
Jan 13, 2017, 10:11:26 AM1/13/17
to django-d...@googlegroups.com
 aside from some very performance-sensitive websites that already worked around Django’s bugs

Tbf it's true I already added READ-COMMITTED at work

 I’d love if this fix made it into 1.10


 

On 13 January 2017 at 15:05, Aymeric Augustin <aymeric.au...@polytechnique.org> wrote:
Hello,

I think there’s been little feedback because this issue is very, very far from the concerns of most Django users — aside from some very performance-sensitive websites that already worked around Django’s bugs and will add the configuration line needed to keep whatever the isolation level they chose.

If I was trying to merge that change, I think I’d just document the backwards incompatibility and call it a day. But as everyone knows, I’m taking backwards compatibility less seriously than most.

Given the uncertainty around the consequences of this change, I don’t think Shai’s approach is out of place, even though it creates an overhead for every Django project using MySQL.

Since Shai is leading the effort and considering the general lack of feedback on this issue, I think it’s fair to let him make the final call and keep the deprecation path if he thinks that’s safer.

Regardless, I’d love if this fix made it into 1.10, let’s not delay it because we’re worried of being too cautious :-)

-- 
Aymeric.


On 13 Jan 2017, at 14:52, Tim Graham <timog...@gmail.com> wrote:

I guess three days is too little time to get a consensus on this. At this point I'm thinking to defer this from 1.11.

On Wednesday, January 11, 2017 at 9:50:27 AM UTC-5, Patryk Zawadzki wrote:
To add some context, REPEATABLE READ can break get_or_create among other things (not sure how many invalid tickets Django gets related to that).

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--
Adam

Tim Graham

unread,
Jan 13, 2017, 10:44:31 AM1/13/17
to Django developers (Contributions to Django itself)
In the end, I don't use MySQL but if a similar change were made for PostgreSQL, I would find the current approach more annoying (bothering me about changing defaults over two release cycles) than cautious.

I'm also uncertain that duplicating a deprecation warning in a system check is a good precedent to set. Adam, is the current implementation what you envisioned when you made your comment and what do you see as the advantages of that?

Aymeric, I assume "I’d love if this fix made it into 1.10" was a typo for 1.11. Anyway, the "Allowed transaction isolation level to be chosen on MySQL" commit isn't controversial. I'll try to get at least that in.


On Friday, January 13, 2017 at 10:11:26 AM UTC-5, Adam Johnson wrote:
 aside from some very performance-sensitive websites that already worked around Django’s bugs

Tbf it's true I already added READ-COMMITTED at work

 I’d love if this fix made it into 1.10


 
On 13 January 2017 at 15:05, Aymeric Augustin <aymeric.au...@polytechnique.org> wrote:
Hello,

I think there’s been little feedback because this issue is very, very far from the concerns of most Django users — aside from some very performance-sensitive websites that already worked around Django’s bugs and will add the configuration line needed to keep whatever the isolation level they chose.

If I was trying to merge that change, I think I’d just document the backwards incompatibility and call it a day. But as everyone knows, I’m taking backwards compatibility less seriously than most.

Given the uncertainty around the consequences of this change, I don’t think Shai’s approach is out of place, even though it creates an overhead for every Django project using MySQL.

Since Shai is leading the effort and considering the general lack of feedback on this issue, I think it’s fair to let him make the final call and keep the deprecation path if he thinks that’s safer.

Regardless, I’d love if this fix made it into 1.10, let’s not delay it because we’re worried of being too cautious :-)

-- 
Aymeric.


On 13 Jan 2017, at 14:52, Tim Graham <timog...@gmail.com> wrote:

I guess three days is too little time to get a consensus on this. At this point I'm thinking to defer this from 1.11.

On Wednesday, January 11, 2017 at 9:50:27 AM UTC-5, Patryk Zawadzki wrote:
To add some context, REPEATABLE READ can break get_or_create among other things (not sure how many invalid tickets Django gets related to that).

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.



--
Adam

Adam Johnson

unread,
Jan 23, 2017, 4:16:06 PM1/23/17
to django-d...@googlegroups.com
Sorry for the radio silence, I've been busy at work.

After re-reading things and thinking a bit more about it, I think Tim's suggestion of just changing the default in a release is probably the right idea. The warning + check does seem very bothersome; as Aymeric says, this is very far from the concerns of most Django users. We might still want a system check for if the user is using repeatable-read as a guard against the bugs that have been seen.

Anyway presumably it's too late to make the change in 1.11 because feature freeze means feature freeze, so I guess we target this at 2.0 now.

To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--
Adam

Adam Johnson

unread,
Jan 23, 2017, 4:20:58 PM1/23/17
to Django developers (Contributions to Django itself)
Sorry for my radio/email silence, I had a lot of things to do at work and wanted to think this through a bit more.

Looking again at the warning + check implementation, it is a bit messy. My goal with the check was to get a message in front of users who need it, but I suppose the release notes are good enough for this. As Aymeric says, this is very far from most Django users' concerns; it would be bothersome for most users to have to change some configuration. So I think we can get away with changing the isolation level in a release without the pre-warning message. Potentially we could still have a system check that returns a warning if using repeatable-read, because it can lead to all the bugs we've seen.

Presumably it's too late to make the change in 1.11 (or is it...?) so 2.0 it is.

Adam Johnson

unread,
Jan 23, 2017, 4:32:28 PM1/23/17
to django-d...@googlegroups.com
(I sent basically the same email twice because it looked to me like the first was lost, didn't even show in my outbox :/)
--
Adam

Anssi Kääriäinen

unread,
Jan 24, 2017, 7:20:43 AM1/24/17
to Django developers (Contributions to Django itself)
For reference there are some discussions about this in https://code.djangoproject.com/ticket/13906. The short version of the discussions is that MySQL is very interesting when it comes to transaction isolation levels and things like SELECT FOR UPDATE...

I think it would be ok to allow changing the isolation level, set the default to read committed, and document that if you use some other isolation level you are on your own. We could of course allow using different isolation levels on other databases, too. PostgreSQL's true serializable transactions for example are extremely nice for some application domains.

  -Anssi
Reply all
Reply to author
Forward
0 new messages