Skip site navigation (1) Skip section navigation (2)

Re: "Idle in Transaction" revisited.

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: John R Pierce <pierce(at)hogranch(dot)com>
Cc: Postgres JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: "Idle in Transaction" revisited.
Date: 2004-09-20 08:47:57
Message-ID: 1095670077.3661.47.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-jdbc
[snip]
> I'm amazed...   Sounds like noone is doing any serious work with JDBC, or 
> only has short lived 'applet' kind of applications where long persistance 
> connections aren't an issue.

In our experience, long running transactions are a PITA for all databases, even if they seam to work fine.
It is always possible to design the work flow so that the transactions are short lived:
 - don't ever open a transaction and then wait for user input;
 - use cooperative locking instead of data base locking for long lived locks;
 - chunk transactions when processing huge amounts of data;

Regarding cooperative locking and the fact that after a crash the lock
stays: I'm not sure how your application is designed, but in ours after
a crash we definitely want it to stay there, so we can manually check
the consistency of the data before releasing the lock for further
processing. I have bad experience with automatic recovery after crashes.

Just my 2c,
Csaba.


> I can think of a bunch of scenarios where this would cause problems...
> 
> *  An service app that opens several connections for various threads,
>    and one or another of them goes idle for a long time ... No vacuuming
>    during this state.
> 
> *  Thread has been idle for awhile, then goes to do a SELECT and it will
>    only see data that was committed prior to this threads last COMMIT.
> 
> etc etc etc. 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org


In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2004-09-20 09:12:07
Subject: Re: raising the default prepareTheshold
Previous:From: Gaetano MendolaDate: 2004-09-19 23:18:06
Subject: Re: "Idle in Transaction" revisited.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group