Hi and thanks
But how do I know which transaction was not commited ?
-gili

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, April 07, 2005 3:40 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Performance when deleting a lot of records, again


At 10:01 PM 7/04/2005 +1000, you wrote:

> > I probalbly have a transaction that remained open.
> > I'm having trubble finding it though.
> > Do you have any ideas how to approach this problem ?
> > I found all the places at the code that calls setAutoCommit and it
> > looks fine. I also worked with a db after backup/restoring and it is
> > not reproduced(next transaction= 1+snapshot transaction).
> > So its a specific transaction that I need to trace.
> > Any suggestions ?
>
>autocommit is often referred to as commit retaining.

No. Autocommit works in different ways in different
implementations. Delphi happens to use CommitRetaining when it does its
"client-side autocommit".

>Not sure what component
>set you are using but autocommit doesn't smell right to me.
>Are you using java?

If he is using Jaybird, then I'm pretty sure it implements server
autocommit, which is not the same as the post + commit with retain that
Delphi interfaces implement.

In any case, the stats provided don't indicate the kind of problems people
get when they persistently use commit retaining. As Ann noted, there are
long-running snapshot transactions causing uncollectable garbage to back up
in this database, apparently deleted record versions that one or more
snapshot transactions can still "see". GC won't even try to
garbage-collect past a snapshot transaction that is still running.

Start by searching your code for a "select <whatever> from <atable>" that
is in a transaction that has never been committed. If <atable> is the one
that the deletes were performed on, then it is the cause of the garbage dam.

As to a postfix to the problem: there isn't one (other than telling all
users to commit their transactions and log out!!) What you need to do is
find the offending statement and fix your code so that the snapshot
transaction commits on some kind of timer. You can make more "breathing
space" by putting that select statement inside a ReadCommitted transaction
(if that is practicable for your application requirements); but you should
also make sure it gets committed in a timely fashion, so that you don't
create garbage buildup further back up the line.

./hb




_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]