Hi Viv.
Thats eactly what I wanted to hear.
I dont expect it to be the same as I will be just adding to it, so that shoukd work fine
Many thanks for your assistance
Regards,
Gerard




>AFAIK the operation is atomic and thus safe.
>As long as you are happy accepting that the value of 'Balance' at the time this is executed is not neccessarily the same as it was when initially retrieved by the user then there's no problem.
>Regards,
>Viv
>
>
>>So if I use a command like:
>>.... Update Inventory Set Balance = Balance + 5 where Code = X
>>
>>Can I be sure that the balance will ALWAYS end up as the Balance + 5 immediately after ?
>>i.e. I dont need to spefically apply any lock ?
>>
>>I want to ensure that internally, Sql does not take the Current Balance, and before I have it updated, somebody else comes in and
>> can take the balance .
>>i.e. while I am executing Update Inventory Set Balance = Balance + 5 where Code = X , can anoher user execute a similar command and grab the same inventory figure before I have finished .
>>
>>What I am really asking is there an Implict record Lock for the command above , or do I need to apply explicit locking
>>
>>Gerard
>>
>>
>>
>>
>>
>>>>>>Hi Viv, thanks for replying.
>>>>>>
>>>>>>....User A : Update Inventory Set Balance = Balance +5
>>>>>>....User B: Update Inventory Set Balance = Balance +7
>>>>>>
>>>>>>Is there a Sql Command like---> Update Inventory Set Balance = Balance +5
>>>>>> which replaces whatever Balance is in Sql with the Balance + 5
>>>>>
>>>>>That is an SQL Command, but you'd want to limit the scope rather than apply it to all records:
>>>>>UPDATE Inventory SET Balance = Balance + 5 WHERE InventoryID=x
>>>>>
>>>>>
>>>>>>If there is, I think that would work fine
>>>>>>I have only ever used a sSql Update as:
>>>>>>Update Inventroy Set Balance = X which replaces the Balance with X
>>>>>>
>>>>>>But in my Case, Say I am User B, when i Initially took the Balance it was 100, and now I am replacing it with 100 + 7
>>>>>> but User A has already increased it to 100 + 5 = 105
>>>>>>
>>>>>>Regards,
>>>>>>Gerard
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>I have following scenario and wonder how to handle it in Sql (In VFP I would have used FLOCK)
>>>>>>>>
>>>>>>>>Inventory Balance = 100
>>>>>>>>
>>>>>>>>User A Selects Out from Inventory Table
>>>>>>>>Uses the 100, Does 'stuff' with Inventory Balance whcih Takes 5 Minutes, and needs to add 5 to the 'Curent ' Balance
>>>>>>>>(If I use a Sql Update Balance woukd be changed to 105 (100 + 5)
>>>>>>>>
>>>>>>>>User B Selects out from Inventory Table at same time as user A (Balance= 100)
>>>>>>>>Uses the 100, Does 'stuff' with Inventory which Takes 7 Minutes, and needs to add 7 to the Balance () (whatever it happens to be now
>>>>>>>>(If I use a Sql Update , Balance would be changed to 107 (100 + 7)
>>>>>>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>>>>>>>
>>>>>>>>What I really want the new balance to be is 100 + 5(User A) + 7 (User B) i.e. 112
>>>>>>>>
>>>>>>>>Do I still use some form of Locking or is there 'some other way' to do this in SQL
>>>>>>>
>>>>>>>In the situation you describe I don't see the problem:
>>>>>>>User A : Update Inventory Set Balance = Balance +5
>>>>>>>User B: Update Inventory Set Balance = Balance +7
>>>>>>>
>>>>>>>I think what you are really worried about is the scenario where they both want, for example, to deduct 80 from the Balance?
>>>>>>>In that case you proabably have to make a value judgement as to how often this could occur. If rarely then either check that the backend balance is still the same as when the user retrieved it. If it is not then you have to decide whether the transaction is still valid or should be rejected.
>>>>>>>
>>>>>>>I'm no SQL expert but I don't think there is any equivalent of VFPs RLOCK() (I assume you would use that rather then FLOCK()) and the 'roll your own' ideas I've seen look cumbersome, risky and best avoided.....
>>>>
>>>>http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx
>>>
>>>Aren't these locks that are automaticallly applied within a transaction - would keeping a transaction open for five or more minutes be a good idea?