All,

I don't have any great knowlege of the internals, but I was under the same
impression as Raymond about how IB/Firebird's versioning system should
prevent updating records that have been updated by other users. I tracked
down the original article, by Borland's Bill Todd, that led me to believe
this. It is in full at:

http://community.borland.com/article/0,1410,27007,00.html

However, I have excerpted part of the relevent example from this article
below. Since this is part of Borland's arguments that this is something
that InterBase does, I believe the capability (to verify versions) should
be there. Does this only apply to "consistent" and/or "concurrent"
transactions? I understand that Lester has created his own system for
managing this situation, but Borland (at least) is advertising this
capability as part of the database engine itself. How do we get this
behavior from it with IBO? Do other tools surface this capability? If it
isn't possible, why not? Is Boland lying?

---------------------------------

Consider the case where a husband and wife go to two different ATM’s at the
same time to withdraw money from their checking account. With no
concurrency control the following sequence of events can occur.

John reads the balance of the account which is $1,000..

Jane reads the balance of the account which is still $1,000.

John posts a $700 withdrawal.

Jane posts a $500 withdrawal.

At this point the account balance is -$200 and the bank is not happy. This
happened because with no concurrency control mechanism John’s update is
lost as far as Jane is concerned. She never sees the change in account
balance. Under the locking model:

John reads the balance of the account causing a read lock.
Jane reads the balance of the account causing a read lock.
John posts his withdrawal attempting a write lock which fails due to Jane’s
read lock.
Jane posts her withdrawal attempting a write lock which fails due to John’s
read lock.

A deadlock now exists. Hopefully the database software will detect the
deadlock and rollback one of the transactions.

Under the versioning model:

John reads the balance of the account.
Jane reads the balance of the account
John posts his withdrawal which causes a new version with new balance to be
written.
Jane posts her withdrawal but is rolled back when the newer version is
detected.

---------------------------------

--Rhett Rodewald

>> Firebird and Interbase are aware of which record version is being
>> updated,
>> and IMO IBObjects should not permit it to happen.
>
> They are? I'm not sure that is quite the case. They know that copies are
> locked, and that other locks cause a deadlock, but I don't believe that
> 'currently' the engine is quite as clever as knowing which 'unlocked'
> data is being modified. I may be wrong - of cause.
>
>> This occurs whether RecVersion = True or False and even if
>> PessimisticLocking = True.
>
> I have not looked at RecVersion, as I said before, I control VERSIONING
> myself with update timestamps.
>
>> I believe that IBObjects should determine if the record version is later
>> than a previously committed version of the record and generate an
>> exception,
>> or respond to an exception generated by Firebird.
> >
>> One way to implement this would be to check the transaction number and
>> compare it with the current transaction number.
>
> One for others to comment on - I'm still using methods from pre-IB/FB
> days ;)
>
>> Another way would be to read the last committed record and check it
>> against the values that were read when the current transaction began.
>
> That would be cumbersome, I just check the timestamp of the record.
> The one thing to bear in mind here is "Does it slow down processing for
> users who do not need it". The current capabilites are designed for speed
> and stability. Adding another layer of functionality is easy, and most
> things can be catered for.
>
> I can see exectly where you are coming from - I've been there myself, and
> have a solution that works for me - and can see what you are proposing
> could have been useful - but I am not able to comment on any problems
> with your solution.
>
> -- Lester Caine
> -----------------------------
> L.S.Caine Electronic Services
>