Opened 11 years ago
Closed 8 years ago
#21670 closed Bug (wontfix)
New Model.save() mechanism causes deadlocks in mysql transactions
Reported by: | err | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.6 |
Severity: | Normal | Keywords: | |
Cc: | django@…, Mathias Dannesbo | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Change History (6)
comment:1 by , 11 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 11 years ago
Type: | Uncategorized → Bug |
---|
comment:3 by , 10 years ago
Cc: | added |
---|
comment:4 by , 10 years ago
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).
comment:5 by , 8 years ago
Cc: | added |
---|
comment:6 by , 8 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
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.
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.