0

While running PostgreSQL 13.12 (occurs in several versions of PG11/PG13) using SQLAlchemy 1.3, we are occasionally hitting issues where increased concurrency leaves certain transactions (and their nested transactions) in the "Idle in Transaction" state with the RELEASE SAVEPOINT ... query.

Looking at the currently running queries, it is not clear why transactions have stopped moving forward. I have also observed this behavior without any hanging locks:

pid datname duration query state application_name wait_event_type
27662 app 22:22:37.429569 select pg_advisory_xact_lock(resource_id) from resource where uuid = '018afcac-a5ab-7a5c-9eb4-2d8b0be4b556' active http.api.1 Lock
25830 app 22:22:29.236398 RELEASE SAVEPOINT sa_savepoint_5 idle in transaction http.api.0 Client
21490 app 22:22:29.015862 select pg_advisory_xact_lock(resource_id)
from resource
where uuid = '018afcac-a5ab-7a5c-9eb4-2d8b0be4b556'		 active http.api.0 Lock
27674 app 22:22:27.780581 RELEASE SAVEPOINT sa_savepoint_3 idle in transaction http.api.2 Client
29120 app 22:22:26.053851 select pg_advisory_xact_lock(resource_id)
from resource
where uuid = '018afcac-a5ab-7a5c-9eb4-2d8b0be4b556'		 active http.api.2 Lock

Any way to debug this? This API call generally works, with the session being cleaned up and does not fail consistently. We are using SQL Alchemy's connection pooling to manage the connections - closed sessions will return the connection to the pool and issue a rollback to clean up the connection (when commit should have committed all other statements).

Improve this question

1 Answer 1

Reset to default
1

The sessions are in the state idle in transaction because you didn't run COMMIT or ROLLBACK, and RELEASE SAVEPOINT does not close a transaction (only a subransaction). The bug is in your application, which apparently forgot to close the transaction. The state idle in transaction does not mean that PostgreSQL is busy: it is waiting for the next statement from your application.

Improve this answer
6
  • The strange thing is - all 3 Python web application processes (Tornado) still appear to be in the active transaction. Spying on the process they are all stopped within their respective functions close to the mentioned locks. When checking the blocking locks, it looks like RELEASE SAVEPOINT is the blocking process. When simulating a single user, no connections are left in a idle in transaction state. Wondering if SQL Alchemy or PostgreSQL is getting tripped up by nested transactions.
    – Justin Lowen
    Commented Oct 9, 2023 at 14:15
  • 1
    Yes, they are in an active transaction - because your program didn't close the transaction. This is unrelated to the savepoint.
    – Laurenz Albe
    Commented Oct 9, 2023 at 14:45
  • Would the failure to commit/rollback be near the immediate statement that would follow RELEASE SAVEPOINT? These are savepoints generated by the ORM, and I believe the outermost nested transaction would be SAVEPOINT sa_savepoint_1 --- it looks like more lines of code should have executed within the transaction before commit. Looking at the processes, I believe there are more active transactions then there are processes (5 active queries, 3 synchronous python processes - possibly 2 transactions that failed to close properly).
    – Justin Lowen
    Commented Oct 9, 2023 at 14:54
  • 1
    No, a savepoint is never the outermost transaction. Perhaps it is the fault of the ORM, perhaps it is your code, but it sure ain't the database.
    – Laurenz Albe
    Commented Oct 9, 2023 at 19:10
  • Wanted to followup. I finally was able to trace the transaction issues. There were a few points where our nested transactions failed to rollback. One issue was that the savepoint wrapped function execution was accidentally moved out of its try: except: block in Python. A second issue was that a database driver error (psycopg2) was bubbling up to SQL Alchemy. We weren't catching this particular error and it blocked the savepoint rollback (could no longer issue queries for session/agent). Using a context manager helped release or rollback of a savepoint in Python / SQL Alchemy.
    – Justin Lowen
    Commented Mar 11 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.