52538

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.

Answer1:

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 <strong>why do you want to do this?!</strong> 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. <a href="http://celeryproject.org/" rel="nofollow">Celery</a> is a popular package for doing just that. If response time isn't a major concern, however, I still think successive transactions should suffice.

<h3>Update:</h3>

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 <em>outside</em> the commit_on_success:

<ul><li>

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

</li> <li>

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 <em>inside</em> 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): "..." </li> <li>

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) </li> </ul>

Answer2:

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 <a href="https://docs.djangoproject.com/en/dev/topics/db/transactions/?from=olddocs#django.db.transaction.commit_manually" rel="nofollow">documentation</a>.

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.

Recommend

  • java.lang.RuntimeException: org.hibernate.AnnotationException: No identifier specified for entity
  • when spring boot startup,throw out the “method names must be tokens” exception
  • Openfire chatting unexpected behavior
  • C# Using sql parameters which now won't work
  • Time Complexity of the given nested loops
  • Dynamic programming matrix chain multiplication
  • Entity Framework Code First Circular Dependices
  • Why the time complexity of an array insertion is O(n) and not O(n+1)? [duplicate]
  • Upload a Java and node.js project to Google AppEngine at once
  • Add custom information to HockeyApp crash report
  • updating and compacting sqlite database in android
  • Is it good to have multiple database running in a same project?
  • parameterized queries in oursql
  • MySQL: Difference between `… ADD INDEX(a); … ADD INDEX(b);` and `… ADD INDEX(a,b);`?
  • Alternative to overridePendingTransition() - Android
  • Eloquent update method change created_at timestamp
  • Visual Studio 2010 debugger build correctly - compiler pdb and linker pdb not in synch?
  • How to get Eclipse Oxygen to run on Java 9
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • Jetty Server not starting: Unable to establish loopback connection
  • Body moving without any force applied? (Box2d)
  • does jqgrid support a multiple checkbox list for editing
  • java.lang.NoClassDefFoundError: com.parse.Parse$Configuration$Builder on below Lollipop versions
  • JFileChooser in front of fullscreen Swing application
  • jQuery show() function is not executed in Safari if submit handler returns true
  • Align navbar back button on right side
  • HTML download movie download link
  • Modifying destination and filename of gulp-svg-sprite
  • How to handle AllServersUnavailable Exception
  • How to model a transition system with SPIN
  • VBA Convert delimiter text file to Excel
  • ORA-29908: missing primary invocation for ancillary operator
  • XCode can't find symbols for a specific iOS library/framework project
  • How to disable jQuery.jplayer autoplay?
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • Busy indicator not showing up in wpf window [duplicate]
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?