Thread: in transaction

in transaction

From
Junaili Lie
Date:
Hi,
We have applications that sits on top of  Java application server. Our
code is written in Java, sitting on top of Jboss with Hibernate, and
we use JDBC driver pg74.215.jdbc2ee.jar. We have observed a number of
<IDLE> in transaction on pg_stat_activity.
I am wondering if there's command/view/system tables/ tools out there
that will allow us to look at what's the in transactions that are
waiting to be committed.
We have some "<IDLE> in transaction" and would like to see which part
of the code that causes it.


Thanks in advance.

J

Re: in transaction

From
elein@varlena.com (elein)
Date:
I am also seeing this situation using hibernate.

Some of the IDLE-in-transaction connections are sitting
there holding locks which is a BIG problem.

The query I use to see the processes and locks is this:

   select procpid, usename , (now() - query_start) as age,
      c.relname , l.mode, l.granted
   from pg_stat_activity a LEFT OUTER JOIN pg_locks l ON (a.procpid = l.pid)
   LEFT OUTER JOIN pg_class c ON (l.relation = c.oid)
   where (current_query = '<IDLE> in transaction'
   or current_query like '%vacuum%')
   -- and query_start < now() - '1 hours'::interval
   order by pid;

Note the commented out part.  Change the interval to what you like.
You cannot see a query because there is none.

Some of these I-i-t connections come and go after a while.
Some stick around for DAYS.

If ANYONE has any brilliant ideas as to the source and
dare I say correction to this problem, many people, especially
myself would be very very happy.

--elein
--------------------------------------------------------------
elein@varlena.com        Varlena, LLC        www.varlena.com
(510)655-2584(o)                             (510)543-6079(c)

          PostgreSQL Consulting, Support & Training

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
--------------------------------------------------------------
AIM: varlenallc          Yahoo: AElein       Skype: varlenallc
--------------------------------------------------------------
I have always depended on the [QA] of strangers.



On Thu, Aug 18, 2005 at 04:16:27PM -0700, Junaili Lie wrote:
> Hi,
> We have applications that sits on top of  Java application server. Our
> code is written in Java, sitting on top of Jboss with Hibernate, and
> we use JDBC driver pg74.215.jdbc2ee.jar. We have observed a number of
> <IDLE> in transaction on pg_stat_activity.
> I am wondering if there's command/view/system tables/ tools out there
> that will allow us to look at what's the in transactions that are
> waiting to be committed.
> We have some "<IDLE> in transaction" and would like to see which part
> of the code that causes it.
>
>
> Thanks in advance.
>
> J
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Re: in transaction

From
Kris Jurka
Date:

On Thu, 18 Aug 2005, Junaili Lie wrote:

> We have applications that sits on top of  Java application server. Our
> code is written in Java, sitting on top of Jboss with Hibernate, and
> we use JDBC driver pg74.215.jdbc2ee.jar. We have observed a number of
> <IDLE> in transaction on pg_stat_activity.

This is an issue with the 7.4 JDBC driver.  It issues BEGIN statements
prior to any commands being issued, so the connection is always in an open
transaction even if it hasn't done anything.  The 8.0 driver (which may be
used against a 7.4 server) should fix this issue.

Elein, who replied as well, has been seeing another issue where the idle
transaction holds some locks blocking other connections.  This points to a
problem in either hibernate or the application code, but the first step to
solving it is definitely moving away from the 7.4 driver.

Kris Jurka


Re: in transaction

From
elein@varlena.com (elein)
Date:
I'm cross posting to INTERFACES.  Please follow up
on INTERFACES and not on general.  Cross posting is evil.

--elein

On Thu, Aug 18, 2005 at 05:02:18PM -0700, elein wrote:
> I am also seeing this situation using hibernate.
>
> Some of the IDLE-in-transaction connections are sitting
> there holding locks which is a BIG problem.
>
> The query I use to see the processes and locks is this:
>
>    select procpid, usename , (now() - query_start) as age,
>       c.relname , l.mode, l.granted
>    from pg_stat_activity a LEFT OUTER JOIN pg_locks l ON (a.procpid = l.pid)
>    LEFT OUTER JOIN pg_class c ON (l.relation = c.oid)
>    where (current_query = '<IDLE> in transaction'
>    or current_query like '%vacuum%')
>    -- and query_start < now() - '1 hours'::interval
>    order by pid;
>
> Note the commented out part.  Change the interval to what you like.
> You cannot see a query because there is none.
>
> Some of these I-i-t connections come and go after a while.
> Some stick around for DAYS.
>
> If ANYONE has any brilliant ideas as to the source and
> dare I say correction to this problem, many people, especially
> myself would be very very happy.
>
> --elein
> --------------------------------------------------------------
> elein@varlena.com        Varlena, LLC        www.varlena.com
> (510)655-2584(o)                             (510)543-6079(c)
>
>           PostgreSQL Consulting, Support & Training
>
> PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
> --------------------------------------------------------------
> AIM: varlenallc          Yahoo: AElein       Skype: varlenallc
> --------------------------------------------------------------
> I have always depended on the [QA] of strangers.
>
>
>
> On Thu, Aug 18, 2005 at 04:16:27PM -0700, Junaili Lie wrote:
> > Hi,
> > We have applications that sits on top of  Java application server. Our
> > code is written in Java, sitting on top of Jboss with Hibernate, and
> > we use JDBC driver pg74.215.jdbc2ee.jar. We have observed a number of
> > <IDLE> in transaction on pg_stat_activity.
> > I am wondering if there's command/view/system tables/ tools out there
> > that will allow us to look at what's the in transactions that are
> > waiting to be committed.
> > We have some "<IDLE> in transaction" and would like to see which part
> > of the code that causes it.
> >
> >
> > Thanks in advance.
> >
> > J
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

