Posted by Jerry Stuckle on 01/23/06 06:46

Jim Michaels wrote:
> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
> news:IbqdnR2rBPUWQVPeRVn-iQ@comcast.com...
>
>
>
> About those times when it's not - like when there's some other UPDATEr out
> there on your rows that you just SELECTed (and you also want to do an
> update, or an external UPDATE would really mess things up) - how can you
> tell MySQL to lock those rows?
>

Jim,

I never said it was ALWAYS a good idea to do this. Rather, I argued
with the statement it is NEVER a good idea. Two entirely different things.

However, since you asked, this happens very often on heavy transaction
systems. It's quite easy.

You buffer the list. If you need to update an item, you fetch THAT ITEM
only a second time. Compare the contents of the just retrieved item to
the saved item. If there's no change, you can update it with impunity.

If there is a change, look at what's changed. If it's a field unrelated
to your current change, go ahead and update it. If it's a field which
will also be changed, you need to make an intelligent decision as to
whether you can update it or need to notify the user of an error.

Take a bank account for example. You get online and wish to move $100
from checking to savings. Meanwhile, your wife is going shopping and
tries to take $50 out of the account at an ATM.

You make the request. The system fetches your current balance and
ensures it is > $100. If it is, it displays a screen asking for you to
confirm this request (if you only have $10 in there, of course it denies
the request).

Now - your internet connection may go down, you may step away a minute
to pour yourself a cup of coffee, whatever. The point is, the system is
waiting for user response. Your account cannot be locked for that
period of time; other actions would be held up (like your wife getting
money or a check being cleared).

Let's say right now your wife makes that $50 withdrawal. Now you tell
the computer to complete your transaction.

But wait - there's less money in your account than when you started. If
the system just subtracted $100 from your earlier request, the $50
withdrawal would be lost (you'd be happy but your bank wouldn't!).

So the program again fetches your balance and compares it to the
original value. But wait - it's changed.

So the system recomputes the balance. If there are sufficient funds in
the account, it processes the withdrawal. But if there aren't, it sends
you an error message.

This is generally how almost all transactional systems work on big
systems. Yes, it's quite a bit more work. But it allows updating
without having to hold locks while someone goes for coffee.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]