postgresql_psycopg2 sometimes leaves connections "idle in transaction"
|Reported by:||brodie||Owned by:||aaugustin|
|Component:||Database layer (models, ORM)||Version:||1.3|
|Cc:||adrian, akaariai, aaugustin, tcpip4000, mdupuis, iacobcatalin@…, gerdemb||Triage Stage:||Accepted|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
When using the postgresql_psycopg2 backend with DB-level autocommit disabled, long-running Django processes (e.g., under Gunicorn) can leave around idle connections stuck in transactions, making things like schema migrations difficult to impossible to do without killing those connections.
This happens because by default (with autocommit disabled), psycopg2 issues a BEGIN before the connection's first query is run. If the connection is committed or rolled back, it'll issue another BEGIN before the next query. Unless you commit or roll back the connection, it'll stick around in that transaction indefinitely.
To make matters worse, if the connection is closed while it's in this idle state, it can cause problems with pgbouncer. pgbouncer will see there's a transaction in progress but that the client disconnected. The only way it can recover the connection safely is to disconnect from the server (issuing an "unclean server" error message).
By default, the ORM issues a commit after every INSERT or UPDATE made through the ORM. If the very last thing the Django process does before disconnecting is an INSERT or an UPDATE, it won't run into the pgbouncer "unclean server" problem, and the connection won't be idle in transaction (as long as another query isn't run). But not every request makes a modification to the database as its last query, so most of the time you'll run into this issue.
One workaround is to use the TransactionMiddleware, but that won't help if you're using the ORM outside of the request framework (e.g., in cron jobs, standalone scripts, or Celery tasks).
I'm not sure what a proper fix would look like (or if this even warrants a fix). I think ideally, Django would by default issue a ROLLBACK after every request. It could also issue a ROLLBACK when disconnecting from the database. At very the least, this issue should be documented.
Also note that while this is related to #16047, the fix for that issue won't resolve this one.
Change History (11)
comment:1 Changed 2 years ago by aaugustin
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
- Triage Stage changed from Unreviewed to Accepted
comment:10 Changed 12 months ago by aaugustin
- Owner changed from nobody to aaugustin
- Status changed from new to assigned