Thread: "Idle in Transaction" revisited.

"Idle in Transaction" revisited.

From
"John R Pierce"
Date:
Our applications are factory floor messaging systems, mostly in Java, that
have persistence measured in weeks and even months, they run 24/7.  This
isn't WebApplet stuff, nor is it JavaBean stuff, its simple java programs
running on servers doing socket messaging and such.

Recently, I've steered the Java developers in my group towards Postgres on
Linux for specific applications rather than the $$$$ Sun/Oracle platforms my
department has traditionally used...

We're using locally-compiled pgsql 7.4.5 with the latest released jdbc, java
1.4.x on linux 2.4.x (typically redhat enterprise 2.1 or 3).

We've been running into a *lot* of problems with "Idle in Transaction"
threads causing long term blockage of VACUUM's ability to free up dead rows.
Our applications tend to be heavily multithreaded, often using a dozen or
more SQL connections for various purposes.

It took me quite a bit of detective work, with some assistance from the
pgsql-bugs list, and the #postgresql folks on freenode to get to the bottom
of this Idle in Transaction thing causing a problem when a JDBC thread was
doing nothing but periodic SELECT * FROM event_master;  without any commits
(and auto_commit=off).    Sorted that case out, but now are running into yet
more related issues...

One case in point, as a SQL form of 'mutex', one of these developers was
used to doing a 'UPDATE' on a particular row of a table without ever
committing to leave a row lock in place so that other threads won't attempt
to 'subscribe' to the same 'topic' on a corporate intranet messaging system.
We can't do this in Postgres (works great in Oracle) due to the 'idle in
transaction' issue...  as other threads and programs and databases on this
system are doing lots and LOTS of updates, we need hourly vacuuming or some
of our tables really slow down (one thread was doing around 60-90 updates
per second of the same 20 or so rows in a table to maintain highwater marks
to prevent duplicate record processing in cases of aborts, failures, and
abrupt restarts).


I've scanned and searched the messages on this forum, and only found a few
threads from back in April dealing with this sort of thing.

I'm curious what common practice is for threads that do nothing but
SELECTS... do folks just enable auto_commit, thereby preventing pgJDBC from
doing BEGIN; ?  Do they lace their code with COMMIT() calls?

What about a resource locking table like I described above, where we've got
a few dozen arbitrarily named resources we need to gain a mutex on?   When I
suggested 'select for update' to see if the resource was available, then
'update' to mark it in use, and commit, the developer said that leaves the
resource locked if the app crashes or the box is rebooted or whatever, while
his row lock would be cleared automatically.


Re: "Idle in Transaction" revisited.

From
Tom Lane
Date:
"John R Pierce" <pierce@hogranch.com> writes:
> We've been running into a *lot* of problems with "Idle in Transaction"
> threads causing long term blockage of VACUUM's ability to free up dead rows.

Yeah, that's from prematurely issuing BEGIN.  I believe there's a
solution for this in the most recent JDBC driver versions --- but
I dunno if it's in any official releases yet.

> One case in point, as a SQL form of 'mutex', one of these developers was
> used to doing a 'UPDATE' on a particular row of a table without ever
> committing to leave a row lock in place so that other threads won't attempt
> to 'subscribe' to the same 'topic' on a corporate intranet messaging system.
> We can't do this in Postgres (works great in Oracle) due to the 'idle in
> transaction' issue...

Check.  It seems like what you might want here is cooperative locking
a la the contrib/userlock module.  We were just having a discussion in
another list about whether that code had any real future ... but if we
see people actually using it in the field, it's much more likely to get
moved into the mainline than dropped.

> What about a resource locking table like I described above, where we've got
> a few dozen arbitrarily named resources we need to gain a mutex on?   When I
> suggested 'select for update' to see if the resource was available, then
> 'update' to mark it in use, and commit, the developer said that leaves the
> resource locked if the app crashes or the box is rebooted or whatever, while
> his row lock would be cleared automatically.

A userlock will be dropped on session exit or database restart, so I
think that base is covered.  The main objection I can think of is that
it is voluntary, since the backend has no idea what other DB resources
a userlock is meant to protect.  Depending on your coding practices that
might be fine, or it might be a fatal drawback.

If you migrate to using userlocks or some variant, let us know --- real
applications using it will definitely move that code way up in priority.

            regards, tom lane

