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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-jdbc by date

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