How to Create Optimistic Locking in C#

What is Optimistic Locking

In a multi-user environment, different users can open a record for editing at the same time. Now, if one user does an update to the record, then when the second user tries to do his update - the second user should get the message that the record has been already updated by another user. So he need to re-fetch the correct record details and then do his update once more.

This process is known as Optimistic Locking, and is very helpful in CRUD Operations that are taking place in multi-user environment.

How to Implement Optimistic Locking in C

To implement an optimistic locking in C#, you need to create a new column of data type ‘TimeStamp’ in SQL Server. Time stamp automatically generates a unique binary number every time you update the SQL Server data.

So when you read a record, you need to store the old ‘TimeStamp’ value in your web page. You can store it in – session variable, hidden field, viewstate or a cookie.

Then when you update the record, probably through a Stored Procedure, just pass that ‘TimeStamp’ value to your Stored Procedure.

In this stored procedure you check this ‘TimeStamp’ value with the timestamp value that is there in the table for that record.

If both the values matches, then you are sure the record is un-edited, and you can go forward with your update thing.

If the values do not match, then you know the record is edited by other person, and so you can send a message back (through an output parameter) telling the user to re-fetches the record’s value from the table, and starts the update process once again.

Optimistic Locking Stored Procedure

Suppose you have to implement the Optimistic Locking on Student’s Table and the TimeStamp column name is ‘MyTimeStamp’.

The Update Stored Procedure will be:

ALTER PROCEDURE [dbo].[sp_UpdateStudent]   
  @Id	   	INT,
  @Name		VARCHAR(50),   
  @Address	VARCHAR(100),   
  @Age		INT,
  @Standard   	VARCHAR(10),
  @MyTimeStamp	TIMESTAMP,
  @Result	VARCHAR(50) OUTPUT    
AS            
BEGIN
    IF(@MyTimeStamp = (SELECT Age from Student WHERE Id=@Id))
  BEGIN
      UPDATE Student SET Name=@Name,Address=@Address,Age=@Age,Standard=@Standard WHERE Id=@Id 	
      SET @Result='Update Successful'
  END
  ELSE
      SET @Result='Record Edited by Somebody else'
  END

Conclusion

I hope you like this trick of Optimistic Locking and don't forget to implement it in your projects. If you have any suggestions then do use the comments section below.