Re: Re[6]: [Firebird-devel] Max transaction duration

[<psc...@in...>]

Ann:

As always, when this group gets hot under the collar over an issue, you have the 
sensible answer to the real problem.

On 13 Dec 2002 at 19:01, Ann W. Harrison wrote:

> At 05:00 PM 12/13/2002 -0500, psc...@in... wrote:
> 
> >I wonder if we are looking at the whole thing from the wrong 
> >perspective.  A stalled
> >OIT/OAT causes the engines performance to degrade; ...  if we could
> >find a way that stalling the OIT/OAT doesn't degrade performance, or
> >at least not as quickly then it wouldn't be an issue.
> 
> This is probably a good time for me to explain OAT, OIT, and their
> impact on performance, again.
> 
> The OIT, oldest interesting transaction, is the first transaction that
> did not commit.  It could be active, but more likely it rolled back or
> died and was rolled back.  The reason it is "interesting" is that
> other transactions must ignore or undo its changes.  Firebird creates
> an array of bits (two bits per transaction) that represents the states
> of all transactions between the current and the oldest interesting. 
> When that array gets huge, it can affect performance, but not a lot. 
> To reduce the impact of the oldest interesting transaction, all
> read-only transactions commit, regardless of what their client
> requested.  The OIT is reset in one of two ways:  a sweep or a backup
> and restore.

I guess this is more of a maintenance issue, if you do a backup and sweep every 
day, then the in most cases the OIT array will never get large enough to cause a 
problem.  
 
> The OAT, oldest active transaction, is the oldest transaction that was
> active when any currently active transaction started.  Record versions
> created by the OAT and subsequent transactions can not be garbage
> collected because they might be needed to provide a consistent view
> for an active transaction.   Read-only read-committed transactions do
> not affect the OAT - they can go on forever without affecting garbage
> collection.  In this case, unlike the OIT, the transaction must be
> declared as read-only, not just be de-facto read-only.  The OIT is
> reset whenever the server is restarted and whenever there are no
> active connections.
>
> The performance impact of the OIT, especially in the SS is probably
> not significant.  The impact of the OAT is quite significant,
> particularly in applications that repeatedly update the same records. 
> That effect could be reduced by some bookkeeping that told the server
> which record versions were important so intermediate versions could be
> removed.  At the moment, it seems to me that the cost of that
> bookkeeping and the complexity of removing intermediate record
> versions is not worth the cost and risk.

So a 17 hour month end processing program, that used a Read Only read 
committed transaction in it's main loop and then creates another very short running 
transaction that does the insert or update followed by a commit every few records or 
even every record, would have little OAT related  performance issues.  Since the 
OAT would not be held up by the long running (read only) transaction.

Paul