MySQL deadlock prevention in Django ORM

Question

Using Django in a MySQL database, I get the following error:

OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction') 

The error occurs in the following code:

 start_time = 1422086855 end_time = 1422088657 self.model.objects.filter( user=self.user, timestamp__gte=start_time, timestamp__lte=end_time).delete() for sample in samples: o = self.model(user=self.user) o.timestamp = sample.timestamp ... o.save() 

I have several parallel processes working with the same database, and sometimes they can have the same job or match sample data. Therefore, I need to clear the database and then save new samples, since I do not want duplicates.

I run all this in a transactional block with transaction.commit_on_success() and quite often get an OperationalError exception. I would prefer that the transaction does not come to a standstill, but instead simply blocks and waits for another process to complete with its work.

From what I read, I have to order the locks correctly, but I'm not sure how to do this in Django.

What is the easiest way to ensure that I don't get this error while still making sure that I don't lose any data?

Answer 1

Use the select_for_update () method:

 samples = self.model.objects.select_for_update().filter( user=self.user, timestamp__gte=start_time, timestamp__lte=end_time) for sample in samples: # do something with a sample sample.save() 

Please note that you must not delete selected samples or create new ones. Just refresh the filtered entries. A lock for these records will be released, then your transaction will be committed.

BTW instead of __gte / __lte you can use __range :

 samples = self.model.objects.select_for_update().filter( user=self.user, timestamp__range=(start_time, end_time)) 

Answer 2

To avoid deadlocks, I made a way to re-query the request in the event of a deadlock.

To do this, I did that I monkey paid the "execute" method of the django CursorWrapper class. This method is called whenever a request is made, so it will work throughout ORM, and you don’t have to worry about deadlocks in your project:

 import django.db.backends.utils from django.db import OperationalError import time original = django.db.backends.utils.CursorWrapper.execute def execute_wrapper(*args, **kwargs): attempts = 0 while attempts < 3: try: return original(*args, **kwargs) except OperationalError as e: code = e.args[0] if attempts == 2 or code != 1213: raise e attempts += 1 time.sleep(0.2) django.db.backends.utils.CursorWrapper.execute = execute_wrapper 

What the code does above: it will try to start the request, and if the OperationalError statement is reset with error code 1213 (deadlock), it will wait 200 ms and try again. He will do this 3 times, and if after 3 times the problem is not resolved, the original exception will be raised.

This code should be executed when the django project is loaded into memory, so it’s convenient to place it in the __ini__.py file of any of your applications (I put it in the __ini__.py file of my main project directory - the one that has the same name as the project django).

Hope this helps anyone in the future.