Subject: Re: IDLE IN TRANSACTION
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
Cc: andy.shellam@mailnetwork.co.uk, pgsql-admin@postgresql.org
In-Reply-To: <004a01c663e2$4860a5d0$4700a8c0@TREEZANTHUS>
References: <20060419120510.B01C611F60B4@postgresql.org>
	<004a01c663e2$4860a5d0$4700a8c0@TREEZANTHUS>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <1145473339.23538.125.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 19 Apr 2006 14:02:20 -0500

On Wed, 2006-04-19 at 13:51, Rodrigo Sakai wrote:
> >No problem really, all it means is the application has issued a start
> of transaction command, it just isn=E2=80=99t doing anything at the momen=
t.
>=20
>=20
> >Transactions allow any data added during the application=E2=80=99s runni=
ng to
> be rolled back should, for example, the application crash =E2=80=93 it=E2=
=80=99ll
> prevent half-written data remaining >stale in your database.
>=20
> >My guess is when you Exit the application correctly, it=E2=80=99ll issue=
 a
> COMMIT statement to store the transactional data to your database.
>=20
> =20
>=20
>   Ok Andy, but what if I do an UPDATE operation on the database within
> this transaction, it will acquire exclusivelock on that table and will
> release only at the commit (or rollback) of this transaction, is that
> right?? So, if this transaction only finishes with the exit of the
> application the UPDATE operation will cause a lot of locks and
> deadlocks, won't they???

Normally when you see this behaviour, the application or possibly the
jdbc drivers are issuing a commit;begin; pair at the end of each
session.  This is to have the connection "ready to go" for the next
session.  It is a bad idea from the olden days that seems to find its
way back to the present every so often.

Usually there's a way somewhere to turn this behaviour off, whether it's
in your app or your driver.