As this is poorly understood by newbies, I will explain.
Firebird / Interbase use a versioning model. This is where a query
for data looks at the last committed data and then through all the
transactions that are still open, in limbo or rolledback.
Putting it another way, every query gets a version / copy of the data
which can be different to the same query run a few seconds later.
The problem really manifests itself when the different queries are
interleaved and an earlier transaction that is still open means that
later committed transactions can not be processed fully.
For instance you could have update a record in an earlier
transaction (but not committed it) and then a later query deletes the
same record and performs a commit. The engine tries to sort these
problems out.
Over time this list can get large so much so that on one of our sites
the system was unusable by lunchtime.
This can be fixed by mending/validating the database. Even then,
some of the transactions remain and require a backup/restore.
Readonly transactions do not contribute to this problem. Note that
both the table/query and the transaction has to be readonly.
WARNING - Do not use the default transaction as it is not readonly
and was the cause of our problems.
Date sent: Wed, 27 Jul 2005 17:02:02 +1200
From: Phil Middlemiss <[EMAIL PROTECTED]>
Organization: MTS Ltd
To: [EMAIL PROTECTED],
NZ Borland Developers Group - Delphi List <[email protected]>
Subject: Re: [DUG] Firebird connections / queries
Copies to:
Send reply to: NZ Borland Developers Group - Delphi List
<[email protected]>
<mailto:[EMAIL PROTECTED]>
<mailto:[EMAIL PROTECTED]>
[ Double-click this line for list subscription options ]
Ah, I was just about to ask if, for SELECT queries, it was preferable to
rollback or to commit. But I guess that answers the question. What is
the long term effect on a server if I keep using rollbacks?
Phil.
Rohit Gupta wrote:
>Paul,
>
>just make sure that you do not leave transactions open.... either
>have readonly ones or commit/rollback as soon as possible. And
>avoid rollback, Firebird seems to treat them as transactions in
>limbo.
>
>From: "Paul Lowman" <[EMAIL PROTECTED]>
>To: "Delphi List" <[email protected]>
>Date sent: Wed, 27 Jul 2005 15:51:54 +1200
>Copies to:
>Subject: [DUG] Firebird connections / queries
>Send reply to: NZ Borland Developers Group - Delphi List
><[email protected]>
> <mailto:[EMAIL PROTECTED]>
> <mailto:[EMAIL PROTECTED]>
>
>[ Double-click this line for list subscription options ]
>
>Hi all
>
>I am using Firebird / dbExpress in an industrial control application and
>wondered about the following points:
>
>Is it OK to open a connection to a database and leave it open for extended
>periods (ie: days) or should I open and close the connection on demand?
>
>Similarly is it OK to prepare queries when the app starts and leave them in
>a prepared state for extended periods?
>
>My instincts tell me that this may be a bad approach ...
>
>Any opinions welcome ...
>
>Cheers
>
>Regards
>
>Paul Lowman
>
>[EMAIL PROTECTED]
>
>_______________________________________________
>Delphi mailing list
>[email protected]
>http://ns3.123.co.nz/mailman/listinfo/delphi
>Regards
>
>Rohit
>
>======================================================================
>CFL - Computer Fanatics Ltd. 21 Barry's Point Road, AKL, New Zealand
>PH (649) 489-2280
>FX (649) 489-2290
>email [EMAIL PROTECTED] or [EMAIL PROTECTED]
>======================================================================
>
>
>_______________________________________________
>Delphi mailing list
>[email protected]
>http://ns3.123.co.nz/mailman/listinfo/delphi
>
>
>
>
>
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
Regards
Rohit
======================================================================
CFL - Computer Fanatics Ltd. 21 Barry's Point Road, AKL, New Zealand
PH (649) 489-2280
FX (649) 489-2290
email [EMAIL PROTECTED] or [EMAIL PROTECTED]
======================================================================
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
