id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 23576 Fast-path deletion for MySQL Jon Dufresne Simon Charette "Originally posted to #19544 due to similar error message, but I this is a different ticket. ---- I have investigated this a bit more. For me the race occurs for the following reason: Suppose you have `MyModel` that has a unique constraint. Suppose you have a view of the form: {{{ @transaction.atomic def my_view(request): MyModel.objects.some_long_filter().delete() objs = builds_list_of_unsaved_models_from_request(request) MyModel.objects.bulk_create(objs) }}} That is, a view that builds a list of objects in bulk. The view first deletes the objects that were created by any previous requests to ensure the unique constraint is not violated. From my investigation, the `delete()` will not actually call an SQL `DELETE` statement directly -- as I would have expected -- but instead caches a list of ids of objects that exist, then calls a new SQL query with a `DELETE .. WHERE id IN (list of ids)`. This all happens in `DeleteQuery.delete_qs()` file `django/db/models/sql/subqueries.py:52`. Now suppose you have some original data. Then, two requests occur at the same time. The first request and the second request will both cache the same ids from the original data to delete. Suppose the first request finishes slightly earlier and commits the *new objects with new ids*. Now, the second request will try to delete the cached ids from the original data and *not* the newly created ids the first request. Upon save there is a unique constraint violation -- as the wrong ids were deleted -- causing an `IntegrityError`. Coming from an application with many raw SQL queries, this unique constraint violation was a big surprise inside a transaction. The caching of ids in the `delete()` function seems a sneaky source of race conditions. ---- Traceback when the IntegrityError occurs: {{{ Traceback (most recent call last): File ""manage.py"", line 13, in execute_from_command_line(sys.argv) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/core/management/__init__.py"", line 399, in execute_from_command_line utility.execute() File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/core/management/__init__.py"", line 392, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/core/management/base.py"", line 242, in run_from_argv self.execute(*args, **options.__dict__) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/core/management/base.py"", line 285, in execute output = self.handle(*args, **options) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/core/management/base.py"", line 415, in handle return self.handle_noargs(**options) File ""/home/jon/devel/myproj/development/myapp/management/commands/thetest.py"", line 36, in handle_noargs MyModel.objects.bulk_create(objs) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/models/manager.py"", line 160, in bulk_create return self.get_queryset().bulk_create(*args, **kwargs) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/models/query.py"", line 359, in bulk_create self._batched_insert(objs_without_pk, fields, batch_size) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/models/query.py"", line 838, in _batched_insert using=self.db) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/models/manager.py"", line 232, in _insert return insert_query(self.model, objs, fields, **kwargs) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/models/query.py"", line 1514, in insert_query return query.get_compiler(using=using).execute_sql(return_id) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/models/sql/compiler.py"", line 903, in execute_sql cursor.execute(sql, params) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/backends/util.py"", line 69, in execute return super(CursorDebugWrapper, self).execute(sql, params) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/backends/util.py"", line 53, in execute return self.cursor.execute(sql, params) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/utils.py"", line 99, in __exit__ six.reraise(dj_exc_type, dj_exc_value, traceback) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/backends/util.py"", line 53, in execute return self.cursor.execute(sql, params) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/django/db/backends/mysql/base.py"", line 124, in execute return self.cursor.execute(query, args) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/MySQLdb/cursors.py"", line 205, in execute self.errorhandler(self, exc, value) File ""/home/jon/devel/myproj/development/venv/lib/python2.7/site-packages/MySQLdb/connections.py"", line 36, in defaulterrorhandler raise errorclass, errorvalue django.db.utils.IntegrityError: (1062, ""Duplicate entry 'mytype-7-6' for key 'type'"") }}}" New feature closed Database layer (models, ORM) dev Normal fixed Ready for checkin 1 0 0 0 0 0