Thread: idle in transaction
We recently upgraded postgres from 7.3 to 7.4, along with the JDBC jar, and noticed all the backend processes/connections are left in the "idle in transaction" state where before they where left in the "idle" state. Has something changed in the 7.4 jdbc driver vs 7.3 which might be causing this? Note We call setAutoCommit(FALSE) on every connection when created. -- Warren Little Senior Vice President Secondary Marketing Security Lending Wholesale, LC www.securitylending.com Tel: 866-369-7763 Fax: 866-849-8082
Just a very quick thought, have You issued a: connection.commit(); Regards, Søren, Subject: [ADMIN] idle in transaction From: Warren Little <wlittle@securitylending.com> To: pgsql-admin@postgresql.org Date sent: Sun, 15 Feb 2004 09:23:32 -0700 > We recently upgraded postgres from 7.3 to 7.4, along with the JDBC jar, > and noticed all the backend processes/connections are left in the "idle > in transaction" state where before they where left in the "idle" state. > Has something changed in the 7.4 jdbc driver vs 7.3 which might be > causing this? > Note > We call setAutoCommit(FALSE) on every connection when created. > > > > -- > Warren Little > Senior Vice President > Secondary Marketing > Security Lending Wholesale, LC > www.securitylending.com > Tel: 866-369-7763 > Fax: 866-849-8082 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
We see the exact same condition when setting "setAutoCommit(FALSE)". However, we expected that situation based on what we understand as the way such a setting manifests itself within the Multi-Statement-Transaction handling of Postgres. Furthermore, we saw this exact same behavior under at least 7.3.4, if not 7.3.2 and maybe even 7.2.something. So long as the idled transaction isn't holding any locks on any data resources, I don't know if this condition is a bad thing. It would be nice to be able to differentiate between a transaction that has been "declared" but has yet to really begin issuing any statements and take locks from transactions that are idle "mid-transaction". The latter of which spells death for a multi-user, OLTP application. I'll be curious to hear others experience with this. Marc Mitchell Enterprise Information Solutions, Inc. Downers Grove, IL 60515 marcm@eisolution.com -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Warren Little Sent: Sunday, February 15, 2004 10:24 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] idle in transaction We recently upgraded postgres from 7.3 to 7.4, along with the JDBC jar, and noticed all the backend processes/connections are left in the "idle in transaction" state where before they where left in the "idle" state. Has something changed in the 7.4 jdbc driver vs 7.3 which might be causing this? Note We call setAutoCommit(FALSE) on every connection when created. -- Warren Little Senior Vice President Secondary Marketing Security Lending Wholesale, LC www.securitylending.com Tel: 866-369-7763 Fax: 866-849-8082 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
"Marc Mitchell" <marcm@eisolution.com> writes:
> So long as the idled transaction isn't holding any locks on any data
> resources, I don't know if this condition is a bad thing. It would be
> nice to be able to differentiate between a transaction that has been
> "declared" but has yet to really begin issuing any statements and take
> locks from transactions that are idle "mid-transaction".
You guys should probably take this to the pgsql-jdbc list, since that's
where the people who know the innards of the JDBC driver hang out.
I just saw a patch go by on that list that purports to
* Keeps track of the current transaction state.
* Prevents starting a new transaction until actually required.
so that may address your concern. I'm not enough of a JDBC hacker
to be sure though.
regards, tom lane