Re: Can't throw the dreaded 'idle in transaction' - need

On Mon, 22 Nov 2004, Temp02 wrote:

> 2. Is the idle in transaction a "normal" event for all update
> transactions? Should the database release the lock immediately after the
> commit, in all instances? Should we assume that when we see an 'idle in
> transaction' that some error event has occurred that we're not seeing,
> like a failed query etc, that's causing the connection to remain open?

Idle in transaction is a normal state for every transaction that is not
run in autocommit mode. When the first statement is run a transaction is
started. When this statement completes the connection is "idle in
transaction". It stays this way until the next statement is run, such
that the transaction is no longer idle, or the transaction is ended by
either a commit or rollback.

> 3. We've tried to reduce all large transactions and complex queries
> effectively to small transaction 'blocks' or simply leave auto.commit to
> true and issue only single line updates. But this hasn't helped.

Potential problems could be error handling code that bails out leaving
the connection and transaction still in the idle-in-transaction state.

Another problem in a multi-threaded multi-connection application is a
deadlock involving both database and application resources. A deadlock in
the database will be detected and broken, but the database doesn't know
anything about locking code in the application.

Kris Jurka