Django application does not see db changes made by external sources

[Sharjeel Ahmed Qureshi]

Hi,

I am running across this problem. For me a django application does not get the latest snapshot of database when the data is modified by some other source. For example if I am running dev webserver and manually make the changes in the database using phpmyadmin, the running django web application won't get those changes until it is restarted. The same happens if two django based scripts are running as separate processes and make create or modify models; the changes do not get reflected among the processes, however the database does get updated.

I made a small project to replicate this behavior. The zip file is attached. It contains two scripts runnable as separate processes from command line. script1.py checks the number of rows added in the table and script2.py periodically adds random data. The web based application contains two views, one for adding data and one for seeing the last entry. I run this using manage.py runserver.

My environment is as following:

Windows XP SP2 (have also tried on Ubuntu)
MySQL 5.0 (Caching disabled)
Python 2.5

I have spent a lot of time trying to figure out what's going on but all in vain. Any help will be highly appreciated.

Regards,
Sharjeel

[Karen Tracey]

You must be using MySQL InnoDB tables, at any rate that is the only way I could recreate the behavior using your project's scripts.  With a MyISAM table script1.py saw the producer script2.py's updates immediately.  So the problem is related to transactions.

script1.py is running in its own isolated transaction, which is never auto-committed by the underlying Django code since it never does any updates/inserts/deletes (see http://www.djangoproject.com/documentation/transactions/).

script2.py does perform updates, which by default will be auto-committed immediately by Django.  However script1.py's transaction does not see those updates because InnoDB's default transaction isolation level is "repeatable read".  From http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html:

The default level for InnoDB is repeatable read. In this isolation mode, each transaction gets to work in an isolated version of the table where each row remains as it was when the transaction started. Reading a row is guaranteed to be repeatable.

If you want script1.py (using an InnoDB table) to see committed updates from other transactions you can change the transaction isolation level like so:

from django.db import connection
connection.cursor().execute('set transaction isolation level read committed')

Alternatively you can enable the database's version of auto-commit, which "commits" queries as well as updates, so that each new query by script1 will be in its own transaction:

connection.cursor().execute('set autocommit=1')

Either one allows script1 to see script2's updates.

Karen