Subject Re: [firebird-support] How to "lock" a record
Author Helen Borrie
At 08:22 AM 5/11/2004 -0300, you wrote:

[more big snip]

> >
> >Then there is the client-side AutoCommit that was invented by Borland to
> >support its implementation of the Delphi data access model. This is the
> >one that causes the problems with the TSB. Cosmetically, it has the same
> >effect as ServerAutoCommit, i.e. executes and commits a statement in one
> hit.
> >
> >However, it uses a "soft commit", a.k.a COMMIT WITH RETAIN, or
> >CommitRetaining. It has the extra "bonus" (necessary for Delphi) of being
> >available for selects. However, although this "soft commit" does commit
> >the work (makes the changes visible to other transactions) it holds the
> >database cursors of your SELECT statement open and puts a block on
> >post-commit cleanup. So - instead of the TSB being maintained in a smooth,
> >roughly FIFO manner, new transactions just keep getting crammed into the
> >TSB and never getting cleared out. These long-running read-write
> >transactions, that are continually committed with CommitRetaining, cause
> >cleanup to get "stuck" at these much lower (= older) transaction IDs.
> >
> >
>I guess you are talking about BDE model rigth? Im using FIB+ and i can
>use transaction in the rigth way.

Sure, and you could actually use the BDE model to explicitly control the
transaction, as well. However, the BDE, IBX, the TDataset series of IBO,
UIB, and, possibly also FIBPlus, all set AutoCommit as the default. It is
just very common to see Delphi and BCPPB applications that blindly go with
this default and never call Commit on any transaction with a SELECT
statement within it.

>Just to clarify to new users. Using Delphi you can use FB transactions
>in the right way with the proper tools.

To clarify even more, you can use FB transactions in the appropriate way,
even with the standard tools. The important thing is to understand which
is the appropriate way. Using CommitRetaining is not a mortal sin (though
my esteemed colleague Ann Harrison might dispute that!). For the types of
applications that a lot of people write with Delphi, CommitRetaining does
save a lot of overhead, both on the client and over the wire. Its use
should be tempered with adequate attention to performing those hard commits
at reasonable intervals.

To determine what is "reasonable", at least set up tests where you are
monitoring the transaction statistics and seeing those Oldest and Oldest
Active transaction ids moving steadily forward.