Transaction ID stuck until database disconnect

[Joe Sansalone]

I noticed something funny. All the transactions in my application perform a
CommitRetaining,
The database components are in a DataModule (I'm using Delphi 6).
The Oldest Active, Interesting and Snapshot Transaction ID don't move
forward. The Next Transaction ID does. The application has lots of
transactions per day (like 500,000) so the difference between Next and
Oldest is a lot.

It's only when I close the application that the Oldest IDs move forward.

Is it because the Database components need to be on the main form?

All help is much appreciated.

[Thomas Steinmaurer]

No, your problem is CommitRetaining. Although it does a commit, it will
retain the current transaction context, thus you end up with a stuck
OIT/OAT.

You have to call a regular, hard commit from time to time.

--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com

[Bill Todd [TeamB]]

Joe Sansalone wrote:

> All the transactions in my application perform a CommitRetaining,

CommitRetaining does not end the current transaction. Therefore it
causes the OAT to stick which causes the OIT to stick which effectively
disables garbage collection and also increases the size of the TIP. The
result is decreasing performance. You must Commit (not CommitRetaining)
you transactions frequently.

--
Bill Todd (TeamB)

[Joe Sansalone]

Thanks!

I got confused when I saw the Next Transaction keep moving forward.
I assumed that meant that the previous transaction was processed.

A related question: If transaction 1000 is not commited and there are
thousands of future committed transactions, does that mean that the database
checks all in between versions, even though they've all been committed
properly? Or does it simply keep version 1000 and the current versions of
the records?

Joe

"Joe Sansalone" <j...@project1.ca> wrote in message
news:488d...@newsgroups.borland.com...

[Bill Todd [TeamB]]

Joe Sansalone wrote:

> A related question: If transaction 1000 is not commited and there
> are thousands of future committed transactions, does that mean that
> the database checks all in between versions, even though they've all
> been committed properly?

Yes. Garbage collection stops with the OIT. Record versions with a
transaction id greater than the OIT will not be garbage collected and
all transactions between the OIT and the next transaction are tracked
on the transaction inventory pages (TIP).

--
Bill Todd (TeamB)

[Joe Sansalone]

Wow.
I thought Interbase would at least ignore all transactions in between
when updating a record because all records in between were committed.
In other words, I thought only current/active record versions were verified
against the current transaction for possible conflict.

Is there really a need to verify all those transactions if they're
committed?
(just my 2 cents)

"Bill Todd [TeamB]" <n...@no.com> wrote in message
news:488f27bf$1...@newsgroups.borland.com...

[Craig Stuntz [TeamB]]

Joe Sansalone wrote:

> Is there really a need to verify all those transactions if they're
> committed?

If you update a record, InterBase does not need to consider previously
committed transactions on that record, unless your transaction
specifies that it should (i.e., you're in a snapshot transaction).
However, InterBase does need to keep the record versions around for the
benefit of the old, still-open transaction. Since that transaction is,
almost by definition, a snapshot transaction (else, it wouldn't have
stuck the OIT), it should not see the committed changes made by more
recent transactions.

So while you're correct that InterBase does not need to consider old
record versions for your transaction, you have to consider the needs of
the other transaction.

--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Useful articles about InterBase development:
http://blogs.teamb.com/craigstuntz/category/21.aspx

[Joe Sansalone]

Ok I get it.

Just curious. Does that mean that if the current transaction ID is the last
one (i.e. highest ID),
and the OIT is 1000 less, even though the TIP contains all in between record
versions (and they
happen to be committed), it will only check with the OIT's record version
before updating the
current one?

"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:488f37da$1...@newsgroups.borland.com...

[Craig Stuntz [TeamB]]

Joe Sansalone wrote:

> Does that mean that if the current transaction ID is the last one
> (i.e. highest ID), and the OIT is 1000 less, even though the TIP
> contains all in between record versions (and they happen to be
> committed), it will only check with the OIT's record version before
> updating the current one?

No.

When InterBase is about to update a record, here is what it does. It
looks at the tip revision of the record and, specifically, examines the
transaction ID on that revision. It then looks up at transaction in
its internal list of transactions (the TIP). If it's not in the list,
then the transaction must have been committed, and the record can be
updated. If the transaction is in the list, then InterBase can see the
status of the transaction. If the transaction is committed, the record
can be updated. If the transaction is active, then there is a
deadlock, and InterBase will report that.

The TIP contains transaction records, not record versions.

--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz

All the great TeamB service you've come to expect plus (New!)
Irish Tin Whistle tips: http://learningtowhistle.blogspot.com