Optimistic Database Concurrent processing policy

1. Include Only the Primary Key Columns, Last in win

2. Include All Columns in the WHERE Clause

3. Include the Primary Key and Timestamp Columns (Recommended)
   4.Include the Primary Key Columns and Modified Columns

Include Only the Primary Key Columns

"Last in wins"

Here's a quick breakdown of the scenario:

User A fetches the row.

User B fetches the row.

User B modifies the row and successfully submits the changes.

User A modifies the row and successfully submits the changes, overwriting the changes that User B just submitted.

The CommandBuilder object does not offer this optimistic concurrency option; the Data Adapter Configuration Wizard does. On the Advanced Options tab, deselect the Use Optimistic Concurrency check box.

Because the value of the ContactName column for this row of data has changed in the database, no row in the table satisfies all the criteria in the query's WHERE clause. thus, the database does not modify the customer row. the DataAdapter queries the database to determine how does rows the query modified, discovers that the query did not successfully update the desired row, and marks the DataRow accordingly. we'll discuss identifying and resolving such conflicts in Chapter 11.

This is the concurrency option that the CommandBuilder object uses. The Data Adapter Configuration Wizard uses this concurrency option by default.

Include the Primary Key and Timestamp Columns

You can define a timestamp column on your SQL Server table, and any time the contents of a row changes, SQL Server will modify the value of the timestamp column for that row. we can add a timestamp column to the MERs table and change the query in the previous example to look like this:

UPDATE MERs

SET CustomerID = 'abcde', CompanyName = 'original Company name ',

ContactName = 'new Contact ', Phone = '2017-555-1212'

WHERE CustomerID = 'abcde' AND

TimestampColumn = 0x00000000000000CC

Because the server will generate a new value for the timestamp column each time it updates a row, you can use a combination of the primary key and timestamp columns in the WHERE clause of your query-based updates to ensure that you don't overwrite another user's changes.

Most database systems support a similar data type. some use a unique binary value, and others use a date/time value. check your database system's documentation to determine the back end's data type and learn how you can force the database to update the value each time you modify the contents of a row.

Currently, neither the CommandBuilder nor the Data Adapter Configuration Wizard supports generating updating logic using this optimistic concurrency strategy.

I prefer using the primary key and timestamp columns in my concurrency checks because this option yields much simpler updating logic and the database has fewer columns to examine per update attempt.

As of SQL Server 2000, rowversion is synonymous with the timestamp data type. the SQL Server documentation recommends using the rowversion keyword instead of timestamp. I 've used the term timestamp in this book because, as of this writing, it is more widely recognized.

Include the Primary Key Columns and Modified Columns

Let's look at our multi-user example using this updating strategy. let's say that User A and User B retrieve the same row of customer data at the same time. they each modify a different column of data-User A changes the CompanyName column, and User B changes the ContactName column. user B submits the pending change to the ContactName column first. user B's UPDATE query looks like this:

UPDATE MERs

SET ContactName = 'new Contact'

WHERE CustomerID = 'abcde' AND

ContactName = 'original Contact'

User A then submits the pending change to the CompanyName column using the following UPDATE query:

UPDATE MERs

SET CompanyName = 'new Company name'

WHERE CustomerID = 'abcde' AND

CompanyName = 'original Company name'

The contents of the row will change from

CustomerID CompanyName ContactName

-----------------------------------------------

ABCDE Original Company Name Original Contact

To

CustomerID CompanyName ContactName

-----------------------------------------------

ABCDE Original Company Name New Contact

And finally

CustomerID CompanyName ContactName

-----------------------------------------------

ABCDE New Company Name New Contact

Both updates will succeed, and the change made by User A will not overwrite changes made by User B.

The structure of the ADO. NET DataAdapter does not lend itself to this updating strategy because it requires that you change the structure of the query based on the columns that have been modified in the row that contains the pending change. the DataAdapter supplies values for the parameters in its query-based updates on a row-by-row basis, but it does not modify the actual structure of the parameterized query.

Theoretically, you cocould write code to dynamically change the structure of the appropriate Command object and use that code while handling the DataAdapter object's RowUpdating event. I think that this updating strategy has benefits, but the costs outweigh them.

Referrence: <Microsoft ADO. Net>
Other resources: http://zitiger.cnblogs.com/archive/2005/08/06/208881.html