| Subject | RE: [firebird-support] Re: Performance when deleting a lot of records, again |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-04-07T12:40:11Z |
At 10:01 PM 7/04/2005 +1000, you wrote:
implementations. Delphi happens to use CommitRetaining when it does its
"client-side autocommit".
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
> > I probalbly have a transaction that remained open.No. Autocommit works in different ways in different
> > 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.
implementations. Delphi happens to use CommitRetaining when it does its
"client-side autocommit".
>Not sure what componentIf he is using Jaybird, then I'm pretty sure it implements server
>set you are using but autocommit doesn't smell right to me.
>Are you using java?
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