optimistic concurrency with timestamp and typed dataset in asp.net

Question

i'm creating a forum system for my web site with the use of c# and asp.net and for data access i'm using a typed dataset and for UI i'm using mvp pattern. in my database i have stored procedures which i have been added to my dataset. the problem was that the dataset didn't allow me to select the "use optimistic concurrency" check box so i had to implement the optimistic concurrency myself. i've been added a timestamp column to my tables now i have a problem with saving this value for each user! so what state management approach is safe and less costly to be used for saving the timestamp value??

i thought maybe it's best to use session for storing the value but if the user's count goes up i would have a problem with memory resources however i can set the session to use sql server instead of memory but i have no idea how much slower that would be so i can't decide about it.

any help is appreciated guys!

Edit1: yes. i want to track the last modification of row to see if it matches with the one that user has. but i don't know where to save the user's timestamp value.

Answer 1

If you can't use timestamp/rowversion for some reason being it is a forum type application I would guess you track post created/modified date/time? If so this would be an easy thing to check.

UPDATED

SQL Server Mode

The use case for SQL Server Mode is usually a web server farm so that ASP.NET session state persists over multiple servers. This makes it slightly more robust because if a web server becomes unavailable as long as another is available the session can continue normally. SQL Server Mode is not as fast as it is locally unless resources are severely contested because we are querying a database instead of in-memory of the same server (Out of process) or even in the same process (In-process).

MVC Pattern and Session State

If you are using WebFormViewEngine in your application you can use state however a primary tenant of the MVC pattern is to be stateless.

How to track a rowversion/timestamp stateless

To track rowversion in a stateless manner we need to return it to the client so when the call back to the controller action is made it is provided for us.

<%= Html.HiddenFor(Model.VersionId) %>

Which will result in the hidden field in the resulting HTML form.

<input type="hidden" name="VersionId">1</input> 

Is it safe?

Is it safe to store this temporarily on the client, full well knowing that maybe someone could alter the value as part of an attack against your web application.

Let us analyse what we know:

• Microsoft does not recommend that it is incremented manually to prevent duplication of the same rowversion in the same table.
• Is incremented sequentially per table insert/update, not per record. i.e. Record A could have version 1 Record B could have version 2. The next update to Record A will make it 3 not 2. See http://msdn.microsoft.com/en-us/library/ms182776.aspx.

Based on this we would write our application to use rowversion so that we use rowversion to determine if the record has changed between updates for our current request. We will always let the DB increment the rowversion internally. So the store procedure will accept the value for the sake of comparison but not insert or update it in the table/view.

So what are the cases that could occur with rowversion if someone attempts to manipulate it.

1. Client supplied rowversion is less than the rowversion of the DB.
2. Client supplied rowversion is equal to the rowversion of the DB.
3. Client supplied rowversion is greater than the rowversion of the DB.

Lets now break that down.

So if the client rowversion is less than the DB what does that mean?

The client used an old copy of the data when they edited it since it has changed since they last read from the DB. To handle this we can either:

A. Give them an error, reload data and get them to resubmit their changes.

B. Merge their changes into the current copy and ask for confirmation before they resubmit.

C. Override the existing copy in the DB. Well we could do that but since we implemented a concurrency model to handle multiple changes at the same time it may not be the best fit. This leaves us with A and B.

The other option is the client altered the value of the rowversion to be less than the actual DB value. In this case even if they did alter the value of the rowversion does it matter? I would suggest that it doesn't and it should be handled the same as if there was a change simultaneously with another user. Returning options A and B again.

So if the client rowversion is equal to the DB, what does that mean?

The client is editing the version they expected and hit the submit button. To handle this we can either:

A. Accept their changes and continue.

B. Deny the changes and provide a feedback message. It is not likely we want to do this since the whole point it to get data in to the database.

The other option is that the client altered the value of rowversion to be exactly the right version we were expecting, or in all likelihood didn’t modify it at all because we told them what rowversion we were expecting as we do for all submissions. But in the case that they did alter the rowversion and there were simultaneous clients that posted their updates and this is the last update to be submitted.

We can handle this case the same as before A or B. Unfortunately we have no way of knowing that they actually modified the rowversion because it was as expected. However we have already given them permission to make the update to the record. If they shouldn’t update the record then they shouldn’t have permission to do so.

That said, we will still validate all the input for sanity and sanitise it before writing it to the DB.

If this is no good then we need to implement content versioning or an approval process in our application to give us control before we update each record or to allow roll back to the previous version.

So if the client rowversion is greater than the DB, what does this mean?

Well the options are exactly the same as if the rowversion is less than the DB. It was not expected, so either A or B. Again C is likely not an option since it defeats the purpose.

Conclusion

So is it safe for a forum web application? We still need to validate input but if the rowversion is different than expected we have methods to resolve this.

As a final last resort we can also encode or encrypt the rowversion to prevent the sequence from being easily guessed.

So in my opinion yes, but that is ultimately your call.