id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
26347	Saving ManyToMany field under race condition causes data loss on MySQL	Hugo Chargois	nobody	"I had to investigate mysteriously disappearing contents of some ManyToManyFields of some Model. Everyone who had access to the admin site assured me that one day the contents were there, and the next day they weren't anymore, even though they didn't touch those fields. Of course, we had absolutely no custom code to edit those fields, and it looks like they didn't lie, nothing showed up in the admin history. I was stumped.

I found that they were erased whenever someone did a double-click on the ""Save"" button (unintentionally, hopefully).

It took me some time to really identify the root of the problem...

Looking at the database log, I found that on each save, even if the field is untouched, this happens (a bit simplified):

{{{
DELETE * FROM relation_table WHERE from_id = <our_object_id>;
SELECT * FROM relation_table WHERE from_id = <our_object_id> and to_id in (<related1_id>, <related2_id>,... );
INSERT INTO relation_table (from_id, to_id) VALUES (<our_object_id>, <related1_id>), (<our_object_id>, <related2_id>), ...;
}}}

The DELETE then INSERT behavior is visible here: https://github.com/django/django/blob/1.8.11/django/db/models/fields/related.py#L1271

And the SELECT in between spawns from the manager.add, from this code that checks that we only insert what is not already present:
https://github.com/django/django/blob/1.8.11/django/db/models/fields/related.py#L1090

So, yeah, deleting everything, selecting from what we just deleted (that should always be nothing, right?) and inserting back the same things is a really weird, unoptimized and dangerous way of doing nothing, but since it is all in a nice transaction, that should always work, right? Right?

Well, no. Not with MySQL of course.

Sometimes, a race condition makes it possible that the SELECT after the DELETE does return some old rows that really aren't there anymore. This fools the manager.add to think that it has nothing to INSERT since the rows are already there. But as soon as the transaction is finished, no rows are effectively there anymore.

This behavior of MySQL is called ""consistent read"" and is well documented: https://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html

Another bug report that arose due to the same MySQL ""feature"": #13906
I'm filing another report because that previous one seems to go nowhere and focuses on get_or_create(), whereas I'm showing here that this MySQL behavior can also cause very obscure data loss, and is thus IMHO of the utmost importance and should be fixed ASAP."	Cleanup/optimization	closed	Database layer (models, ORM)	1.8	Normal	wontfix	mysql transaction	django@…	Accepted	0	0	0	0	0	0