Re: "Idle in Transaction" revisited.

From
"John R Pierce"
Date:
> Yeah, that's from prematurely issuing BEGIN.  I believe there's a
> solution for this in the most recent JDBC driver versions --- but
> I dunno if it's in any official releases yet.

Its my understanding from reading the prior threads on this that somewhere
after jdbc 213 they moved the initial BEGIN to the first SQL Execute rather
than immediately upon Connection.

When an app does a Commit or Rollback, this generates an END;

After this END; does a new BEGIN happen immediately, or is it postponed
until the next Execute ?



Re: "Idle in Transaction" revisited.

From
Oliver Jowett
Date:
Tom Lane wrote:
> "John R Pierce" <pierce@hogranch.com> writes:
>
>>We've been running into a *lot* of problems with "Idle in Transaction"
>>threads causing long term blockage of VACUUM's ability to free up dead rows.
>
>
> Yeah, that's from prematurely issuing BEGIN.  I believe there's a
> solution for this in the most recent JDBC driver versions --- but
> I dunno if it's in any official releases yet.

The "development driver" versions on jdbc.postgresql.org have this fixed
as part of the V3 overhaul. I'm not sure about the driver that's in the
7.4 tree.

-O

Re: "Idle in Transaction" revisited.

From
Kris Jurka
Date:

On Fri, 17 Sep 2004, John R Pierce wrote:

> > Yeah, that's from prematurely issuing BEGIN.  I believe there's a
> > solution for this in the most recent JDBC driver versions --- but
> > I dunno if it's in any official releases yet.
>
> Its my understanding from reading the prior threads on this that somewhere
> after jdbc 213 they moved the initial BEGIN to the first SQL Execute rather
> than immediately upon Connection.
>

This change only occured in the 8.0 development branch, and not on the 7.4
branch.  I produced a patch and then current versions of the 7.4 jar files
for people to try this out on their 7.4 servers, but no one ever got back
to me about it, so I didn't apply it because it was a pretty ugly fix that
no one seemed to need.  See

http://www.ejurka.com/pgsql/jars/transaction_state/

> When an app does a Commit or Rollback, this generates an END;
> After this END; does a new BEGIN happen immediately, or is it postponed
> until the next Execute ?

Yes, in the 7.4 branch a BEGIN is issued immediately.

Kris Jurka

Re: "Idle in Transaction" revisited.

From
Gaetano Mendola
Date:
John R Pierce wrote:

> I'm curious what common practice is for threads that do nothing but 
> SELECTS... do folks just enable auto_commit, thereby preventing pgJDBC 
> from doing BEGIN; ?  Do they lace their code with COMMIT() calls?

We were bitten by this problem too, and my solution was to suggest our
developer to do and explicit: "ABORT" after the connection, and do
explicit "BEGIN"   "END"  instead of rely on the jdbc interface.


Regards
Gaetano Mendola



Re: "Idle in Transaction" revisited.

From
Oliver Jowett
Date:
Gaetano Mendola wrote:
> John R Pierce wrote:
>
>> I'm curious what common practice is for threads that do nothing but
>> SELECTS... do folks just enable auto_commit, thereby preventing pgJDBC
>> from doing BEGIN; ?  Do they lace their code with COMMIT() calls?
>
>
> We were bitten by this problem too, and my solution was to suggest our
> developer to do and explicit: "ABORT" after the connection, and do
> explicit "BEGIN"   "END"  instead of rely on the jdbc interface.

This is a pretty bad idea as it can confuse the driver's idea of the
current transaction state. For example, cursor-based resultsets won't
ever be used if you do your own transaction demarcation in this way.

Better to use the standard JDBC autocommit API and a driver that has the
premature-BEGIN problem fixed.

-O

Re: "Idle in Transaction" revisited.

From
Gaetano Mendola
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Oliver Jowett wrote:
| Gaetano Mendola wrote:
|
|> John R Pierce wrote:
|>
|>> I'm curious what common practice is for threads that do nothing but
|>> SELECTS... do folks just enable auto_commit, thereby preventing
|>> pgJDBC from doing BEGIN; ?  Do they lace their code with COMMIT() calls?
|>
|>
|>
|> We were bitten by this problem too, and my solution was to suggest our
|> developer to do and explicit: "ABORT" after the connection, and do
|> explicit "BEGIN"   "END"  instead of rely on the jdbc interface.
|
|
| This is a pretty bad idea as it can confuse the driver's idea of the
| current transaction state. For example, cursor-based resultsets won't
| ever be used if you do your own transaction demarcation in this way.

