I need to update a datastore entity in a way that will not be broken by multiple concurrent users doing the same thing.
I understand that I can't use SQL for updating the datastore but I'm not sure what else would work.
This is how I would achieve it in an RDBMS using SQL:
-- Account.Balance = current balance
-- Account.Rate = increase per second
-- Account.CheckDate = the last time the balance was checked and updated
-- so we need to find the number of seconds since the last check,
-- update the balance by rate*seconds, then update the check datetime
UPDATE Account
SET Account.Balance = Account.Balance + ( DATEDIFF(S, GETDATE(), Account.CheckDate) * Account.Rate),
Account.CheckDate = GETDATE()
I know that I can wrap all the operations in a single transaction, but how can I ensure that the update is not miscalculated because of multiple users without using a single update operation like the SQL shown?
You can probably see that several operations like:
1. Read entity
2. Update values
3. Save entity
might fail because of several users doing the same thing
I'm guessing there are several possible ways to achieve this and I'm looking for the one which would work best for this and future requirements.
================== ANSWER ==================
When I got to this point I realised that as long as I update the balance and the check date at the same time, all will be fine. Concurrent updates will not break anything :)
But I thought I'd post it anyway!
I'm still happy to see better solutions though...