Re: in transaction

From
Alvaro Herrera
Date:
On Fri, Aug 19, 2005 at 11:55:19AM -0700, elein wrote:
> I'm cross posting to INTERFACES.  Please follow up
> on INTERFACES and not on general.  Cross posting is evil.

Well, I'm not on -interfaces, so I'll reply to both :-)  I don't worry
too much about crossposting, because a) it's commonplace in PostgreSQL
lists, and b) majordomo can deliver a single copy of the message if you
configure it to do so.


> > Some of these I-i-t connections come and go after a while.
> > Some stick around for DAYS.
> >
> > If ANYONE has any brilliant ideas as to the source and
> > dare I say correction to this problem, many people, especially
> > myself would be very very happy.

While this is a purely client-side problem, which is the client issuing
a BEGIN right after a COMMIT, we talked about coding around it
server-side, back in the time when I was doing nested transactions.
It didn't get done though.  I think if you push hard enough, somebody
(myself?) may do it for 8.2.

Of course, this is no solution if the client started a transaction, did
some work, and then sat on the connection with the transaction open for
days.  But this is not a common case and is certainly much more broken,
if only because other RDBMS behave more reasonably in the COMMIT-BEGIN
scenario.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La persona que no quería pecar / estaba obligada a sentarse
 en duras y empinadas sillas    / desprovistas, por cierto
 de blandos atenuantes"                          (Patricio Vogel)

Re: in transaction

From
elein@varlena.com (elein)
Date:
On Fri, Aug 19, 2005 at 07:06:02PM -0400, Alvaro Herrera wrote:
> On Fri, Aug 19, 2005 at 11:55:19AM -0700, elein wrote:
> > I'm cross posting to INTERFACES.  Please follow up
> > on INTERFACES and not on general.  Cross posting is evil.
>
> Well, I'm not on -interfaces, so I'll reply to both :-)  I don't worry
> too much about crossposting, because a) it's commonplace in PostgreSQL
> lists, and b) majordomo can deliver a single copy of the message if you
> configure it to do so.
>
>
> > > Some of these I-i-t connections come and go after a while.
> > > Some stick around for DAYS.
> > >
> > > If ANYONE has any brilliant ideas as to the source and
> > > dare I say correction to this problem, many people, especially
> > > myself would be very very happy.
>
> While this is a purely client-side problem, which is the client issuing
> a BEGIN right after a COMMIT, we talked about coding around it
> server-side, back in the time when I was doing nested transactions.
> It didn't get done though.  I think if you push hard enough, somebody
> (myself?) may do it for 8.2.

To replicate the situation is psql:
    BEGIN;
    select something;

In another window I see that I have not only shared access locks
but an exclusive access lock.  I do not understand why the exclusive
lock is there.  Am I seeing ghosts?

Also, for some relief we found a piece of code that forgot its
commit.  That helped a lot but I'm not convinced it was the only place
this occurred.

--elein

>
> Of course, this is no solution if the client started a transaction, did
> some work, and then sat on the connection with the transaction open for
> days.  But this is not a common case and is certainly much more broken,
> if only because other RDBMS behave more reasonably in the COMMIT-BEGIN
> scenario.
>
> --
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
> "La persona que no quería pecar / estaba obligada a sentarse
>  en duras y empinadas sillas    / desprovistas, por cierto
>  de blandos atenuantes"                          (Patricio Vogel)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: in transaction

From
Alvaro Herrera
Date:
On Fri, Aug 19, 2005 at 06:01:40PM -0700, elein wrote:

> To replicate the situation in psql:
>     BEGIN;
>     select something;
>
> In another window I see that I have not only shared access locks
> but an exclusive access lock.  I do not understand why the exclusive
> lock is there.  Am I seeing ghosts?

No, it's the lock on the transaction's own TransactionId.  It's harmless
until the transaction updates a tuple that some other transaction later
wants to update too.  You should be able to see it too if you don't
issue the SELECT.  The point of the modification I was indicating is to
delay the acquisition of such a lock until the transaction actually does
something, like that SELECT.  (So if the client does exactly what you
did, it wouldn't be solved by my proposed change.)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"In a specialized industrial society, it would be a disaster
to have kids running around loose." (Paul Graham)