#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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
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.