Fortunatelly I do not use cursors otherwise I could find it out long time ago,
this was the only solution since the 6.x version, and is working well at
least in my applications.
BTW also the python drivers were ( are ? ) affected by this "idle in transaction"
phenomenon and the jdbc 7.4 series too.


Regards
Gaetano Mendola





-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBTD0p7UpzwH2SGd4RAr1nAKCmtDjy8TWAMP/vhC+Kcn+7/XZEOgCgvLN4
YEWFotidXi46eF2rfOpywWc=
=XFRs
-----END PGP SIGNATURE-----


Re: "Idle in Transaction" revisited.

From
"John R Pierce"
Date:
>> Its my understanding from reading the prior threads on this that
>> somewhere
>> after jdbc 213 they moved the initial BEGIN to the first SQL Execute
>> rather
>> than immediately upon Connection.
>>
>
> This change only occured in the 8.0 development branch, and not on the 7.4
> branch.  I produced a patch and then current versions of the 7.4 jar files
> for people to try this out on their 7.4 servers, but no one ever got back
> to me about it, ...

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.

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.


Re: "Idle in Transaction" revisited.

From
Kris Jurka
Date:

On Sat, 18 Sep 2004, John R Pierce wrote:

> 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.

This doesn't mean no vacuuming, only no vacuuming of old rows.  This can
be difficult to actually detect so it's probably happening to people
without them noticing.  They see a performance drop, but vacuum seems to
work, so they don't see anything really wrong.

> *  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.
>

This will only happen when working with serializable transaction
isolation, not read committed (which is the default).

Kris Jurka


Re: "Idle in Transaction" revisited.

From
Oliver Jowett
Date:
John R Pierce wrote:
>>> Its my understanding from reading the prior threads on this that
>>> somewhere
>>> after jdbc 213 they moved the initial BEGIN to the first SQL Execute
>>> rather
>>> than immediately upon Connection.
>>>
>>
>> This change only occured in the 8.0 development branch, and not on the
>> 7.4
>> branch.  I produced a patch and then current versions of the 7.4 jar
>> files
>> for people to try this out on their 7.4 servers, but no one ever got back
>> to me about it, ...
>
>
> 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.

[...]

Often, connection pools have a maximum connection lifetime (either
directly or indirectly via their replacement policy). So you may just
not see the problem at all in this case since you never have an idle
transaction open indefinitely.

I'm doing "serious work" with JDBC and the stable driver does have a
number of deficiencies. I've been fixing them as I encounter them -- I
suggest you try the current development driver as this is where I've
been doing my work. The development driver should be quite stable, it
just hasn't had as much real-world use as the stable driver has had.
We're using it in released versions of our software without problems.

-O

Re: "Idle in Transaction" revisited.

From
Jeffrey Tenny
Date:
> John R Pierce <pierce@hogranch.com>, pgsql-jdbc@postgresql.org
>
> Gaetano Mendola wrote:
>
>> John R Pierce wrote:
>>
>>> I'm curious what common practice is for threads that do nothing
>>> but SELECTS... do folks just enable auto_commit, thereby
>>> preventing pgJDBC from doing BEGIN; ?  Do they lace their code
>>> with COMMIT() calls?
>>
>>
>>
>> We were bitten by this problem too, and my solution was to suggest
>> our developer to do and explicit: "ABORT" after the connection, and
>> do explicit "BEGIN"   "END"  instead of rely on the jdbc interface.
>>
>
>
> This is a pretty bad idea as it can confuse the driver's idea of the
> current transaction state. For example, cursor-based resultsets won't
> ever be used if you do your own transaction demarcation in this way.
>
> Better to use the standard JDBC autocommit API and a driver that has
> the premature-BEGIN problem fixed.

Second that, it's a very bad idea. I once naively had my own transaction
management using begin/end via jdbc for multi-statement transactions
against PostgreSQL.
Then the driver was updated at some point and I was getting all kinds
of screwed up commit logic and inconsistent data models.

Stick with and support the proper JDBC interfaces.  Anything else is
platform dependeng, among other bad things.



