Thread: in transaction
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
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
>
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
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 >
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)
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
>
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)