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

[Pavel C. <pc...@us...>]

Hi,

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

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

Developers are often confused why rolled back transactions are 
"interesting". Actually, they just fall in "not committed" category, but 
more important are active and limbo trn. While active transactions are 
covered by OAT, limbo trn are not -> hence OIT (limbo trn can become 
committed in future!!!). I don't completely understand why rolled back 
trn are maintained through OIT in trn state table. FB could handle back-
version made by transactions that are not in trn state table as rolled 
back and ignore/gc them, but FB handles all three in the same way.

If I'm not mistaken, IB/FB has an optimisation that allows to advance OIT 
on rollback. Transactions maintain in-memory undo log of all changes 
made, and this log is used to unwind all changes on rollback, so OIT can 
advance (transaction is marked as committed). Transaction is marked as 
rolled back and thus interesting only when server goes up after abnormal 
termination (all active trn are marked as rolled back). So, the rollback 
command or when client dies does not freeze OIT, only server abend does. 
Of course, one can disable trn undo log in trn parameters. I'm also not 
sure if CS and SS behave the same way (but should).

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

True, but that same apply for OIT transactions, because if they are 
limbo, they could be committed manualy.

> The OIT is reset in one of two ways:  a sweep or a backup and restore. 

True, "by the way" garbage collection can't reset OIT. To do that, all 
row versions made by transaction must be removed, and only sweep and gbak 
read whole database and thus GC all those versions.

The big trn state table (due to big difference between OIT and actual 
transaction) only slow down a bit trn start, not the normal work. And of 
course, memory consumption is higher. But the real pain of stalled OIT is 
blocked GC and amount of back-versions that remain in database. If they 
run over from head-row datapage to other datapages, the performance goes 
south pretty quickly.

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

I'd like to add that they are actually handled internaly as pre-
committed. They should be a solution to OIT/ OAT problem caused by use of 
CommitRetaing in 7x24 apps.

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

I think that impact of OIT is almost the same as OAT. Applications that 
update the same records are problem on its own, because they made 
exceptionally long back-version chains due to inefficient GC (we 
discussed that in ib-architect some time ago).

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

What do you mean by intermediate versions ? Many versions of single row 
that are made by one transaction ? I think that the bookkeeping is 
already there as undo log. It's primarily used to undo the changes made 
by procedures and triggers, but also at transaction level on rollback if 
undo log is not disabled (if it's disabled, it's still there for proc and 
triggers).

Best regards
Pavel Cisar
http://www.ibphoenix.com
For all your upto date Firebird and
InterBase information