Re: "Idle in Transaction" revisited.

From
Jeffrey Tenny
Date:
> On Sat, 18 Sep 2004, John R Pierce wrote:
>
>
>>> 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.
>
>
> This doesn't mean no vacuuming, only no vacuuming of old rows.  This can
> be difficult to actually detect so it's probably happening to people
> without them noticing.  They see a performance drop, but vacuum seems to
> work, so they don't see anything really wrong.
>
>
>>> *  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.
>>>
>
>
> This will only happen when working with serializable transaction
> isolation, not read committed (which is the default).
>

I'm still running against 7.3.x, but if the above two paragraphs
are true that's very problematic for me if I'm going to upgrade.
My app also runs 24/7 and uses serializable transactions in pooled
connections.

Are you saying that I will see stale database state at the logical
"start" of my transaction from a pooled connection?

That's a showstopper for sure. If I'm confused please enlighten me.
I've been holding off on upgrading because of the amount activity on
this list about 7.4+ systems.

This is the first thing that's been sufficiently integrity threatening
that it prompted me to de-lurk.  I need data as of when the transaction
starts, not when the database last committed a transaction via that
connection.



Re: "Idle in Transaction" revisited.

From
Tom Lane
Date:
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> Are you saying that I will see stale database state at the logical
> "start" of my transaction from a pooled connection?

> That's a showstopper for sure. If I'm confused please enlighten me.

Apparently you are confused, because the JDBC behavior has always
been like that; it's only the current devel driver that fixes it.

            regards, tom lane

Re: "Idle in Transaction" revisited.

From
Oliver Jowett
Date:
Tom Lane wrote:
> Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
>
>>Are you saying that I will see stale database state at the logical
>>"start" of my transaction from a pooled connection?
>
>
>>That's a showstopper for sure. If I'm confused please enlighten me.
>
>
> Apparently you are confused, because the JDBC behavior has always
> been like that; it's only the current devel driver that fixes it.

That's not entirely true; 7.3-era drivers (which I think is what is
being used here) used "set autocommit" rather than explicit BEGIN. It
went back to using BEGIN again when 7.4 removed that option.

-O

Re: "Idle in Transaction" revisited.

From
Oliver Jowett
Date:
Jeffrey Tenny wrote:

> I'm still running against 7.3.x, but if the above two paragraphs
> are true that's very problematic for me if I'm going to upgrade.
> My app also runs 24/7 and uses serializable transactions in pooled
> connections.
>
> Are you saying that I will see stale database state at the logical
> "start" of my transaction from a pooled connection?
>
> That's a showstopper for sure. If I'm confused please enlighten me.
> I've been holding off on upgrading because of the amount activity on
> this list about 7.4+ systems.

You'll only see the problem if you use a driver without a fix for the
premature-BEGIN problem.

Ideally you should be using the current stable driver even with a 7.3
server. The stable driver is intended to work against 7.3 servers
correctly, and you're missing out on many bugfixes (although
unfortunately not the premature-BEGIN one). AFAIK we haven't been
backporting fixes to the 7.3 branch for quite a while now, since the
latest drivers directly support 7.3.

7.3 is a bit of a special case with respect to transaction handling as
the 7.3-era drivers used the backend "set autocommit" functionality, but
then it went away again in 7.4 so we went back to explicit BEGIN. So
unfortunately unless you want to use the development driver or fix the
stable driver, you might be stuck with the 7.3 driver for now.

I think the plan is for the current development driver to become the
"stable" driver around the time that 8.0.0 is released. There has also
been discussion about dropping protocol V2 support in the driver (and
therefore 7.3 support) although we're keeping it for now. And the 7.3
server itself is getting pretty old.. You probably want to start
thinking about your upgrade path!

-O

Re: "Idle in Transaction" revisited.

From
Paul Thomas
Date:
On 18/09/2004 22:59 Jeffrey Tenny wrote:
> [snip]
> I'm still running against 7.3.x, but if the above two paragraphs
> are true that's very problematic for me if I'm going to upgrade.
> My app also runs 24/7 and uses serializable transactions in pooled
> connections.
>
> Are you saying that I will see stale database state at the logical
> "start" of my transaction from a pooled connection?
>
> That's a showstopper for sure. If I'm confused please enlighten me.
> I've been holding off on upgrading because of the amount activity on this
> list about 7.4+ systems.
>
> This is the first thing that's been sufficiently integrity threatening
> that it prompted me to de-lurk.  I need data as of when the transaction
> starts, not when the database last committed a transaction via that
> connection.

