Does django create implicit transaction on first query?

Question

I have django 1.3.1, python2.6 and MySQL 5.5.20. init_command in setting.py is set to SET storage_engine=INNODB.

I have the following situation:

Consider the model:

class Fruit(models.Model):
    name = models.CharField(max_length=50)

    def __unicode__(self):
        return self.name

Now I open two Django shells and type:

django shell I:

./manage.py shell
(InteractiveConsole)
>>> import fruits.models as m
>>> m.Fruit(name="apple").save()
>>> m.Fruit.objects.get(pk=1)    
<Fruit: apple>

django shell II:

./manage.py shell
(InteractiveConsole)
>>> import fruits.models as m
>>> m.Fruit.objects.get(pk=1)    
<Fruit: apple>

So far so good. Now I continue to type in shell I:

>>> m.Fruit(name="peach").save()
>>> m.Fruit.objects.get(pk=2)
<Fruit: peach>

Qestion: Why typing the following in the shell II does not find the object?

>>> m.Fruit.objects.get(pk=2)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "..../django/db/models/manager.py", line 132, in get
    return self.get_query_set().get(*args, **kwargs)
  File "..../django/db/models/query.py", line 349, in get
    % self.model._meta.object_name)
DoesNotExist: Fruit matching query does not exist.

The only way I've found to make shell II to have "a fresh look" is this:

>>> from django.db import transaction
>>> transaction.enter_transaction_management()
>>> transaction.rollback()

And I need to roolback() each time I query for fruits if I want to be sure that orm will not "lie".

You can relax isolation level to read commited: http://stackoverflow.com/questions/2280779/django-transaction-isolation-level-in-mysql-postgresql (see Peter Lundberg answer) — dani herrera, Aug 27 '12 at 20:46
But "peach" is committed. If I exit shell 2 and run it again, I'll see it — Zaar Hai, Aug 27 '12 at 20:54
@danihp, yes you are actually right. This is MySQL issue and django one's. Please write this as an answer and I will accept it. Thanks. — Zaar Hai, Aug 27 '12 at 21:01

dani herrera · Accepted Answer · 2012-08-27T23:45:31.097

2

This is a issolation issue. You can relax to 'isolation level read commited' to avoid this behavior. Also, learn about this issolation level implications (phantoms).

To set Read Commited Isolation Level into MySQL for your django project you should set this parameter in settings.py:

DATABASE_OPTIONS = { 
     "init_command": 'SET storage_engine=INNODB,    \
                      SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', }

edited Aug 27 '12 at 23:45

answered Aug 27 '12 at 21:06

dani herrera

48,760
8
117
177

As a note, current MySQL isolation level can be checked using the following: `select @@session.tx_isolation;` – Zaar Hai Aug 27 '12 at 21:24
Can you give an example of "concurrency exception"? – Zaar Hai Aug 27 '12 at 21:25
A concurrency exception is raised when two users attempt to change the same data in a database at the same time. Is more complex than this: each isolation level has different rules, a transaction can handle several data changes, etc. – dani herrera Aug 27 '12 at 21:54
Remember that exists auto commit decorator that is also a solution to your issue: https://docs.djangoproject.com/en/1.2/topics/db/transactions/#django-db-transaction-autocommit – dani herrera Aug 27 '12 at 23:00
**IMPORTANT**: Its not enough to set init_command in settings.py - in **celery** it does not seem to be applied, so isolation level should be set on database-wide level. – Zaar Hai Aug 29 '12 at 09:24

Does django create implicit transaction on first query?

1 Answers1

Linked