Jerry,
Going back to your original description...

> Everything is set to Read Committed and Lock Wait.

[snip]

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

What happens is that your SP performs the update operation if it
can...because you have the lock WAIT set, you have set up a "livelock"
situation. If the SP goes to post the change to the balance and gets a
lock conflict, it will wait until the contending transaction completes
before trying again. By that time, if the contending transaction was
successful, your transaction's delta will be out of date and the post will
fail. If the other transaction failed, then both transactions are waiting
for ever for the other to finish and neither ever completes. At this
point, each separate "surrounding" transaction can't complete. Commit
won't work, because the posting of the balance change didn't succeed: so
the only solution is to end the transaction by rolling back and to start
again from the top.

Notice that the failure of the post on the server does *not* cause rollback
to occur. Likewise, failure of the commit call from the client does not
magically cause rollback to occur instead. You must explicitly test the
outcomes, including any error or lock messages that might be returned by
the SP call and commit or roll back accordingly.

There are very few situations where a WAIT lock control setting is
appropriate - and this certainly is not one of them. You have engineered a
bottleneck operation into your app here so your transaction settings must
enable it to either commit or rollback immediately and your app must handle
the case where rollback is necessary.

Don't be deluded into thinking that your Read Committed isolation level
will make every update to the table carrying the client balance "fresh and
up to the minute". The update operation will see the latest committed
version of the row but it won't see any updates that another transaction is
about to post . In the gap between reading the old version and posting the
update, the record version on which your transaction performs its update
may well become out of date and the post will fail accordingly; or post
may succeed but commit fails. It is just fallacious to assume because your
SP was instructed to do some work that it must have succeeded, or even that
it is first in the queue to post changes.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com