Our web app deploys on 7.3 and 7.4. There's no problem with the 7.3
db/driver but you could be bitten using the 7.4 driver. My solution is to
always setAutoCommit(true) before returning the connection to the pool.

HTH

--
Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business           |
| Computer Consultants         | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+

Re: "Idle in Transaction" revisited.

From
Gaetano Mendola
Date:
Jeffrey Tenny wrote:
>> John R Pierce <pierce@hogranch.com>, pgsql-jdbc@postgresql.org
>>
>> Gaetano Mendola wrote:
>>
>>> John R Pierce wrote:
>>>
>>>> I'm curious what common practice is for threads that do nothing
>>>> but SELECTS... do folks just enable auto_commit, thereby
>>>> preventing pgJDBC from doing BEGIN; ?  Do they lace their code
>>>> with COMMIT() calls?
>>>
>>>
>>>
>>>
>>> We were bitten by this problem too, and my solution was to suggest
>>> our developer to do and explicit: "ABORT" after the connection, and
>>> do explicit "BEGIN"   "END"  instead of rely on the jdbc interface.
>>>
>>
>>
>> This is a pretty bad idea as it can confuse the driver's idea of the
>> current transaction state. For example, cursor-based resultsets won't
>> ever be used if you do your own transaction demarcation in this way.
>>
>> Better to use the standard JDBC autocommit API and a driver that has
>> the premature-BEGIN problem fixed.
> 
> 
> Second that, it's a very bad idea. I once naively had my own transaction 
> management using begin/end via jdbc for multi-statement transactions 
> against PostgreSQL.

I totally agree woth both of you but this is the only solution now, something
else to suggest ? The 8.0 version will work with a 7.4 engine ?



Regards
Gaetano Mendola








Re: "Idle in Transaction" revisited.

From
Oliver Jowett
Date:
Gaetano Mendola wrote:

> I totally agree woth both of you but this is the only solution now,
> something
> else to suggest ? The 8.0 version will work with a 7.4 engine ?

