Communicating with the outside world from within an atomic database transaction
Question: I am implementing an import tool (Django 1.6) that takes a potentially very
large CSV file, validates it and depending on user confirmation imports it or
not. Given the potential large filesize, the processing of the file is done
via flowy (a python wrapper over Amazon's SWF). Each import job is saved in a
table in the DB and the workflow, which is quite simple and consists of only
one activity, basically calls a method that runs the import and saves all
necessary information about the processing of the file in the job's record in
the database.

The tricky thing is: We now have to make this import atomic. Either all
records are saved or none. But one of the things saved in the import table is
the progress of the import, which is calculated based on the position of the
file reader:

    
    
    progress = (raw_data.tell() * 100.0) / filesize
    

And this progress is used by an AJAX progressbar widget in the client side. So
simply adding `@transaction.atomic` to the method that loops through the file
and imports the rows is not a solution, because the progress will only be
saved on commit.

The CSV files only contain one type of record and affect a single table. If I
could somehow do a transaction only on this table, leaving the job table free
for me to update the progress column, it would be ideal. But from what I've
found so far it seems impossible. The only solution I could think of so far is
opening a new thread and a new database connection inside it every time I need
to update the progress. But I keep wondering… will this even work? Isn't there
a simpler solution?


Answer: One simple approach would be to use the `READ UNCOMMITTED` transaction
isolation level. That _could_ allow dirty reads, which would allow your other
processes to see the progress even though the transaction hasn't been
committed. However, whether this works or not will be database-dependent. (I'm
not familiar with MySQL, but this wouldn't work in PostgreSQL because `READ
UNCOMMITTED` works the same way as `READ COMMITTED`.)

Regarding your proposed solution, you don't necessarily need a new thread, you
really just need a fresh connection to the database. One way to do that in
Django might be to take advantage of the [multiple database
support](https://docs.djangoproject.com/en/dev/topics/db/multi-db/). I'm
imagining something like this:

  1. As described in the documentation, add a new entry to `DATABASES` with a different name, but the same setup as `default`. From Django's perspective we are using multiple databases, even though we in fact just want to get multiple connections to the same database.

  2. When it's time to update the progress, do something like:
    
        JobData.objects.using('second_db').filter(id=5).update(progress=0.5)
    

That should take place in its own autocommitted transaction, allowing the
progress to be seen by your web server.

Now, does this work? I honestly don't know, I've never tried anything like it!

