Обсуждение: idle in transaction with mod_perl
Hi all. Let me start by saying that I've been using postgres 7.1.3 since it came out and have found it to be rock solid and simply wonderful! :-) I particularly like the unlimited size varchars which I was waiting like crazy for. ( actually i think they came out in a previous release which i missed) Also for all anoraks out there let me say that performance wise the thing kicks ass! I've been using it on linux in a dual 1.2Ghz AMD box with SCSI as a web crawler database with up to 30 crawlers hammering two tables simultaneously, without a glitch. (a table has over 4,000,000 records and still holds fine) Anyway, sorry for the long intro. Back to the point. I've a got a production machine running the above version of pg with mod_perl (with standard DBI v1.13, not the Apache flavor) accessing a database and i'm getting some idle connections that make me worry. postgres 28252 0.0 0.2 6832 3708 ? S 02:59 0:00 postgres: nobody mydbname [local] idle in transaction I can appreciate them being "idle" but why would they be in a "transaction"? I've checked all my code and found that it always does a rollback or a commit after each INSERT/UPDATE SQL statement. Further to this I am very-very sure that statement handles for SELECTs are always "finished" ($sth->finish) once I'm done reading rows from them. ( I spent hours verifying this) To make things simpler, I tested a senario where the only staments issued where SELECTs. But the idle connections still come up (ps -auwwx|grep postg) as being in a transaction. So what's going on? I read a post by another guy that had a similar problem and found out that SELECT statements can also be considered as transactions which is fine. In a desperate attempt to make things as tidy as possible I even went as far as always calling rollback at the end of the script just to try and clear whatever it is that is in transaction but nope.... they're still there. When I run psql and execute a couple of statements through it and then leave in peace, the connection becomes idle but not in a transaction, which sort of gives me a warm feeling inside. Can I achieve this in my situation? Any help, solutions, suggestions? Should I be worried in eirher case? Can the "idle in transaction" connections turn sour and corrupt my DB or worst still bring down my server? I appreciate any help. Thanks, Kostis
Hey Kotis!
I too use mod_perl + postgresql
its perfectly ok to have "idle in transaction..."
Becoz as soon as DBI -> commits a given transaction the
mod_perl process starts a new transaction. the $dbhs' are
in a perpetual transaction mode.
I gues you must have done DBI->{AutoCommit} = 0 for that
to happen.
the persistant connections makes all the difference.
One advise : please upgrade to postgresql 7.2.2
Regds
Mallah.
> Hi all.
>
> Let me start by saying that I've been using postgres 7.1.3 since it came out and have found it
> to be rock solid and simply wonderful! :-)
> I particularly like the unlimited size varchars which I was waiting like crazy for. ( actually
> i think they came out in a previous release which i missed)
>
> Also for all anoraks out there let me say that performance wise the thing kicks ass! I've been
> using it on linux in a dual 1.2Ghz AMD box with SCSI as a web crawler database with up to 30
> crawlers hammering two tables simultaneously, without a glitch. (a table has over 4,000,000
> records and still holds fine)
>
> Anyway, sorry for the long intro. Back to the point.
>
> I've a got a production machine running the above version of pg with mod_perl
> (with standard DBI v1.13, not the Apache flavor) accessing a database and
> i'm getting some idle connections that make me worry.
>
> postgres 28252 0.0 0.2 6832 3708 ? S 02:59 0:00 postgres: nobody mydbname
> [local] idle in transaction
>
> I can appreciate them being "idle" but why would they be in a "transaction"?
>
> I've checked all my code and found that it always does a rollback or a commit after each
> INSERT/UPDATE SQL statement. Further to this I am very-very sure that statement handles for
> SELECTs are always "finished" ($sth->finish) once I'm done reading rows from them. ( I spent
> hours verifying this)
>
> To make things simpler, I tested a senario where the only staments issued where SELECTs. But
> the idle connections still come up (ps -auwwx|grep postg) as being in a transaction.
>
> So what's going on?
>
> I read a post by another guy that had a similar problem and found out that SELECT statements
> can also be considered as transactions which is fine.
>
> In a desperate attempt to make things as tidy as possible I even went as far as always calling
> rollback at the end of the script just to try and clear whatever it is that is in transaction
> but nope.... they're still there.
>
> When I run psql and execute a couple of statements through it and then leave in peace, the
> connection becomes idle but not in a transaction, which sort of gives me a warm feeling
> inside. Can I achieve this in my situation?
>
> Any help, solutions, suggestions?
> Should I be worried in eirher case?
> Can the "idle in transaction" connections turn sour and corrupt my DB or worst still bring
> down my server?
>
> I appreciate any help.
>
> Thanks,
> Kostis
>
> ---------------------------(end of broadcast)--------------------------- TIP 6: Have you
> searched our list archives?
>
> http://archives.postgresql.org
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
On Fri, Oct 04, 2002 at 08:04:37PM +0530, mallah@trade-india.com wrote:
> I too use mod_perl + postgresql
> its perfectly ok to have "idle in transaction..."
Well, not really. But it's probably normal. See below.
> the persistant connections makes all the difference.
For some reason, most of the persistent
connection/pooling/auto-commit=off approaches in PostgreSQL issue a
BEGIN as soon as a transaction ends.
The problem with this is that several maintenance types of things --
VACUUM FULL being the most obvious -- will conflict with these kinds
of transactions. If you happen to have a connection that stays idle
this way for a long time, it can cause you some little bits of grief
(like by not reclaiming as much disk as it otherwise would, or not
recycling as much in a non-blocking VACUUM, or other similar things).
The real answer is to change the connection pooling &c. But since
that's not likely to happen in a lot of cases, you should just make
sure that you don't perpetually leave around idle connections.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110