Some time ago I've reported ticket #13870¹. Here's a brief explanation of the problem:
PostgreSQL has a concept called "isolation levels". These are various types of meta-transactions. 1. There's level 0 which means "off" and results in ghost reads (SELECT returning data not yet commited). 2. The most useful level is 1 which prevents ghost-reading uncommited data yet does not guarantee atomicity (it's possible for two identical SELECTs to return different results). 3. There's also a third "serializable" level that guarantees full atomicity but it's not used by Django. In psycopg(2) the default isolation level is 0 or "no isolation at all". If enabled, a meta-transaction is designed to work as follows: 1. The first query you execute results in an implicit "BEGIN" being called internally by PostgreSQL 2. Any consecutive queries are executed in the same transaction 3. You terminate the meta-transaction by calling connection.commit() or connection.rollback() 4. The first query you execute results in an implicit "BEGIN" being called internally by PostgreSQL 5. Any consecutive queries are executed in the same transaction 6. You terminate the meta-transaction by calling connection.commit() or connection.rollback() ...and so on. The problem is that this is not true for Django. The backend initializes the meta-transaction at connection time and never bothers to terminate it. As the transaction is merely a ghost-read-preventing one, it does not stop data from being saved in the database, but it does result in a parmanently open transaction. This both wastes resources on the server and prevents any other client from touching the database structure (any ALTER or VACUUM command will hang waiting for the remaining transactions to finish). The ticket contains a naïve workaround but as described there, I feel a proper solution would look like this: 1. Introduce IsolationMiddleware that does something like this (pseudocode): class IsolationMiddleware(object): def process_request(self, request): for db in database_connections: db.enter_isolation_block() def process_response(self, request, response): for db in database_connections: db.leave_isolation_block() 2. Make the middleware default and describe it in the migration guide. 3. Introduce no-op enter_isolation_block() and leave_isolation_block() that just "pass" 4. Override both methods in the pgsql backends (pseudocode): def enter_isolation_block(self): if self.isolation_level: self.connection.set_isolation_level(self.isolation_level) def leave_isolation_block(self): if self.isolation_level and self.connection.get_transaction_status() == \ psycopg2.extensions.TRANSACTION_STATUS_INTRANS: if self.commited: self.connection.commit() else: self.connection.rollback() 5. Remove code that sets isolation level in the connection initialization code. ¹ http://code.djangoproject.com/ticket/13870 -- Patryk Zawadzki -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-develop...@googlegroups.com. To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.