Postgresql – SELECT 1 – idle in transaction

postgresql

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.

Best Answer

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

Related Question