Hi Diego,

The behaviour you encountered is normal and good. Let me explain.

Firebird stores multi version records. Until your transaction
commits, it behaves like it doesn't exist to other transactions. In a
nutshell, your queries will see the data as it was at the point you
begin your transaction, and no other transactions will see your
changes until you commit.

Say Customer 1 wanted to pay customer 2 $100.

You would run the following query:

(1) update accounttrans
set balance = balance - 100
where customerid = 1

(2) update accounttrans
set balance = balance + 100
where customerid = 2

(3) commit

Imagine another program wanted to get the balance of each customer
and ran the following query

select customerid, balance
from accounttrans
where customerid in (1,2)

If this executed at between position 1 and 2 (before the 2nd query),
and firebird did not have multi-version records, the sum of the
accounts balance would be $100 different to positions 1 or 3. But
until the commit is run at number 3, the balance of customer 1 will
still appear to have the $100. It is a really powerful feature to
have, as your database is always in a consistent state and you don't
have to use a record or a table lock to enforce it.

There is one issue when you have multiversion records. When you have
two separate transactions that try to update the same record, there
is a problem. The way delphi handles it by default is to force the
second transaction to wait for the first transaction to commit or
rollback. If the first transaction commits, then the second
transaction raises an exception. If the first transaction rollback,
then the second transaction continues as normal. Read committed is
basically a bit more pessimistic. It says that if transaction 2 tries
to update a record that has been updated by transaction 1, you
immediately get an exception in transaction 2.

The advantage of the default (Snapshot i think it is called) method
is that your query in the second transaction is more likely to
suceed, because there is a chance that the first transaction is
rolled back. The disadvantage is that your application will
effectively freeze until the other transaction commits or rolls back
unless you are using a different thread. The advantage of read
committed is that it suceeds or fails quickly. The disadvantage is
that if transaction 2 were to hang around a bit longer, it may have
been successful.

btw, Ignorance is not wanting to learn. Asking questions in order to
learn something is not ignorance but the first step to competance.

Adam




--- In firebird-support@yahoogroups.com, Diego Barros <dbarros@m...>
wrote:

> Hi all,
>
> I am a newbie when it comes to transactions, so please excuse my

ignorance when it comes to transactions.

>
> I am using FB 1.5.2, with Delphi 7 and the IB components. My

question is, why when I insert a record into a table don't I see it
in another running program?

>
> I have a small test application with a query which selects a count

of records from a table. On a form I show this number when I click
the Refresh button. I have another button which inserts a new record
into the table. So my form has the following components:

>
> IBQuery1 - which does a SELECT COUNT(*) on the table
> IBQuery2 - which inserts a record
> IBDatabase - used by both queries
> IBTransaction - used by the IBQuery1, IBQuery2 and IBDatabase

components

>
> When I click the Refresh button I execute the following code:
>
> IBQuery1.Close();
> IBQuery1.Open();
>
> When I click the Insert button I execute the following code:
>
> IBQuery2.ExecSQL;
>
> I then run two copies of this test program. I insert a record in

one instance of the program, click refresh and I see the record count
increase. I click Refresh on the other instance of the application
and the count does not change.

>
> Can this behavior be changed? I changed the transaction component

to be read-commited, but that did not change the behavior. Maybe this
is all about a different mindset and how one works with IB/FB? Are
there any articles which talk about this? Maybe I need to try other
components which work differently from a programming point of view?

>
> Any help or tips would be greatly appreciated by the FB newbie.
>
> Regards,
> Diego