[Django] #36213: Add warning to documentation: QuerySet.update can execute two separate SQL queries when using MySQL

[Django]

#36213: Add warning to documentation: QuerySet.update can execute two separate SQL
queries when using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Type:
| Uncategorized
Status: new | Component:
| Documentation
Version: | Severity: Normal
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
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36213>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#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:

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]

#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
-------------------------------------+-------------------------------------

Comment (by Babak):

Here's a
[https://github.com/bbkfhq/django/commit/e20f91e55f37f0ed8e968b83500c072b80dcf645
draft] that I wrote. Happy to submit a PR if it's appropriate.
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:2>