NGWS SDK Documentation  

This is preliminary documentation and subject to change.
To comment on this topic, please send us email at ngwssdk@microsoft.com. Thanks!

Optimistic Concurrency

There are two basic way to do update to data within a database: Optimistic and Pessimistic Concurrency.

Pessimistic concurrency is a system of locks that prevents users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, other users can't perform actions that would conflict with the lock until the lock owner releases it. This model is mostly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

Thus in a pessimistic model, when a user reads a record with the intention of changing it, they establish a lock. Until that user has finished that update and released that lock, no one else can change that record.

With Optimistic concurrency, users do not lock data when read it. When a user wants to update a record, it is the application's responsibility to check if another user has changed the record since it was read. Optimistic concurrency is generally used in environments with a low contention for data.

In an optimistic model, there are traditionally three ways of identifying changes made to the record. The first is to simply compare all of the column values that were read from the database to the current values.

For example:

1:00 PM. User1 reads a row from the database with the following values:

CustID LastNameFirstName

101SmithBob

Column Name Original Value Current Value Value in DB
CustID 101 101 101
LastName Smith Smith Smith
FirstName Bob Bob Bob

1:01 PM. User2 reads the same row

1:03 PM. User2 changes the first name from "Bob" to "Robert" and updates the database.

Column Name Original Value Current Value Value in DB
CustID 101 101 101
LastName Smith Smith Smith
FirstName Bob Robert Bob

Since the values in the database at the time of update match the original values that user2 has, the update succeeds.

1:05 PM. User1 changes Bob's first name to "James" and tries to update the row.

Column Name Original Value Current Value Value in DB
CustID 101 101 101
LastName Smith Smith Smith
FirstName Bob James Robert

Since the original value for user1 is "Bob," and the value in since user2's update is "Robert," user1's update fails.

The second and third ways of performing optimistic concurrency are actually very similar: either use a date-time stamp column, or a row version column. Whenever a user successfully updates a row, the date-time stamp (or row version) column is set to a new, unique value.

Thus, wherever a user attempts to update a row, the application compares the date-time stamp (or row version) value between the original and the current version in the database. If the values are identical, the row hasn't changed since it was read, and the update can proceed (resulting in a new value for the date-time or row version column).

To perform this task with SQL syntax is simple –

Update Table1 set x = newx1, y = newy where datetimestamp = originaldatetime

Or

Update Table1 set x = newx1, y = newy where rowversion = originalrowversion

The other option is more performance oriented: setup a stored procedure to handle updates.

To keep the datetimestamp/version accurate, set up a trigger on the table to update their values anytime a change to a row occurs.

So how do we do it? VS7 and the DataSetCommand Configuration Wizard (DCW) provides automatic support for this if you use it to create stored procedures. The example below represents an update that the DCW generated for the authors table of the pubs database. You will notice that the parameter list of this stored procedure actually contains two sets of parameters. The second set (@param1 - @param9) is simply a copy of the first 9 parameters.

CREATE PROCEDURE AuthorUpdate
(
   @au_id id,
   @au_lname varchar(40),
   @au_fname varchar(20),
   @phone char(12),
   @address varchar(40),
   @city varchar(20),
   @state char(2),
   @zip char(5),
   @contract bit,
   @Param1 id,
   @Param2 varchar(40) /* Optimistic Concurrency Check */,
   @Param3 varchar(20) /* Optimistic Concurrency Check */,
   @Param4 varchar(40) /* Optimistic Concurrency Check */,
   @Param5 varchar(20) /* Optimistic Concurrency Check */,
   @Param6 bit /* Optimistic Concurrency Check */,
   @Param7 char(12) /* Optimistic Concurrency Check */,
   @Param8 char(2) /* Optimistic Concurrency Check */,
   @Param9 char(5) /* Optimistic Concurrency Check */
)
AS
   SET NOCOUNT OFF;
   UPDATE authors SET au_id = @au_id, au_lname = @au_lname, au_fname = @au_fname, phone = @phone, address = @address, city = @city, state = @state, zip = @zip, contract = @contract WHERE (au_id = @Param1) AND (address = @Param2) AND (au_fname = @Param3) AND (au_lname = @Param4) AND (city = @Param5) AND (contract = @Param6) AND (phone = @Param7) AND (state = @Param8) AND (zip = @Param9);

The second set of parameters holds the original values of the fields as they were loaded from the data store. This is used to perform the comparison in the "WHERE" clause of the update statement.

When we loaded the DataSet, the Authors record looked like:

   Au_id = "999-99-9999"
   Au_Lname = "Smith"
   Au_Fname = "Jane"
   Phone = "111 111-1111"
   Address = "123 Main"
   City = "Redmond"
   State = "WA"
   Zip = "98052"
   Contract = 1

Our update changed the phone number to "222 111-1111." However, prior to our update, but after we loaded it, another user else changed Jane's last name from "Smith" to "Doe." When we execute our update, no records will be affected because the au_lname column no longer matches the original value.

Thus in our form code or business object we can check the number of rows affected to see if our transaction succeeded or failed because no matching record was found.