On Thursday, January 13, 2005, at 11:25PM, Adam <s3057043@...> wrote:

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

Hi Adam,

Thank you for the lengthy reply, appreciate it. BTW I am asking questions because I am wanting to learn. I plead ignorance to let it be known I'm a newbie and avoid any flames. :-)

Cheers,
Diego

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