SQL Server uses timestamp to help resolve data concurrency conflicts turn "Go"

http://blog.csdn.net/u011014032/article/details/42936783

Regarding the concurrent request, many friends on the net have said very detailed, I am not here caught dead. This is only a record of the project that has just been completed, how to deal with timestamp in case of future contingencies.

There is a timestamp field type in SQL Server, if we need to process a data table for concurrent requests to prevent two users from modifying the same piece of data in the table to create a conflict. You can add a timestamp field to the table, and when the data is added and modified, the timestamp type automatically generates a new value. At this point we can avoid overwriting other users ' modifications by taking the value of the timestamp field as one of the where sub-conditions of the update.

For example, I have a product table products, in which there is a field buyer for the buyer ID, if a customer buys a product, will be in the corresponding goods in the buyer record their own ID, But if there are two customers buying a product at the same time (in fact, they have to submit a request for a difference, but it is likely to be a few milliseconds apart, the computer can perceive, but people do not perceive), while the product's buyer to write their own ID, it will cause conflict, At this point we should prioritize the processing time slightly ahead of the user, and the notification time slightly after the user other users have already purchased.

The SQL commands for detailed operations are as follows:

1, the user to access the Baby Details page, read the baby information from the database, which includes the field named RowVersion timestamp field type value (Note: To convert the rowversion to a long integer in SQL for later alignment), At this point the value of the rowversion obtained by the two users is the same.

[SQL]View PlainCopy

1. Select ProductID,name,price,convert (bigint,fp.rowversion) as version from Products where productid=1058

This step is to record the value of the rowversion in the program, for example, we can save to a string type of field, such as: Strrowversion = reader["Version"]. ToString ();

2, the user modified buyer for their own ID

Update products set buyer=35 where productid=1058 and rowVersion = @version

In this step we can pass in the SQL statement to the rowversion value just recorded, if the previous user saved successfully, then the RowVersion value will automatically change. At this point, the subsequent user can also use the same SQL statement to modify the words will fail, and then we may prompt the user that the product has been purchased by other users.

SQL Server uses timestamp to help resolve data concurrency conflicts turn "Go"