pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedidle in transaction
13+ messages / 12 participants
[nested] [flat]
* idle in transaction
@ 2004-02-15 16:23 Warren Little <wlittle@securitylending.com>
0 siblings, 2 replies; 13+ messages in thread
From: Warren Little @ 2004-02-15 16:23 UTC (permalink / raw)
To: pgsql-admin
We recently upgraded postgres from 7.3 to 7.4, along with the JDBC jar,
and noticed all the backend processes/connections are left in the "idle
in transaction" state where before they where left in the "idle" state.
Has something changed in the 7.4 jdbc driver vs 7.3 which might be
causing this?
Note
We call setAutoCommit(FALSE) on every connection when created.
--
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8082
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: idle in transaction
@ 2004-02-15 16:53 Soeren Laursen <sl@scrooge.dk>
parent: Warren Little <wlittle@securitylending.com>
1 sibling, 0 replies; 13+ messages in thread
From: Soeren Laursen @ 2004-02-15 16:53 UTC (permalink / raw)
To: Warren Little <wlittle@securitylending.com>; +Cc: pgsql-admin
Just a very quick thought, have You issued a:
connection.commit();
Regards,
Søren,
Subject: [ADMIN] idle in transaction
From: Warren Little <wlittle@securitylending.com>
To: pgsql-admin@postgresql.org
Date sent: Sun, 15 Feb 2004 09:23:32 -0700
> We recently upgraded postgres from 7.3 to 7.4, along with the JDBC jar,
> and noticed all the backend processes/connections are left in the "idle
> in transaction" state where before they where left in the "idle" state.
> Has something changed in the 7.4 jdbc driver vs 7.3 which might be
> causing this?
> Note
> We call setAutoCommit(FALSE) on every connection when created.
>
>
>
> --
> Warren Little
> Senior Vice President
> Secondary Marketing
> Security Lending Wholesale, LC
> www.securitylending.com
> Tel: 866-369-7763
> Fax: 866-849-8082
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: idle in transaction
@ 2004-02-15 17:10 Marc Mitchell <marcm@eisolution.com>
parent: Warren Little <wlittle@securitylending.com>
1 sibling, 1 reply; 13+ messages in thread
From: Marc Mitchell @ 2004-02-15 17:10 UTC (permalink / raw)
To: 'Warren Little' <wlittle@securitylending.com>; pgsql-admin
We see the exact same condition when setting "setAutoCommit(FALSE)".
However, we expected that situation based on what we understand as the
way such a setting manifests itself within the
Multi-Statement-Transaction handling of Postgres. Furthermore, we saw
this exact same behavior under at least 7.3.4, if not 7.3.2 and maybe
even 7.2.something.
So long as the idled transaction isn't holding any locks on any data
resources, I don't know if this condition is a bad thing. It would be
nice to be able to differentiate between a transaction that has been
"declared" but has yet to really begin issuing any statements and take
locks from transactions that are idle "mid-transaction". The latter of
which spells death for a multi-user, OLTP application.
I'll be curious to hear others experience with this.
Marc Mitchell
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm@eisolution.com
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Warren Little
Sent: Sunday, February 15, 2004 10:24 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] idle in transaction
We recently upgraded postgres from 7.3 to 7.4, along with the JDBC jar,
and noticed all the backend processes/connections are left in the "idle
in transaction" state where before they where left in the "idle" state.
Has something changed in the 7.4 jdbc driver vs 7.3 which might be
causing this? Note We call setAutoCommit(FALSE) on every connection when
created.
--
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8082
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: idle in transaction
@ 2004-02-15 17:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Mitchell <marcm@eisolution.com>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2004-02-15 17:29 UTC (permalink / raw)
To: Marc Mitchell <marcm@eisolution.com>; +Cc: 'Warren Little' <wlittle@securitylending.com>; pgsql-admin
"Marc Mitchell" <marcm@eisolution.com> writes:
> So long as the idled transaction isn't holding any locks on any data
> resources, I don't know if this condition is a bad thing. It would be
> nice to be able to differentiate between a transaction that has been
> "declared" but has yet to really begin issuing any statements and take
> locks from transactions that are idle "mid-transaction".
You guys should probably take this to the pgsql-jdbc list, since that's
where the people who know the innards of the JDBC driver hang out.
I just saw a patch go by on that list that purports to
* Keeps track of the current transaction state.
* Prevents starting a new transaction until actually required.
so that may address your concern. I'm not enough of a JDBC hacker
to be sure though.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* IDLE IN TRANSACTION
@ 2006-04-19 11:59 Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
0 siblings, 2 replies; 13+ messages in thread
From: Rodrigo Sakai @ 2006-04-19 11:59 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: IDLE IN TRANSACTION
@ 2006-04-19 12:05 Andy Shellam <andy.shellam@mailnetwork.co.uk>
parent: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
1 sibling, 1 reply; 13+ messages in thread
From: Andy Shellam @ 2006-04-19 12:05 UTC (permalink / raw)
To: 'Rodrigo Sakai' <rodrigo.sakai@zanthus.com.br>; +Cc: pgsql-admin
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!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: IDLE IN TRANSACTION
@ 2006-04-19 12:36 Peter Eisentraut <peter_e@gmx.net>
parent: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
1 sibling, 0 replies; 13+ messages in thread
From: Peter Eisentraut @ 2006-04-19 12:36 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, "Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br>
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/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: IDLE IN TRANSACTION
@ 2006-04-19 18:14 Chandra Sekhar Surapaneni <chandu@positivenetworks.net>
0 siblings, 0 replies; 13+ messages in thread
From: Chandra Sekhar Surapaneni @ 2006-04-19 18:14 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>; pgsql-admin
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!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: IDLE IN TRANSACTION
@ 2006-04-19 18:51 Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
parent: Andy Shellam <andy.shellam@mailnetwork.co.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Rodrigo Sakai @ 2006-04-19 18:51 UTC (permalink / raw)
To: andy.shellam@mailnetwork.co.uk; +Cc: pgsql-admin
>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!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: IDLE IN TRANSACTION
@ 2006-04-19 19:02 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
0 siblings, 0 replies; 13+ messages in thread
From: Scott Marlowe @ 2006-04-19 19:02 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>; +Cc: andy.shellam@mailnetwork.co.uk; pgsql-admin
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* idle in transaction
@ 2023-11-28 04:00 Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Nikhil Ingale @ 2023-11-28 04:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
Can somebody help me to understand the below given idle in transaction?
SELECT * FROM pg_stat_activity where pid = 2161513;
-[ RECORD 1
]----+-----------------------------------------------------------------------------------
datid | 37490141
datname | prod
pid | 2161513
leader_pid |
usesysid | 37490157
usename | admin
application_name |
client_addr | ::1
client_hostname |
client_port | 16788
backend_start | 2023-11-27 20:24:20.20062+05:30
xact_start | 2023-11-27 20:24:33.694904+05:30
query_start | 2023-11-28 09:25:57.666297+05:30
state_change | 2023-11-28 09:25:57.666336+05:30
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid |
backend_xmin |
query | SAVEPOINT _per_query_svp_;DEALLOCATE
"_PLAN0x7fdd58004550";RELEASE _per_query_svp_
backend_type | client backend
I would like to understand the following query found in the
pg_stat_activity.
I see this particular process always active but not doing anything in the
BDR network. How do I find out what query is it running?
SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE
_per_query_svp_
Regards,
Nikhil Ingale
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: idle in transaction
@ 2023-11-28 04:07 David G. Johnston <david.g.johnston@gmail.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2023-11-28 04:07 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Monday, November 27, 2023, Nikhil Ingale <niks.bgm@gmail.com> wrote:
> How do I find out what query is it running?
>
> SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE
> _per_query_svp_
>
That is the last command (multi-statement though it is) that was executed
before the transaction/session went idle.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: idle in transaction
@ 2023-11-28 06:53 Laurenz Albe <laurenz.albe@cybertec.at>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Laurenz Albe @ 2023-11-28 06:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Mon, 2023-11-27 at 21:07 -0700, David G. Johnston wrote:
> On Monday, November 27, 2023, Nikhil Ingale <niks.bgm@gmail.com> wrote:
> > How do I find out what query is it running?
> >
> > SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE _per_query_svp_
> >
>
> That is the last command (multi-statement though it is) that was executed before the transaction/session went idle.
... and it points towards a problem that is quite unrelated to the actual
question. There seems to be an automatism in your application that wraps
every statement in a subtransaction to emulate statement-level rollback.
That will cause severe performance problems:
https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/
So your application is broken not only because it does not close its
transactions.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2023-11-28 06:53 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-15 16:23 idle in transaction Warren Little <wlittle@securitylending.com>
2004-02-15 16:53 ` Soeren Laursen <sl@scrooge.dk>
2004-02-15 17:10 ` Marc Mitchell <marcm@eisolution.com>
2004-02-15 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-19 11:59 IDLE IN TRANSACTION Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
2006-04-19 12:05 ` Re: IDLE IN TRANSACTION Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-04-19 18:51 ` Re: IDLE IN TRANSACTION Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
2006-04-19 19:02 ` Re: IDLE IN TRANSACTION Scott Marlowe <smarlowe@g2switchworks.com>
2006-04-19 12:36 ` Re: IDLE IN TRANSACTION Peter Eisentraut <peter_e@gmx.net>
2006-04-19 18:14 Re: IDLE IN TRANSACTION Chandra Sekhar Surapaneni <chandu@positivenetworks.net>
2023-11-28 04:00 idle in transaction Nikhil Ingale <niks.bgm@gmail.com>
2023-11-28 04:07 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-28 06:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox