Skip site navigation (1) Skip section navigation (2)

"Idle in Transaction" revisited.

From: "John R Pierce" <pierce(at)hogranch(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: "Idle in Transaction" revisited.
Date: 2004-09-17 23:30:47
Message-ID: 0dfb01c49d0e$5c61c1f0$0200a8c0@hogranch.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Our applications are factory floor messaging systems, mostly in Java, that 
have persistence measured in weeks and even months, they run 24/7.  This 
isn't WebApplet stuff, nor is it JavaBean stuff, its simple java programs 
running on servers doing socket messaging and such.

Recently, I've steered the Java developers in my group towards Postgres on 
Linux for specific applications rather than the $$$$ Sun/Oracle platforms my 
department has traditionally used...

We're using locally-compiled pgsql 7.4.5 with the latest released jdbc, java 
1.4.x on linux 2.4.x (typically redhat enterprise 2.1 or 3).

We've been running into a *lot* of problems with "Idle in Transaction" 
threads causing long term blockage of VACUUM's ability to free up dead rows. 
Our applications tend to be heavily multithreaded, often using a dozen or 
more SQL connections for various purposes.

It took me quite a bit of detective work, with some assistance from the 
pgsql-bugs list, and the #postgresql folks on freenode to get to the bottom 
of this Idle in Transaction thing causing a problem when a JDBC thread was 
doing nothing but periodic SELECT * FROM event_master;  without any commits 
(and auto_commit=off).    Sorted that case out, but now are running into yet 
more related issues...

One case in point, as a SQL form of 'mutex', one of these developers was 
used to doing a 'UPDATE' on a particular row of a table without ever 
committing to leave a row lock in place so that other threads won't attempt 
to 'subscribe' to the same 'topic' on a corporate intranet messaging system. 
We can't do this in Postgres (works great in Oracle) due to the 'idle in 
transaction' issue...  as other threads and programs and databases on this 
system are doing lots and LOTS of updates, we need hourly vacuuming or some 
of our tables really slow down (one thread was doing around 60-90 updates 
per second of the same 20 or so rows in a table to maintain highwater marks 
to prevent duplicate record processing in cases of aborts, failures, and 
abrupt restarts).


I've scanned and searched the messages on this forum, and only found a few 
threads from back in April dealing with this sort of thing.

I'm curious what common practice is for threads that do nothing but 
SELECTS... do folks just enable auto_commit, thereby preventing pgJDBC from 
doing BEGIN; ?  Do they lace their code with COMMIT() calls?

What about a resource locking table like I described above, where we've got 
a few dozen arbitrarily named resources we need to gain a mutex on?   When I 
suggested 'select for update' to see if the resource was available, then 
'update' to mark it in use, and commit, the developer said that leaves the 
resource locked if the app crashes or the box is rebooted or whatever, while 
his row lock would be cleared automatically.


Responses

pgsql-jdbc by date

Next:From: Tom LaneDate: 2004-09-17 23:40:31
Subject: Re: "Idle in Transaction" revisited.
Previous:From: Tom LaneDate: 2004-09-17 23:02:57
Subject: Re: Fetching rows from a cursor returned by a stored function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group