[DUG] Interbase connections / queries / transactions

[Richard Vowles]

Your "for instance" does not compute. The multi-generational
architecture of the InterBase platform (I don't know about Firebird,
they are now completely different products with a completely different
codebase) only locks when two transactions are trying to "write" to the
same record. 

In your "for instance", the second transaction would fail because it
would get a write-lock error, which is normal and expected in a database
engine. The MGA property allows transactions that start reading the
tables to not see transactional updates that occur "after" them, thus
allowing InterBase to be used for both general transactional use and
longer running database queries. 

This does cause old versions of records to hang around, but this is what
the sweep is for - to clear those away. The only instance where this
wouldn't work well is on a very busy database where someone started
running a long, long transactions (hours and hours worth), which would
cause the database to attract a lot of old record versions.

> 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.

Can you explain what you mean by this a little more? I don't quite
understand it?

-----Original Message-----
From: delphi-bounces at ns3.123.co.nz [mailto:delphi-bounces at ns3.123.co.nz]
On Behalf Of Rohit Gupta
Sent: Thursday, 28 July 2005 10:23 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] Firebird connections / queries / transactions

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.