Yes, it works with 7.3, 7.4, 8.0, and possibly 7.2 (I can't remember..).

-O

Re: "Idle in Transaction" revisited.

From
Gaetano Mendola
Date:
Oliver Jowett wrote:> Gaetano Mendola wrote:>>> I totally agree woth both of you but this is the only solution now,>>
something>>else to suggest ? The 8.0 version will work with a 7.4 engine ?>>> Yes, it works with 7.3, 7.4, 8.0, and
possibly7.2 (I can't remember..).
 

These are good news, don't you think is better write on jdbc.postgresql.orf
a compatibility table ? Do you have any forecast for a 8.0 serie stable ?
As soon as is ready I'll recomend the upgrade, for that time I have to rely
on my ugly explicit "begin" "end".


Regards
Gaetano Mendola







Re: "Idle in Transaction" revisited.

From
Csaba Nagy
Date:
[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@postgresql.org


Re: "Idle in Transaction" revisited.

From
"John R Pierce"
Date:
> 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;

Trouble is, with JDBC as its currently implemented, the instant you Commit()
or Rollback(), JDBC starts a new transaction automatically, since JDBC has
no explicit Begin().   If that thread then goes quiescent for an arbitrary
period of time (perhaps waiting for more messaging traffic, our apps are
message driven factory floor middleware things), this transaction remains
open.    Our applications are characterized by bursts of frenetic activity
followed by long idle periods, quite unpredictably.

> 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.

in this particular case, the lock is to simply indicate there is an active
'subscriber' to a messaging 'subject', if the app has exited any which ways,
by definition that 'subscriber' is no longer active, so yes, we want the
lock to go away.


Re: "Idle in Transaction" revisited.

From
Csaba Nagy
Date:
> Trouble is, with JDBC as its currently implemented, the instant you Commit()
> or Rollback(), JDBC starts a new transaction automatically, since JDBC has
> no explicit Begin().   If that thread then goes quiescent for an arbitrary
> period of time (perhaps waiting for more messaging traffic, our apps are
> message driven factory floor middleware things), this transaction remains
> open.    Our applications are characterized by bursts of frenetic activity
> followed by long idle periods, quite unpredictably.
>
OK, now I got your problem. For this one I can't imagine other solution
than fixing it on the DB side :-)
Just as a side note: if you use a connection pool which is under your
control, it still could prove useful to sort of "refresh" the
connections from time to time by dropping connections which are in
service for longer than a maximum time to live (say a few hours).
This way you avoid possible problems with resource leakage which
occasionally can happen.

> in this particular case, the lock is to simply indicate there is an active
> 'subscriber' to a messaging 'subject', if the app has exited any which ways,
> by definition that 'subscriber' is no longer active, so yes, we want the
> lock to go away.
>

Now this looks to me more like a "registry" than a lock.
In any case, it could be replaced by some leasing scheme, where you
place a record which expires after a specified amount of time, and the
active process has to refresh it from time to time to keep it active.
I'm just that against DB locks, the hit my back a few times...

Cheers,
Csaba.



Re: "Idle in Transaction" revisited.

From
Jeffrey Tenny
Date:
> Trouble is, with JDBC as its currently implemented, the instant you
> Commit() or Rollback(), JDBC starts a new transaction automatically,
> since JDBC has no explicit Begin().   If that thread then goes
> quiescent for an arbitrary period of time (perhaps waiting for more
> messaging traffic, our apps are message driven factory floor
> middleware things), this transaction remains open.    Our
> applications are characterized by bursts of frenetic activity
> followed by long idle periods, quite unpredictably.

I was under the impression that this was easily addressed by
delaying the onset of the next transaction after commit/rollback until
some actual statement activity began. (like a SELECT...)



Re: "Idle in Transaction" revisited.

From
"John R Pierce"
Date:
>> Trouble is, with JDBC as its currently implemented, the instant you
>> Commit() or Rollback(), JDBC starts a new transaction automatically,
>> since JDBC has no explicit Begin().   If that thread then goes
>> quiescent for an arbitrary period of time (perhaps waiting for more
>> messaging traffic, our apps are message driven factory floor
>> middleware things), this transaction remains open.    Our
>> applications are characterized by bursts of frenetic activity
>> followed by long idle periods, quite unpredictably.
>
> I was under the impression that this was easily addressed by
> delaying the onset of the next transaction after commit/rollback until
> some actual statement activity began. (like a SELECT...)

yes, apparently that fix is in the 8 beta client, but not in the 7.4.5
client.


Re: "Idle in Transaction" revisited.

From
Markus Schaber
Date:
Hi,

On Mon, 20 Sep 2004 08:52:29 -0700
"John R Pierce" <pierce@hogranch.com> wrote:

> > 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.
>
> in this particular case, the lock is to simply indicate there is an active
> 'subscriber' to a messaging 'subject', if the app has exited any which ways,
> by definition that 'subscriber' is no longer active, so yes, we want the
> lock to go away.

Have you ever thought at using LISTEN/NOTIFY for this purpose?

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Re: "Idle in Transaction" revisited.

From
Gaetano Mendola
Date:
John R Pierce wrote:
>>> Trouble is, with JDBC as its currently implemented, the instant you
>>> Commit() or Rollback(), JDBC starts a new transaction automatically,
>>> since JDBC has no explicit Begin().   If that thread then goes
>>> quiescent for an arbitrary period of time (perhaps waiting for more
>>> messaging traffic, our apps are message driven factory floor
>>> middleware things), this transaction remains open.    Our
>>> applications are characterized by bursts of frenetic activity
>>> followed by long idle periods, quite unpredictably.
>>
>>
>> I was under the impression that this was easily addressed by
>> delaying the onset of the next transaction after commit/rollback until
>> some actual statement activity began. (like a SELECT...)
> 
> 
> yes, apparently that fix is in the 8 beta client, but not in the 7.4.5 
> client.

However it seems the 8.0 client will work with the 7.4.x  server



Regards
Gaetano Mendola




Re: "Idle in Transaction" revisited.

From
Andrew Sullivan
Date:
On Sat, Sep 18, 2004 at 08:47:24AM -0700, John R Pierce wrote:
>
> 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.

For what it's worth, this issue has killed me at work, several times
(I'm actually a cat).  We've hacked around it in various ways, but
I'm glad that a real solution is in the 8.0 driver.

A
--
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
        --Roger Brinner