#36213: Add warning to documentation: QuerySet.update can execute two separate
SQL
queries when using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: (none)
Type: Uncategorized | Status: new
Component: Documentation | Version:
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage:
race-condition | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Babak:
Old 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 [https://forum.djangoproject.com/t/queryset-update-
> silently-turns-into-select-update-mysql/39095/2 forum] (thanks
> charettes), this is an undocumented
> [https://github.com/django/django/blob/240421c7c4c81fe5df26274b807266bd4ca73d7f/django/db/backends/mysql/features.py#L165-L167
> MySQL-only behaviour] because MySQL [https://dbfiddle.uk/Zfz_e9Kw 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.
New 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 [https://forum.djangoproject.com/t/queryset-update-
silently-turns-into-select-update-mysql/39095/2 forum] (thanks charettes),
this is an undocumented
[https://github.com/django/django/blob/240421c7c4c81fe5df26274b807266bd4ca73d7f/django/db/backends/mysql/features.py#L165-L167
MySQL-only behaviour] because MySQL [https://dbfiddle.uk/Zfz_e9Kw 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
[https://docs.djangoproject.com/en/5.1/ref/models/querysets/#django.db.models.query.QuerySet.update
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/010701953ab16efe-54c7710c-4037-4aff-a1f6-0ebe0ba80d37-000000%40eu-central-1.amazonses.com.
