Hi Lester,

Please read below...

> I have 3 queries with update objects a stored procedure. All of them

are

> part of the same transaction. Everything is set to Read Committed and
> Lock Wait. Once in a while, when the transaction is committed
> (retaining), all the queries get updated, but the stored procedure

does

> not complete the client balance update.
>
> The reason I am using the Stored Procedure to update the client

balance

> is because this is a multi-user application and the same client record
> may be updated between the time one user starts and completes a
> transaction. So, in the SP I alter the balance based on what the DB

says

> is the client's current balance.

This sounds like a candidate for a trigger rather than a
Stored Procedure, but it sounds as if you are expecting the
Stored Procedure to do something that it will not - pick up
data from another transaction?

[GWM] I am preparing the stored procedure and assigning the value of the
balance change to the Param of the procedure. And, this SP is using the
exact same transaction as the queries.

> Is there anything wrong with the IBO 4.2g? Does anyone have any ideas,
> because this is obviously a huge problem and I can't let it go

unfixed.

I don't think this is a problem with IBO, but rather your
logic. If two users are both posting updates to the one
record, you have a problem. User 1 will get a ballance, and
add or subtract from it. User 2 will see the same original
ballance until User 1 commits a change. Then User 2 needs to
know that they can or cannot proceed if there are
insufficient funds, so they need to be refreshed or stopped
from doing their update. Is the Stored Procedure managing to
bypass this checking, and so User 2 makes a change further
change to the ballance based on the uncommited starting
point, rather than the later User 1 resulting ballance?

[GWM] I don't understand what you are saying. What you have indicated is
exactly why I am using the SP. I am simply sending the balance change to
it, and the server should be taking the client's current balance and
applying the change to it...regardless of how many people are sending
balance changes at the same time. What I didn't want, was to load the
balance with a query and change it based on the value at the time is was
loaded. That, to me, would create the scenario you are describing.

[GWM] I guess what I don't understand is why all aspects of the query is
committing except for the SP. And, remember, this happens very rarely,
but it does happen and I need to solve it.

[GWM] I greatly appreciate your input and further assistance.


--
Lester Caine
-----------------------------
L.S.Caine Electronic Services


________________________________________________________________________
___
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.
________________________________________________________________________
___
http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
keyword-searchable FAQ, community code contributions and more !


Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/