Search code examples
node.jspostgresqlpg-promise

What can cause "idle in transaction" for "BEGIN" statements

We have a node.js application that connects via pg-promise to a Postgres 11 server - all processes are running on a single cloud server in docker containers.
Sometimes we hit a situation where the application does not react anymore.

The last time this happened, I had a little time to check the db via pgadmin and it showed that the connections were idle in transaction with statement BEGIN and an exclusive lock of virtualxid
enter image description here enter image description here

I think the situation is like this:

  1. the application has started a transaction by sending the BEGIN sql command to the db
  2. the db got this command and started a new transaction and thus acquired an exclusive lock of mode virtualxid
  3. now the db waits for the application to send the next statement/s (until it receives COMMIT or ROLLBACK) - and then it will release the exclusive lock of mode virtualxid
  4. but for some reason it does not get anymore statements:
    I think that the node.js event-loop is blocked - because at the time, when we see these locks, the node.js application does not log anymore statements. But the webserver still gets requests and reported some upstream timed out requests.

Does this make sense (I'm really not sure about 2. and 3.)?
Why would all transactions block at the beginning? Is this just coincidence or is the displayed SQL maybe wrong?

BTW: In this answer I found, that we can set idle_in_transaction_session_timeout so that these transactions will be released after a timeout - which is great, but I try to understand what's causing this issue.

Solution

  • The transactions are not blocking at all. The database is waiting for the application to send the next statement.

    The lock on the transaction ID is just a technique for transactions to block each other, even if they are not contending for a table lock (for example, if they are waiting for a row lock): each transaction holds an exclusive lock on its own transaction ID, and if it has to wait for a concurrent transaction to complete, it can just request a lock on that transaction's ID (and be blocked).

    If all transactions look like this, then the lock must be somewhere in your application; the database is not involved.

    When looking for processes blocked in the database, look for rows in pg_locks where granted is false.