Re: IDLE IN TRANSACTION

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’t doing anything at the moment.
>
>
> >Transactions allow any data added during the application’s running to
> be rolled back should, for example, the application crash – it’ll
> prevent half-written data remaining >stale in your database.
>
> >My guess is when you Exit the application correctly, it’ll issue a
> COMMIT statement to store the transactional data to your database.
>
>
>
> 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.