Force Django to commit

Question

Setup:

• Python script A inserts data to a database every 15 minutes
• Python script B queries a few of the latest entries from the database every few minutes

Both use Django's ORM, run on the same machine and use a local MySQL database.

The Problem:
B fetches entries, except for the latest one, even though A saves it minutes before.

I suspected that A doesn't close the transaction, thus B sees the database without the last entry. Indeed when examining the MySQL logs, I noticed the commit for each INSERT happens right before the next INSERT.

Even though it's supposed to be redundant, I added @commit_on_success decorator to the A function that includes the save(), but it did not help.

How can I force Django (or MySQL?!) to commit right after the save()?

UPDATE:
I discovered that the commits DO happen - I was mislead to believe they don't because MySQL's General Query Log only has 1 sec resolution.
In light of this and other new information, I've reasked the question here.

Solution 1

You can use the commit_manually decorator and call it whenever you want.

Straight from the documentation:

from django.db import transaction

@transaction.commit_manually
def viewfunc(request):
    ...
    # You can commit/rollback however and whenever you want
    transaction.commit()
    ...

    # But you've got to remember to do it yourself!
    try:
        ...
    except:
        transaction.rollback()
    else:
        transaction.commit()

This answers the question you asked, though I wonder if there might be something else at work.

NOTE: commit_manually was deprecated in 1.6 and removed in 1.8.

Solution 2

The problem is caused by that MySQL by default has a REPEATABLE-READ transaction isolation level. That means that the same query is expected to return the same values. Changes won't return. You can do two things here:

1. Set transaction isolation level to READ-COMMITTED in the settings. As explained here.
2. Force a commit, thus close the transaction on script B, so as a new transaction starts, you will see all changes before this new transaction. Model.objects.update() does the trick.

Comments

[Jonathan Livni]

How would that be different than @commit_on_success? (switching to manual commit will hurt our uptime, so I'd like to better understand in advance before tempering with the system)

[eric]

commit_on_success only runs the commit after the wrapped function returns; if it's sitting idle without returning it won't help. commit_manually lets you call the commit right after the last save. I'm not sure what your code is doing, so that's all I can think of right now.

[Jonathan Livni]

Although I discovered my problem was elsewhere, I do believe your answer to be significant for those searching for forcing django commits, therefore I marked it as the right answer