Django on MySQL: how to enable auto-sorting?

Question

I have something like the following code that runs in the background:

def run() while True: objs = MyModel.objects.filter(last_updated < time.time() - FREQUENCY) print objs def update() while True: # some processing code mymodel.last_updated = time.time() mymodel.save() 

The above functions are performed in two separate threads: update () updates all models in turn, and run () selects the models that need updating. All this works against MySQL and MyModel lives in an InnoDB table.

The problem is that run () always sees the same value for last_updated. The reason is that it is inside the transaction and selects a consistent snapshot of the data. Naturally, I want him to select the latest data. It works if I do the following:

 def run() from django.db import connection while True: connection.connection.execute('SET autocommit = 1') objs = MyModel.objects.filter(last_updated < time.time() - FREQUENCY) print objs 

But this means that I will execute an additional request each time. Also, if the connection closes between where I set autocommit = 1 and the next choice, it will not work.

Postgres happily supports this: http://docs.djangoproject.com/en/dev/ref/databases/#autocommit-mode (at least according to the docs), but is there a way to enable autocommit for MySQL?

In addition, since this runs as a background process, requests are not processed, and middleware is not involved.

Answer 1

In Django, I listen to the connection_created signal, send the instruction "set autocommit = 1" after the connection is established. It works great!

 from django.db.backends.signals import connection_created from django.dispatch import receiver @receiver(connection_created) def connection_init(sender, connection, **kwargv): connection.cursor().execute("SET autocommit=1") 

Answer 2

The solution I'm using is basically committing at the end of the loop. As far as I can tell, Django just does not support this option, so there is no way to turn off auto-messaging.

Answer 3

You can see the transaction isolation level (Read uncommitted, Read commit, Repeatable read, Serializable).

In addition, you should not configure the connection behind Django ORM, but use special methods for this .