Why doesn't this loop display an updated object count every five seconds?

Question

I use this python code to output the number of Things every 5 seconds:

def my_count():     
    while True:
        print "Number of Things: %d" % Thing.objects.count()
        time.sleep(5)

my_count() 

If another process generates a new Thing while my_count() is running, my_count() will keep printing the same number, even though it now has changed in the database. (But if I kill my_count() and restart it, it will display the new Thing count.)

Things are stored in a MYSQL innodb database, and this code runs on ubuntu.

Why won't my_count() display the new Thing.objects.count() without being restarted?

Answer 1

Because Python DB API is by default in AUTOCOMMIT=OFF mode, and (at least for MySQLdb) on REPEATABLE READ isolation level. This means that behind the scenes you have an ongoing database transaction (InnoDB is transactional engine) in which the first access to given row (or maybe even table, I'm not sure) fixes "view" of this resource for the remaining part of the transaction.

To prevent this behaviour, you have to 'refresh' current transaction:

  from django.db import transaction


  @transaction.autocommit  
  def my_count():     
      while True:
          transaction.commit()
          print "Number of Things: %d" % Thing.objects.count()
          time.sleep(5)

-- note that the transaction.autocommit decorator is only for entering transaction management mode (this could also be done manually using transaction.enter_transaction_management/leave_transaction_managemen functions).

One more thing - to be aware - Django's autocommit is not the same autocommit you have in database - it's completely independent. But this is out of scope for this question.

Edited on 22/01/2012

Here is a "twin answer" to a similar question.