[DUG] Firebird connections / queries / transactions

[Rohit Gupta]

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 <phil at tumonz.co.nz>
Organization:   	MTS Ltd
To:             	rohit at cfl.co.nz,
  	NZ Borland Developers Group - Delphi List <delphi at ns3.123.co.nz>
Subject:        	Re: [DUG] Firebird connections / queries
Copies to:      	
Send reply to:  	NZ Borland Developers Group - Delphi List <delphi at ns3.123.co.nz>
	<mailto:delphi-request at ns3.123.co.nz?subject=unsubscribe>
	<mailto:delphi-request at ns3.123.co.nz?subject=subscribe>

[ 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" <paul_lowman at xtra.co.nz>
>To:             	"Delphi List" <delphi at delphi.org.nz>
>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 <delphi at ns3.123.co.nz>
>	<mailto:delphi-request at ns3.123.co.nz?subject=unsubscribe>
>	<mailto:delphi-request at ns3.123.co.nz?subject=subscribe>
>
>[ 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
>
>paul_lowman at xtra.co.nz
>
>_______________________________________________
>Delphi mailing list
>Delphi at ns3.123.co.nz
>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 rohit at cfl.co.nz  or  r.gupta at xtra.co.nz
>======================================================================
>
>
>_______________________________________________
>Delphi mailing list
>Delphi at ns3.123.co.nz
>http://ns3.123.co.nz/mailman/listinfo/delphi
>
>
>
>  
>
_______________________________________________
Delphi mailing list
Delphi at ns3.123.co.nz
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 rohit at cfl.co.nz  or  r.gupta at xtra.co.nz
======================================================================