Thread: Connection Idle in transaction
Hi all,
I'm facing a problem with the unfamous:
"idle in transaction"
problem. I'm using the JDBC driver.
Mainly the problem is that the JDBC interface doesn't
provide the method begin() for a transaction object,
of course this is not a JDBC postgres interface problem.
Let me explain what happen using the JDBC interface
Client Side | Server Side
---------------------------------------------------
1) Open a connection | Connection accepted
| <- Connection Idle
2) set autocommit false | begin;
| <- Idle in transaction
3) select now(); | select now();
| <- Idle in transaction
4) commit; | commit; begin;
| <- Idle in transaction
5) select now(); | select now();
| <- Idle in transaction
6) rollback; | rollback; begin;
| <- Idle in transaction
as you can easily understand there is no window time larger enough with
a connection idle, I thin that the JDBC behaviour ( with the server I
mean ) is not really correct: if the application is waiting for a user
entry then the connection remain: idle in transaction.
This is the behaviour that I think it's better:
Client Side | Server Side
---------------------------------------------------
1) Open a connection | Connection accepted
| <- Connection Idle
2) set autocommit false | NOP
| <- Connection Idle
3) select now(); | begin; select now();
| <- Idle in transaction
4) commit; | commit;
| <- Connection Idle
5) select now(); | begin; select now();
| <- Idle in transaction
6) select now(); | select now();
| <- Idle in transaction
7) rollback; | rollback;
| <- Connection Idle
AS you can see the JDBC driver must do a begin only before the
first statement.
Am I missing something ?
Regards
Gaetano Mendola
> I'm facing a problem with the unfamous: > > "idle in transaction" Why is this "infamous"? Does it cause problems somewhere? I understand it may "look unusual" to you, but there's really no issue in that each connection that doesn't use autocommit is always in a transaction, so that any statements you issue are done under that transaction until you commit/rollback, at which time a new transaction is initiated in preparation for further statements being issued. Is there some issue that Postgresql has problems because such transactions are held open for a long time? I've never seen any such problem in the years using PG. After all, maintaining the open connection is the expensive operation, and I think the open transaction is a nit (but I certainly can be wrong!). David
David Wall wrote: >>I'm facing a problem with the unfamous: >> >>"idle in transaction" > > > Why is this "infamous"? Does it cause problems somewhere? I understand it > may "look unusual" to you, but there's really no issue in that each > connection that doesn't use autocommit is always in a transaction, so that > any statements you issue are done under that transaction until you > commit/rollback, at which time a new transaction is initiated in preparation > for further statements being issued. > > Is there some issue that Postgresql has problems because such transactions > are held open for a long time? I've never seen any such problem in the > years using PG. After all, maintaining the open connection is the expensive > operation, and I think the open transaction is a nit (but I certainly can be > wrong!). Yes a "idle in transaction" connection for example block each vacuum full, just not to mention all the rows kidnapped by that transaction. Again, for me the actual behave is not the right one. Regards Gaetano Mendola.
Gaetano Mendola wrote: > David Wall wrote: > >>> I'm facing a problem with the unfamous: >>> >>> "idle in transaction" >> >> >> >> Why is this "infamous"? Does it cause problems somewhere? I >> understand it >> may "look unusual" to you, but there's really no issue in that each >> connection that doesn't use autocommit is always in a transaction, so >> that >> any statements you issue are done under that transaction until you >> commit/rollback, at which time a new transaction is initiated in >> preparation >> for further statements being issued. >> >> Is there some issue that Postgresql has problems because such >> transactions >> are held open for a long time? I've never seen any such problem in the >> years using PG. After all, maintaining the open connection is the >> expensive >> operation, and I think the open transaction is a nit (but I certainly >> can be >> wrong!). > > > Yes a "idle in transaction" connection for example block each vacuum > full, just not to mention all the rows kidnapped by that transaction. Not block sorry, but the vacuum is not able to free all the row updated by other connections. try to connect to your DB, do a "begin" and leave that transaction sitting there for days... Regards Gaetano Mendola.
> Not block sorry, but the vacuum is not able to free all the row updated > by other connections. But each connection shouldn't have any updated rows that haven't been committed or rolled back. Now, if you are running some statements against a connection and then never calling commit/rollback, that's another thing since you are actually tying up resources then. But since the driver just does a 'begin' after any given commit/rollback, there shouldn't be anything locked by that connection. David
Gaetano Mendola wrote: > AS you can see the JDBC driver must do a begin only before the > first statement. > > Am I missing something ? This has been something that could be improved for some time now. If we delay the BEGIN we could also deal with transaction isolation changes better. Overall, though, it's a pretty minor thing. Patches are welcome.. -O
A bit off topic: We implemented our own pooling mechanism which when the connection is in the pool we set autocommit(false) thereby setting the state of the connection to "idle" and when the pool hands out the connection we flip it to autocommit(true). This made it much easier to find connections/transactions orphaned by the application code. Just food for thought. On Thu, 2004-04-08 at 19:28, David Wall wrote: > > Not block sorry, but the vacuum is not able to free all the row updated > > by other connections. > > But each connection shouldn't have any updated rows that haven't been > committed or rolled back. Now, if you are running some statements against a > connection and then never calling commit/rollback, that's another thing > since you are actually tying up resources then. But since the driver just > does a 'begin' after any given commit/rollback, there shouldn't be anything > locked by that connection. > > David > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Warren Little Senior Vice President Secondary Marketing Security Lending Wholesale, LC www.securitylending.com Tel: 866-369-7763 Fax: 866-849-8079
David Wall wrote: >>Not block sorry, but the vacuum is not able to free all the row updated >>by other connections. > > > But each connection shouldn't have any updated rows that haven't been > committed or rolled back. Now, if you are running some statements against a > connection and then never calling commit/rollback, that's another thing > since you are actually tying up resources then. But since the driver just > does a 'begin' after any given commit/rollback, there shouldn't be anything > locked by that connection. It's not a lock issue as I understand it. It's that the presence of an open "old" transaction means that for rows that have been updated since that transaction started, VACUUM cannot delete the "old" version of the row. For example: > test=> select * from t; > i | j > ---+--- > 1 | 1 > 2 | 2 > 3 | 3 > (3 rows) > > test=> vacuum full verbose t; > INFO: vacuuming "public.t" > INFO: "t": found 3 removable, 3 nonremovable row versions in 1 pages > DETAIL: 0 dead row versions cannot be removed yet. [...] > test=> begin; > BEGIN On a separate connection: > test=> update t set j=5 where i=3; > UPDATE 1 > test=> vacuum full verbose t; > INFO: vacuuming "public.t" > INFO: "t": found 0 removable, 4 nonremovable row versions in 1 pages > DETAIL: 1 dead row versions cannot be removed yet. [...] Back on the original connection: > test=> commit; > COMMIT > test=> vacuum full verbose t; > INFO: vacuuming "public.t" > INFO: "t": found 1 removable, 3 nonremovable row versions in 1 pages > DETAIL: 0 dead row versions cannot be removed yet. [...] So the open transaction prevents the old version of the row (where i=3 and j=3) from being removed. I have a feeling this is an implementation artifact more than anything -- as it appears that the snapshot to use for a (serializable) transaction is not actually "taken" until the first query in a transaction is executed, so the unremovable row in the above example is never actually needed. But I'm not familiar with how the backend code works so this is mostly guesswork :) -O
> It's not a lock issue as I understand it. It's that the presence of an > open "old" transaction means that for rows that have been updated since > that transaction started, VACUUM cannot delete the "old" version of the row. Why would the "old" transaction have anything locked up if it hadn't done a select, update, insert or delete? And if it did, then why not commit/rollback and leave the connection in a more clean state? Two transactions shouldn't bother one another. I'm not sure what state information is maintained in the JDBC library, but assuming it can maintain state, it shouldn't be hard for it to know whether it's in autocommit mode or not, and if not, then only issue the 'begin' when a statement is first created on the connection, and then reset that state after a commit/rollback. But I'm going to look at my dbs with the vacuum full verbose command and see if there's anything stuck in my system! David
David Wall wrote: >>It's not a lock issue as I understand it. It's that the presence of an >>open "old" transaction means that for rows that have been updated since >>that transaction started, VACUUM cannot delete the "old" version of the > > row. > > > Why would the "old" transaction have anything locked up if it hadn't done a > select, update, insert or delete? Empirically, an open transaction that has run no queries *does* prevent some rows from being vacuumed. I haven't delved into the backend code to see why exactly; asking on -hackers may be a better bet. > I'm not sure what state information is maintained in the JDBC library, but > assuming it can maintain state, it shouldn't be hard for it to know whether > it's in autocommit mode or not, and if not, then only issue the 'begin' when > a statement is first created on the connection, and then reset that state > after a commit/rollback. That's the plan; it just needs implementing. There are also some interactions with changing transaction isolation levels that should be dealt with at the same time. This is somewhere on my todo list and I'll get to it eventually if noone else does, but there's quite a bit of other stuff ahead of it at the moment.. -O
> That's the plan; it just needs implementing. There are also some > interactions with changing transaction isolation levels that should be > dealt with at the same time. > > This is somewhere on my todo list and I'll get to it eventually if noone > else does, but there's quite a bit of other stuff ahead of it at the > moment.. Wow, thanks. Yeah, I agree about priorities. The problem doesn't bother me at all since we've not seen any issues with PG and it's worked like a champ for several years in multiple deployments, including being used in Fortune 500 deployments, by our office edition licensees of Signed & Secured, and to drive our public web service at yozons.com. It's just been awesome, fast and reliable. In fact, when we had to port Oracle, we had to "dumb down" our application a bit because of their odd handling of BLOBs, inability to support multiple TEXT (LONG) fields in a single table and their shorter table and field names. David
On Friday 09 April 2004 3:23 am, David Wall wrote: > Wow, thanks. Yeah, I agree about priorities. The problem doesn't bother > me at all since we've not seen any issues with PG and it's worked like a > champ for several years in multiple deployments, including being used in > Fortune 500 deployments, by our office edition licensees of Signed & > Secured, and to drive our public web service at yozons.com. It's just been > awesome, fast and reliable. In fact, when we had to port Oracle, we had to > "dumb down" our application a bit because of their odd handling of BLOBs, > inability to support multiple TEXT (LONG) fields in a single table and > their shorter table and field names. I am rather keen to develop a service porting people toPG from Oracle. I have always got the impression that this was a non-no due to the PL side of Oracl PL/SQL? What can Oracle do that PG can't ? cheers timp
On Apr 9, 2004, at 4:58 AM, Tim Pizey wrote: > > > What can Oracle do that PG can't ? > Bankrupt you? Make your hair fall out? Cause ulcers? The list is endless.... ;-) > cheers > timp > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
Andrew Rawnsley wrote:
>
> On Apr 9, 2004, at 4:58 AM, Tim Pizey wrote:
>
>>
>>
>> What can Oracle do that PG can't ?
>>
>
> Bankrupt you? Make your hair fall out? Cause ulcers? The list is
> endless....
>
> ;-)
pro: OLAP, recursive queries, clustering, tablespaces, advanced queuing,
bitmap indexing, index organized tables, materialized views, database
links, nested tables, cubes, ... (just to name a few).
contra: bankrupt you, driver your dbas crazy, malloc(size_of(ram) * 1000)
Don't get me wrong. PostgreSQL is a damn good product and it supports
95% of all commonly used features.
However, it is not time yet to tell that we can do EVERYTHING oracle can
do and that Oracle is already completely obsolete. There are still areas
which cannot be covered with PostgreSQL
For a "normal" database PostgreSQL is definitely the better choice but
when working with cubes and so forth it can be damn hard. More work is
needed to make PostgreSQL support these features.
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
Gaetano Mendola wrote: > Gaetano Mendola wrote: >> Yes a "idle in transaction" connection for example block each vacuum >> full, just not to mention all the rows kidnapped by that transaction. > > > Not block sorry, but the vacuum is not able to free all the row updated > by other connections. > I agree that your suggested change in behavior is good (please feel free to submit a patch), it isn't necessarily going to solve your problem. > try to connect to your DB, do a "begin" and leave that transaction > sitting there for days... The problem here is that your application would allow a connection to the database to sit around for days. Even with your suggested changes to the driver, if a jdbc connection did a select (which would start a transaction) and then left that connection sit arround for days you would still have the same problem. Your suggested change to the driver just delays the point where a connection starts, but if you allow long lived connections to the database I contend that you will still see your problem since some code somewhere will simply issue a select and then hold an open idle connection for long periods of time. --Barry
Barry Lind wrote:
> > try to connect to your DB, do a "begin" and leave that transaction
> > sitting there for days...
>
> The problem here is that your application would allow a connection to
> the database to sit around for days. Even with your suggested changes
> to the driver, if a jdbc connection did a select (which would start a
> transaction) and then left that connection sit arround for days you
> would still have the same problem. Your suggested change to the driver
> just delays the point where a connection starts, but if you allow long
> lived connections to the database I contend that you will still see your
> problem since some code somewhere will simply issue a select and then
> hold an open idle connection for long periods of time.
If you're pooling connections, while it makes sense for the pool to
commit/rollback any transaction in progress when the connection is
released by a client, it's not obvious that the pool should immediately
reset the autocommit state of the connection. But the current driver
requires setting autocommit on idle connections to avoid keeping a
transaction open.
So you can have:
get connection from pool
conn.setAutoCommit(true);
conn.setAutoCommit(false); -> begin
stmt.executeUpdate("foo"); -> foo
commit() -> commit;begin
return connection to pool
rollback() -> rollback;begin
connection is idle in the pool for the next week.
get connection from pool
conn.setAutoCommit(true); -> commit
// client uses connection
This seems to be the main case where the change in behaviour is useful.
-O
Yes, this sucks - I've run into this as well. This doesn't happen when
autocommit is set to true.
In my application, connections come from a connection pool that I
control, so I set autocommit to true when adding or returning
connections to the pool, and set it to false when retrieving connections
from the pool. The connections that are sitting idle in the pool aren't
actually "idle in transaction".
The real problem (for us, at least) was that connections that are idle
in transaction effectively hang on to old transaction IDs, so a
connection that's not used for several days can basically block vacuums
from vacuuming old rows. Once we changed our pool to have the above
behavior, these problems went away.
mike
-----Original Message-----
From: Gaetano Mendola [mailto:mendola@bigfoot.com]
Sent: Thursday, April 08, 2004 3:21 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Connection Idle in transaction
Hi all,
I'm facing a problem with the unfamous:
"idle in transaction"
problem. I'm using the JDBC driver.
Mainly the problem is that the JDBC interface doesn't
provide the method begin() for a transaction object,
of course this is not a JDBC postgres interface problem.
Let me explain what happen using the JDBC interface
Client Side | Server Side
---------------------------------------------------
1) Open a connection | Connection accepted
| <- Connection Idle
2) set autocommit false | begin;
| <- Idle in transaction
3) select now(); | select now();
| <- Idle in transaction
4) commit; | commit; begin;
| <- Idle in transaction
5) select now(); | select now();
| <- Idle in transaction
6) rollback; | rollback; begin;
| <- Idle in transaction
as you can easily understand there is no window time larger enough with
a connection idle, I thin that the JDBC behaviour ( with the server I
mean ) is not really correct: if the application is waiting for a user
entry then the connection remain: idle in transaction.
This is the behaviour that I think it's better:
Client Side | Server Side
---------------------------------------------------
1) Open a connection | Connection accepted
| <- Connection Idle
2) set autocommit false | NOP
| <- Connection Idle
3) select now(); | begin; select now();
| <- Idle in transaction
4) commit; | commit;
| <- Connection Idle
5) select now(); | begin; select now();
| <- Idle in transaction
6) select now(); | select now();
| <- Idle in transaction
7) rollback; | rollback;
| <- Connection Idle
AS you can see the JDBC driver must do a begin only before the first
statement.
Am I missing something ?
Regards
Gaetano Mendola
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> In my application, connections come from a connection pool that I > control, so I set autocommit to true when adding or returning > connections to the pool, and set it to false when retrieving connections > from the pool. The connections that are sitting idle in the pool aren't > actually "idle in transaction". While this may work, it seems rather expensive since I believe turning on and off the autocommit feature actually sends a message from the JDBC driver to the backend server. It's probably not a big deal, but it seems like two more roundtrips to the DB each time a connection is used is a bit much. David
On Thu, 8 Apr 2004, Gaetano Mendola wrote: > Hi all, > I'm facing a problem with the unfamous: > > "idle in transaction" > I have put in transaction state tracking to the cvs version of the driver. If you could give this some testing I'd appreciate it. Binaries are available here: http://www.ejurka.com/pgsql/jars/ Kris Jurka