Do cursors in Django run inside the open transaction?

Question

My Django application is using some custom SQL which I am executing inside a view like this:

db = router.db_for_write(model)
cursor = connections[db].cursor()
cursor.execute("INSERT INTO ....")

Since I am using the TransactionMiddleware, my view is running inside a transaction, but I'm not clear if getting a new cursor like this "escapes" the currently open transaction or if the cursor is still a part of the open transaction. I am getting some error messages that lead me to believe that cursor is running inside the transaction.

I would like to be able to use a cursor to execute SQL commands outside of the transaction that was opened by the TransactionMiddleware. Is this possible?

If it matters, I am running Django 1.4 with a PostgreSQL 8.4 database.

Answer 1

I believe you'd need a separate db connection to get a separate, simultaneous transaction. I am also pretty sure django manages only one connection per database. But you could create another one. There might be some good reason not to do this. Complexity comes to mind.

I think something like this would work:

from django.conf import settings
from django.db.utils import ConnectionHandler

def my_view(request):
    """Flirt with complexity by using two connections to db"""
    private_connections = ConnectionHandler(settings.DATABASES)
    db = router.db_for_write(model)
    new_conn = private_connections[db]
    new_conn.enter_transaction_management()
    new_conn.managed(True)
    new_cur = new_conn.cursor()
    new_cur.execute("INSERT INTO ...")
    new_conn.commit()
    new_conn.close()

Note that you can't use django.db.transaction because it operates on the global connection instances in django.db.connections, but in any case, that is just a thin wrapper around the transaction management methods on the connection object.

I guess the real question is why do you want to do this?! And what is wrong with Lakshman Prasad's answer? You can commit/rollback whenever you want, so there is nothing preventing you from performing different tasks in distinct transactions within a single view. The fact that the transactions must be parallel and not successive hints at some logical connection between them, which to my mind would indicate that they should really be in the same transaction.

If, on the other hand, you're just trying to emulate some sort of offline processing, the success or failure of which isn't particularly relevant to the view at all, consider setting up a message queue and performing these inserts in a separate process. Celery is a popular package for doing just that. If response time isn't a major concern, however, I still think successive transactions should suffice.

Update:

If you want your database-backed cache to operate in autocommit mode while still running your business logic in a single (separate) transaction, there's a django way. All you need to do is make sure that the caching occurs outside the commit_on_success:

• If you're just using the caching middleware, make sure it's outside the TransactionMiddleware.

• If you use caching view decorators, I'd venture to guess that you could disable TransactionMiddleware (or put the problem view inside an autocommit decorator) and use the commit_on_success decorator inside the caching decorator. It looks funny, but I don't know why it wouldn't work:

  @transaction.autocommit
  @cache_page(500)
  @transaction.commit_on_success
  def my_view(request):
      "..."
  

• If you use template caching or do more involved manual caching, you could also disable TransactionMiddleware (or put the problem view inside an autocommit decorator) and use commit_on_success as a context manager to put only the code you need in a managed transaction, leaving the rest of the view in autocommit.

  @transaction.autocommit
  def my_view(request):
      data = cache.get(some_key)
      with transaction.commit_on_success():
          context = do_some_processing(data)
      cache.set(some_key, context['data'])
      return render('template/with/cache/blocks.html', context=context)
  

Answer 2

If there is a view in which you want to manage the transaction manually, you should use the decorator in that view to commit_manually.

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()

@transaction.commit_manually(using="my_other_database")
def viewfunc2(request):
    ....

And yes, importing a transaction cursor only provides the the cursor of the transaction, and does not create a new transaction.