#23576: ORM delete strategy can lead to race conditions inside a transaction
-------------------------------------+-------------------------------------
Reporter: jdufresne | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.7
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by jdufresne):
This looks specific to database backends that do not support
`update_can_self_select`. From the list of database backends shipped with
Django, this applies only to MySQL.
This is the race free query generated for backends supporting
`update_can_self_select`.
{{{
DELETE FROM `myapp_c` WHERE `myapp_c`.`id` IN (SELECT U0.`id` AS `id` FROM
`myapp_c` U0 INNER JOIN `myapp_a` U1 ON ( U0.`a_id` = U1.`id` ) INNER JOIN
`myapp_b` U2 ON ( U0.`b_id` = U2.`id` ) WHERE (U1.`name` = 'foo' AND
U2.`name` = 'bar'))
}}}
The problematic queries used by the MySQL:
{{{
SELECT `myapp_c`.`id` FROM `myapp_c` INNER JOIN `myapp_a` ON (
`myapp_c`.`a_id` = `myapp_a`.`id` ) INNER JOIN `myapp_b` ON (
`myapp_c`.`b_id` = `myapp_b`.`id` ) WHERE (`myapp_a`.`name` = 'foo' AND
`myapp_b`.`name` = 'bar')
DELETE FROM `myapp_c` WHERE `myapp_c`.`id` IN ({ RESULT FROM PREVIOUS
QUERY })
}}}
The fact that this occurs across two queries allows rows to be inserted
that would normally meet the criteria of the `WHERE` clause.
One way to handle this would be to use MySQL's multiple table syntax
http://dev.mysql.com/doc/refman/5.7/en/delete.html which allows joins. The
query would be as simple as taking the `SELECT` query, removing the
`SELECT` clause and replacing it with `DELETE myapp_c FROM myapp_c INNER
JOIN ...`.
I am interested in doing this, however, I'm not sure exactly where to
start in order to build a better query. I see inside
`DeleteQuery.delete_qs()` is where the check for feature
`update_can_self_select` occurs. So perhaps a new feature check and branch
should occur here. However, once the feature is checked, I'm not sure how
to start building the alternative query. Any guidance would be
appreciated.
--
Ticket URL: <https://code.djangoproject.com/ticket/23576#comment:2>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.3d7c803c3e6a9a457fbcfa62aa0133a5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
