Wednesday, July 12, 2006
Optimistic Concurrency
Was playing around with data access in .Net 2.0 today and I found something interesting.
The SqlDataSource control in .Net 2.0 gives you the possibility of avoiding db locking while allowing users to not overwrite data in the db using Optimistic Concurrency.
Basically, what they do is add the original values the user loads to the web page they're using to the WHERE clause in the UPDATE statement
example
instead of a simple:
UPDATE table1 SET col1 = @new_value WHERE id = @id
do the following
UPDATE table1 SET col1 = @new_value WHERE id = @id AND col1 = @old_value
That way, if col1 has been changed since you loaded the page, the AND col1 = @old_value will no longer be true and your update will not update any rows.
If you were to do this in a stored procedure, you could get the stored procedure to try to execute the update, then return a status flag back to your application telling it whether or not the UPDATE passed!
The SqlDataSource control in .Net 2.0 gives you the possibility of avoiding db locking while allowing users to not overwrite data in the db using Optimistic Concurrency.
Basically, what they do is add the original values the user loads to the web page they're using to the WHERE clause in the UPDATE statement
example
instead of a simple:
UPDATE table1 SET col1 = @new_value WHERE id = @id
do the following
UPDATE table1 SET col1 = @new_value WHERE id = @id AND col1 = @old_value
That way, if col1 has been changed since you loaded the page, the AND col1 = @old_value will no longer be true and your update will not update any rows.
If you were to do this in a stored procedure, you could get the stored procedure to try to execute the update, then return a status flag back to your application telling it whether or not the UPDATE passed!
# posted by Shane @ 3:17 p.m. 
