Opened 7 days ago
Last modified 5 days ago
#36213 new Cleanup/optimization
Add warning to documentation: QuerySet.update can execute two separate SQL queries when using MySQL — at Initial Version
| Reported by: | Babak | Owned by: | |
|---|---|---|---|
| Component: | Documentation | Version: | dev |
| Severity: | Normal | Keywords: | mysql self-select race-condition |
| Cc: | Simon Charette, John Speno | Triage Stage: | Accepted |
| Has patch: | no | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
When you have a QuerySet that has filter conditions based on related tables, the QuerySet.update() function will execute two separate SQL queries (a SELECT, followed by an UPDATE).
Examples:
BlogPost.objects.filter(published=True).update(foo="bar") (Causes a single UPDATE)
BlogPost.objects.filter(published=True, author__name="Foo Bar").update(foo="bar") (Causes SELECT THEN UPDATE)
As I was told in the forum (thanks charettes), this is an undocumented MySQL-only behaviour because MySQL doesn't allow self-select updates.
This will silently cause nasty race conditions since the update is no longer running as a single SQL statement.
Currently the docs for QuerySet.update say:
Using update() also prevents a race condition wherein something might change in your database in the short period of time between loading the object and calling save().
But in the case that I described, it causes the exact same type of race condition that the docs suggest that it prevents.
If the users are aware of this behaviour they can take care to avoid such filter conditions or to use alternative transaction handling mechanisms to ensure atomic behaviour.
I'd like to suggest for a warning to be added to the documentation about this.