#30904: Document caveats of MySQL SELECT FOR UPDATE when filtering against non-
indexed columns
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: nobody
Charette |
Type: | Status: new
Cleanup/optimization |
Component: | Version: 2.2
Documentation | Keywords: mysql deadlock
Severity: Normal | select_for_update index unique
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
We recently discovered that MySQL (tested with MySQL 8) will acquire an
exclusive write lock when performing a `SELECT ... FOR UPDATE` filtered on
non-indexed columns.
This was highly unexpected as it's barely documented but it can easily
bring a database to its knees because of the heavy contention it causes.
Given the following model
{{{#!python
class Entry(models.Model):
account = models.IntegerField(db_index=True)
segment = models.IntegerField()
value = models.IntegerField()
}}}
And a view or a command that does
{{{#!python
with transaction.atomic():
entry = list(Entry.objects.filter(account=account,
segment__gt=threshold).select_for_update())
}}}
Will cause an exclusive write lock to be acquired on the full `entry`
table and not only on the row `WHERE account = account AND segment <
threshold` because `segment` is not indexed. This might seem like an
evidence here but because of how queryset accumulate filters and can be
passed around it's not that hard to shoot yourself in the foot.
I suggest we add a mention in the `FOR UPDATE`
[https://docs.djangoproject.com/en/dev/ref/databases/#row-locking-with-
queryset-select-for-update documentation] that goes along the following
lines
> Make sure you filter against at least set of fields contained in a
unique constraint or only against fields covered by indices on MySQL when
using `select_for_update` else an exclusive write lock will be acquired
over the full table for the duration of the transaction.
While a small admonition might not prevent this issue from happening it
will at least provide a description of the problem to those who encounter
a suddenly elevated number of deadlock on write attempts. If this is
deemed too niche to land in the documentation this ticket will at least
provide some search engine indexed background about a possible solution
instead of going on a wild goose chase like we did.
--
Ticket URL: <https://code.djangoproject.com/ticket/30904>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/052.13a35323935a9402ba50e66ac2eb4f3d%40djangoproject.com.
