7

Across my problems with the load on the database, I just have found a new, odd one. I understand, that some queries might be in IDLE IN TRANSACTION status, when I did something wrong in my application layer. However, just now I have spotted this query having this status for almost 20 seconds now:

SELECT 1;

This is the internal mechanism of JBoss AS, checking if the connection is still valid. I cannot understand how it be idle in transaction. Any hints welcome.

Improve this question
3
  • A transaction was started beforehand (maybe automatically, as it's done by default by some clients) and never committed. The query you see is the last executed one, it can be basically anything in an idle transaction. Commented Oct 22, 2015 at 20:30
  • I still do not understand how pure SELECT 1 can start a transaction. Commented Oct 22, 2015 at 21:04
  • 1
    Like dezso said: SELECT 1 just happens to be the last query executed in a transaction that was started with a manual BEGIN (not in autocommit mode), either by you or by your client. The transaction is idle, not the query. It was neither committed nor rolled back and waits for more commands. Commented Oct 23, 2015 at 1:22

2 Answers 2

Reset to default
7

idle in transaction means pretty much what it suggests: there is an open transaction doing nothing at the moment.

What you see can be easily reproduced. Do the following in one session (disable autocommit in your client if necessary - usually it isn't, an explicit BEGIN; will do that automatically):

test=> BEGIN; SELECT 1;

In an other session, let's see how it looks in pg_stat_activity:

test=> SELECT pid, datname, usename, xact_start, query_start, state, state_change, query, now() FROM pg_stat_activity; pid │ 13639 datname │ test usename │ dezso xact_start │ 2015-10-23 10:14:36.853529+02 query_start │ 2015-10-23 10:15:15.838252+02 state │ idle in transaction state_change │ 2015-10-23 10:15:15.838365+02 query │ SELECT 1 now │ 2015-10-23 10:16:24.340721+02

Here we see four different timestamps:

  • the time when the transaction started (xact_start),
  • the time when the query started (query_start),
  • the time when the transaction state last changed (state_change, I suppose it's the end of the query) and
  • the current timestamp (when the above query was run).

Between the last two the transaction is idling, but the last query is still shown.

To answer your question: many clients have autocommit disabled by default. In this case, a transaction is started silently when the first command (SELECT 1;, for example) is issued - and then never closed. In this case you'll see what you experience currently and I reproduced above.

AFAIK there is a way to persuade JBoss AS to turn it off. This might help: https://developer.jboss.org/wiki/Non-transactionaldataaccessandtheauto-commitmode

Improve this answer
1
  • Sometimes, my applications get this problem 'open but never closed', there are many reasons on clients making that problem. I'm using a script to monitor idle in transaction state from clients, then manually terminate those clients process. Commented Dec 20, 2016 at 5:06
0

actually the reason for this was memory leak in application. Garbage collector did 'stop the world' collection, which freezes all threads of JBoss, that is why. But thanks for your help anyway, good to read about idle in transaction.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.