locking on a server farm (asp.net)

Question

I would like to know if there are any ways one can perform 'locking' on an asp.net web application which is deployed on multiple servers, using a distributed server like MySQL Cluster.

For example, take the classic example of updating an account balance. No two requests should update the same account balance at the same time. For example:

Member 1 account balance is 100.

• Request A accesses Server 1 to add 100 to the balance of member 1
• Request B accesses Server 2 to add 50 to the balance of member 1

Thus request A updates the balance from 100 to 200 and saves.
Request B updates the balance to 150, and saves.

These all happen exactly at the same time, thus losing the information as the end result should have been 250.

How can one lock such that Request B would have to wait until Request A finishes, until it retrieves the balance. If it was a single process, this could be implemented by an application-wide lock. However, since there are multiple independent processes, this wouldn't work as for Server 1 it is not locked, and neither Server 2

Any ideas or best-practices how this can be done?

Answer 1

That's what transactions are for.

Wrap the operations that need to be atomic in a TransactionScope to enrol them in an explicit transaction.

This doesn't completely resolve the issue of stale data, as once a transaction completes if the other concurrent user is using the old value to update, you still have a problem.

The solution is to use a field on the table that changes whenever the row changes - in SQL Server 2008 this is the rowversion type.

You only update the row if the rowversion has not changed - if it has, you notify the user and bring back the current values without making an update.

Answer 2

In the ORM I created I implemented a locking mechanism. Each type of entity, such as Product, Customer, etc has 2 additional fields: 'LockedBy (int)' and 'LockedAt (datetime)'.

LockedBy contains the ID of the user that locked the entity, and lockedAt contains the timestamp at which it was locked.

So, when a user wants to start editing a entity, the code goes through this process:

Get the entity from DB, '1' being the ProductID

Product p = new Product(1);

Lock the entity to the currently logged in user

entity.Lock();

Save/commit the entity to the database, Save() calls LockCheck() which returns true if the logged in user has permission to save the entity.

entity.Save();

User has permission to save the entity if:

 (LockedBy < 0 || LockedBy == LoggedInUser.UserID || LockedAt < DateTime.Now.AddMinutes(-30))

Note: Users can edit entities which were locked over 30 mins ago. If another user has the entity open (in a editing screen) then every 15 minutes it will pop up and ask if the user is still there, if so, it does a request which increases the LockedAt to Now (like a rolling lock)

At this point no other user can edit this entity!

The code/user can freely edit this entity as much as it wishes and be certain that it hasn't changed behind it's back :)

Once the user has stopped editing the code calls:

entity.Unlock()

then

entity.Save()

this then frees up the entity for other users to edit it

Answer 3

This is where a system like Redis' pub/sub mechanism can help you. It is essentially a way to send a message to subscribers (the other load-balanced servers) to let them know an event has occurred. The ServiceStack Redis client has full support for this.