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.

Reply via email to