Thread: IDLE IN TRANSACTION
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!
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/
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
>No problem really, all it means is the application has issued a start of transaction command, it just isnt doing anything at the moment.
>Transactions allow any data added during the applications running to be rolled back should, for example, the application crash itll prevent half-written data remaining >stale in your database.
>My guess is when you Exit the application correctly, itll 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!
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.