8

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.

gerdemb
  • 11,275
  • 17
  • 65
  • 73

2 Answers2

3

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)
    
Aryeh Leib Taurog
  • 5,370
  • 1
  • 42
  • 49
  • Thanks for your help. After looking through the Django source code, I reached the same conclusion that I would need to create another database connection. I am using the Django cache with a database backend and trying to track down an intermittent problem that I think is caused by simultaneous UPDATEs to the cache from different transactions. Since the cache doesn't need to be consistant inside a transaction I want to move the cache transactions to autocommit. – gerdemb Sep 11 '12 at 17:21
  • Okay. I'm still not sure I quite understand how or why this helps, which makes me think this is still not quite what you really want, but I've updated my answer with easier ways to keep the cache queries out of the business logic transaction. – Aryeh Leib Taurog Sep 11 '12 at 18:23
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.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
lprsd
  • 84,407
  • 47
  • 135
  • 168
  • Thank you. What I want to do is continue running my view inside a transaction, but also open up a separate connection to the database where I can execute SQL outside of the view's transaction. Is this possible? If I decorate a function inside my view with @transaction.commit_manually or autocommit will that work? – gerdemb Jul 19 '12 at 07:06
  • The question seems to be whether you can open a connection that isn't in the current transaction. Your answer is what you can do with the current transaction. – freyley Mar 07 '13 at 18:05