Subject Re: [firebird-support] Sweep Interval
Author Helen Borrie
At 01:25 PM 12/01/2004 +1100, you wrote:
>Hi Helen,
>UPS!, I think I am begining to undestand a few problems here.
>1) I am using autocommit, and in the App the is no Begin Transaction and
>Commit at all. If I understood your comments, I MUST execute a "Non Soft
>Commit", wich in SQL would be: "CommitRetaining"????

OK, let's get this in context. I know that Sandeep is using Delphi, and
Delphi's AutoCommit is a "simultaneous" Post +
CommitRetaining. "CommitRetaining" is a Delphi method, encapsulating the
COMMIT WITH RETAIN statement of Firebird/InterBase SQL. This is a "soft"
commit that I believe Borland introduced for the benefit of Delphi
programmers. It really doesn't make sense in terms of either MGA or atomic
transactions, IMPO, but it's there and people use it. The engine's
transaction accounting does not register a transaction as "not interesting"
until it has been hard-committed. A "hard" commit is "COMMIT".

Your Autocommit takes care of both Post and CommitRetaining. Most Delphi
components perform START TRANSACTION for you if you don't call
StartTransaction yourself. In IBO (don't know about all of the others), if
you explicitly start a transaction, then Autocommit is disabled and you
must take care of Post and Commit (or CommitRetaining, if you must do it).

>2) Every user that is conected to the database, and is reading some tables,
>is considered an OPEN transaction for the Database until he/she closes the

Not exactly the right way to envisage it. A client can be connected to the
database with no data transactions started. This is a transaction between
the client and the database that lasts until the connection is closed. It
doesn't affect the *data* at all and is not considered in GC reckonings.

However, ALL things between a client and the server occur inside
transactions. So - if you are a user looking at the output from a query,
then that query is part of a transaction. Lots of queries can be open in
lots of transactions. If a transaction exists, it is "open", by your
terms. But you don't "open" and "close" transactions - you START them and
then, when you are ready, you COMMIT them or ROLLBACK.

Transactions can be read-write or read-only. Read-write is the
default. That means you have to make a transaction read-only explicitly,
otherwise it will be read-write. That goes regardless of whether it's a
SELECT query or a DML query. All read-write transactions are "interesting"
from the point of view of GC.


It usually does. :-))

Errrm, would you please TRIM your replies? thks