CODEWITHSUNDEEP
mysqldjangotransactionsdata-integrity
Antoine Pinsard
Antoine Pinsard

Reputation: 34922

Transactions not working in Django using MySQL backend

I am regularily getting integrity errors for the following code snippet:

 class StatsManager(Manager):

    @transaction.atomic
     def create(self, **kwargs):
         kwargs.setdefault('date', date.today())
         try:
             obj = self.get_queryset().get(**kwargs)
         except self.model.DoesNotExist:
             obj = super().create(hits=1, **kwargs)  # line 28
         else:
             obj.hits = F('hits') + 1
             obj.save()
         return obj

Here is the error message: 

IntegrityError at /emploi/cours-particuliers-en-physique-chimie-en-classe-de-superieur-1-evry-14skb.html
(1062, "Duplicate entry 'EMP_VIEW-1903259-2018-01-08' for key 'statEvent'")

And the traceback:

File "/home/www/aladom_v6/www/stats/models/managers.py" in create                                                                                                                                                                                                                
  26.             obj = self.get_queryset().get(**kwargs)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/query.py" in get
  380.                 self.model._meta.object_name

      During handling of the above exception (Stats matching query does not exist.), another exception occurred:


File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/backends/utils.py" in execute
  65.                 return self.cursor.execute(sql, params)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django_mysql/monkey_patches.py" in execute

  35.         return orig_execute(self, sql, args)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/backends/mysql/base.py" in execute
  101.             return self.cursor.execute(query, args)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in execute
  250.             self.errorhandler(self, exc, value)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  50.         raise errorvalue

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in execute
  247.             res = self._query(query)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in _query
  411.         rowcount = self._do_query(q)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in _do_query
  374.         db.query(q)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/connections.py" in query
  277.             _mysql.connection.query(self, query)


      The above exception ((1062, "Duplicate entry 'EMP_VIEW-1903259-2018-01-08' for key 'statEvent'")) was the direct cause of the following exception:



File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/core/handlers/exception.py" in inner
  41.             response = get_response(request)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/core/handlers/base.py" in _get_response
  187.                 response = self.process_exception_by_middleware(e, request)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/channels/handler.py" in process_exception_by_middleware 
  243.             return super(AsgiHandler, self).process_exception_by_middleware(exception, request)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/core/handlers/base.py" in _get_response
  185.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/views/generic/base.py" in view
  68.             return self.dispatch(request, *args, **kwargs)

File "/home/www/aladom_v6/www/utils/views/behaviors.py" in dispatch
  343.             return super().dispatch(request, *args, **kwargs)

File "/home/www/aladom_v6/www/offers/views/public.py" in dispatch
  1089.         return super().dispatch(request, *args, **kwargs)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/views/generic/base.py" in dispatch
  88.         return handler(request, *args, **kwargs)

File "/home/www/aladom_v6/www/offers/views/public.py" in get
  1308.         return super().get(request, *args, **kwargs)

File "/home/www/aladom_v6/www/stats/views/behaviors.py" in get
  16.                                  target_id=self.object.pk)

File "/usr/lib/python3.4/contextlib.py" in inner
  30.                 return func(*args, **kwds)

File "/home/www/aladom_v6/www/stats/models/managers.py" in create
  28.             obj = super().create(hits=1, **kwargs)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/manager.py" in manager_method
  85.                 return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/query.py" in create
  394.         obj.save(force_insert=True, using=self.db)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/base.py" in save
  808.                        force_update=force_update, update_fields=update_fields)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/base.py" in save_base
  838.             updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/base.py" in _save_table
  924.             result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/base.py" in _do_insert
  963.                                using=using, raw=raw)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/manager.py" in manager_method
  85.                 return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/query.py" in _insert
  1076.         return query.get_compiler(using=using).execute_sql(return_id)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/models/sql/compiler.py" in execute_sql
  1107.                 cursor.execute(sql, params)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/backends/utils.py" in execute
  65.                 return self.cursor.execute(sql, params)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/utils.py" in __exit__
  94.                 six.reraise(dj_exc_type, dj_exc_value, traceback)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/utils/six.py" in reraise
  685.             raise value.with_traceback(tb)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/backends/utils.py" in execute
  65.                 return self.cursor.execute(sql, params)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django_mysql/monkey_patches.py" in execute
  35.         return orig_execute(self, sql, args)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/django/db/backends/mysql/base.py" in execute
  101.             return self.cursor.execute(query, args)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in execute
  250.             self.errorhandler(self, exc, value)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  50.         raise errorvalue

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in execute
  247.             res = self._query(query)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in _query
  411.         rowcount = self._do_query(q)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/cursors.py" in _do_query
  374.         db.query(q)

File "/home/www/.virtualenvs/aladom/lib/python3.4/site-packages/MySQLdb/connections.py" in query
  277.             _mysql.connection.query(self, query)

Line 28 raises a duplicate entry error while I checked right above whether the key exists or not, and encapsulated it in a transaction.

I'm using Django 1.11, so I tried to set MySQL isolation level to read committed. I thought it would fix that issue, but it still occurs.

I also tried to do it the other way round:

 class StatsManager(Manager):

     def create(self, **kwargs):
         kwargs.setdefault('date', date.today())
         try:
             obj = super().create(hits=1, **kwargs)
         except IntegrityError:
             obj = self.get_queryset().get(**kwargs)  # line 27
             obj.hits = F('hits') + 1
             obj.save()
         return obj

But in this case, it sometimes fail with the following error, because my requests are encapsulated in transactions:

TransactionManagementError at /admin/moderation/serviceoffermoderation/248424/change/
An error occurred in the current transaction. You can't execute queries until the end of the 'atomic' block.

Which is actually expected, unlike the former error.

Any idea what's going on and how to deal with it?

Upvotes: 0

Views: 582

Answers (1)

Alasdair
Alasdair

Reputation: 308769

It looks like you could rewrite your code to use get_or_create, which has some handling for race conditions.

To avoid the An error occurred in the current transaction error, you need to wrap the code that can raise the IntegrityError with transaction.atomic:

 def create(self, **kwargs):
     kwargs.setdefault('date', date.today())
     try:
         with transaction.atomic():
             obj = super().create(hits=1, **kwargs)
     except IntegrityError:
         obj = self.get_queryset().get(**kwargs)  # line 27
         obj.hits = F('hits') + 1
         obj.save()
     return obj

See the docs on controlling transactions explicitly for more info.

Upvotes: 1

Related Questions