Opened 15 months ago

Last modified 6 months ago

#21670 new Bug

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@… 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 (4)

comment:1 Changed 15 months ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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.

comment:2 Changed 14 months ago by carljm

  • Type changed from Uncategorized to Bug

comment:3 Changed 6 months ago by streeter

  • Cc django@… added

comment:4 Changed 6 months ago 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).

Note: See TracTickets for help on using tickets.
Back to Top