Thread: IDLE IN TRANSACTION

IDLE IN TRANSACTION

From
"Rodrigo Sakai"
Date:
  Hi,
 
  I have an app developed with Delphi, and when I just start tha app it start a process (a connection) on the db server and the status stay 'IDLE IN TRANSACTION' until the end of the application. The app is developed by other people and I don't know much about how it was programmed! But I want to know if this kind of connection (idle in transaction) can cause locks and deadlocks on the db? And if is there a better way to connect to db?
 
  Thanks in advance!

Re: IDLE IN TRANSACTION

From
"Andy Shellam"
Date:

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.

 


From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Sakai
Sent: 19 April 2006 1:00 pm
To: pgsql-admin@postgresql.org
Subject: [ADMIN] IDLE IN TRANSACTION

 

  Hi,

 

  I have an app developed with Delphi, and when I just start tha app it start a process (a connection) on the db server and the status stay 'IDLE IN TRANSACTION' until the end of the application. The app is developed by other people and I don't know much about how it was programmed! But I want to know if this kind of connection (idle in transaction) can cause locks and deadlocks on the db? And if is there a better way to connect to db?

 

  Thanks in advance!

!DSPAM:14,4446269633691691718838!

Re: IDLE IN TRANSACTION

From
Peter Eisentraut
Date:
Rodrigo Sakai wrote:
> But I want to know if this kind of connection (idle in
> transaction) can cause locks and deadlocks on the db?

Certainly.  Make sure the application is closing the transactions
quickly.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: IDLE IN TRANSACTION

From
"Chandra Sekhar Surapaneni"
Date:
The Idle-in transaction process will prevent VACUUM from removing dead rows. I think you should have them fix the client such that they dont issue a begin unless they need to do something on the database. Also have the client commit as early as it can. It is safe to leave the database connection idle, but dont leave it idle in a transaction.
 
-Chandra Sekhar Surapaneni


From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Sakai
Sent: Wednesday, April 19, 2006 7:00 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] IDLE IN TRANSACTION

  Hi,
 
  I have an app developed with Delphi, and when I just start tha app it start a process (a connection) on the db server and the status stay 'IDLE IN TRANSACTION' until the end of the application. The app is developed by other people and I don't know much about how it was programmed! But I want to know if this kind of connection (idle in transaction) can cause locks and deadlocks on the db? And if is there a better way to connect to db?
 
  Thanks in advance!

Re: IDLE IN TRANSACTION

From
"Rodrigo Sakai"
Date:

>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???

 

  Thanks!

Re: IDLE IN TRANSACTION

From
Scott Marlowe
Date:
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.