[Django] #21670: New Model.save() mechanism causes deadlocks in mysql transactions

Django

unread,

Dec 25, 2013, 6:03:06 AM12/25/13

to django-...@googlegroups.com

#21670: New Model.save() mechanism causes deadlocks in mysql transactions
----------------------------------------------+--------------------
Reporter: err | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
With default mysql isolation level (repeatable read).
For example I have this model:

{{{
class TestTable(models.Model):
id = models.IntegerField(primary_key=True) # not autoincrement
name = models.CharField(max_length=1024)
}}}

And this code

{{{
with transaction.atomic():
m = TestTable()
m.id = N
m.save()
}}}

And when I have 2 parallel model.save() (with transaction.atomic) I'm
immediately getting deadlocks.

for example:
transaction 1:
set autocommit=0;UPDATE `test_table` SET `name` = '' WHERE
`test_table`.`id` = 1; # nothing updated. this update causes mysql gap
locking

transaction 2:
set autocommit=0;UPDATE `test_table` SET `name` = '' WHERE
`test_table`.`id` = 2; # nothing updated. this update causes mysql gap
locking

transaction 1:
INSERT INTO `test_table` (`id`, `name`) VALUES (1, ''); # waiting for lock

transaction 2:
INSERT INTO `test_table` (`id`, `name`) VALUES (2, '');
# boom. deadlock

I dont know the solution. Maybe recommeded mysql isolation level should be
"read commited" or maybe old save mechanism should be used.

--
Ticket URL: <https://code.djangoproject.com/ticket/21670>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Dec 28, 2013, 9:39:01 AM12/28/13

to django-...@googlegroups.com

#21670: New Model.save() mechanism causes deadlocks in mysql transactions

-------------------------------------+-------------------------------------

Reporter: err | Owner: nobody
Type: Uncategorized | Status: new

* needs_better_patch: => 0
* stage: Unreviewed => Accepted
* needs_tests: => 0
* needs_docs: => 0

Comment:

I can confirm this.

You can switch back to the old save() behaviour by setting select_on_save
meta flag. Maybe this should be documented as another gotcha where you
want to set this flag in the release notes?

Luckily this issue should be relatively rare as this will not happen when
doing normal saves with autoincrement primary keys.

I'll mark this as accepted, at least release note change should be done.

--
Ticket URL: <https://code.djangoproject.com/ticket/21670#comment:1>

Django

unread,

Jan 27, 2014, 12:51:28 PM1/27/14

to django-...@googlegroups.com

#21670: New Model.save() mechanism causes deadlocks in mysql transactions

-------------------------------------+-------------------------------------
Reporter: err | Owner: nobody
Type: Bug | Status: new

Changes (by carljm):

* type: Uncategorized => Bug

--
Ticket URL: <https://code.djangoproject.com/ticket/21670#comment:2>

Django

unread,

Sep 19, 2014, 10:28:34 AM9/19/14

to django-...@googlegroups.com

#21670: New Model.save() mechanism causes deadlocks in mysql transactions

-------------------------------------+-------------------------------------
Reporter: err | Owner: nobody

Changes (by streeter):

* cc: django@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/21670#comment:3>

Django

unread,

Oct 3, 2014, 3:56:58 AM10/3/14

to django-...@googlegroups.com

#21670: New Model.save() mechanism causes deadlocks in mysql transactions

-------------------------------------+-------------------------------------
Reporter: err | Owner: nobody

Comment (by vldmit):

This affects models with multi-table inheritance, as child model's primary
key is not auto incremented. Concurrent insert lead to deadlocks.

`select_on_save = True` in model's solve the problem. I believe this
option needs to be enabled for multi-table inheritance models, as inserts
are not guaranteed without it (at least when using MySQL).

--
Ticket URL: <https://code.djangoproject.com/ticket/21670#comment:4>

Django

unread,

Sep 17, 2016, 4:10:17 PM9/17/16

to django-...@googlegroups.com

#21670: New Model.save() mechanism causes deadlocks in mysql transactions

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by neic):

* cc: neic (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/21670#comment:5>

Django

unread,

Feb 11, 2017, 2:35:38 PM2/11/17

to django-...@googlegroups.com

#21670: New Model.save() mechanism causes deadlocks in mysql transactions

-------------------------------------+-------------------------------------
Reporter: err | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: 1.6
(models, ORM) |

Severity: Normal | Resolution: wontfix

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Tim Graham):

* status: new => closed
* resolution: => wontfix

Comment:

In Django 2.0, the MySQL backend will default to read committed (#27683).
I think we can close this ticket in light of that. The resolution is
"wontfix" in the sense that there's still a problem under repeatable read.
If my analysis is incorrect, please reopen with more details as I'm not a
MySQL user.

--
Ticket URL: <https://code.djangoproject.com/ticket/21670#comment:6>

Reply all

Reply to author

Forward