How to avoid concurrency conflicts!

Some applications use the "backward in Wins" method when updating database data. Updating a database with the LIFO method does not compare updates to the original record, and therefore may overwrite all changes made by other users since the last time the records were refreshed. However, sometimes an application needs to determine whether the data has been changed since it was first read before performing an update. Data access Logic components can implement code that manages locking and concurrency. There are two ways to manage locking and concurrency: • pessimistic concurrency. A user who reads a row of data for an update can set a lock on the row in the data source. No other user can change the row until the user unlocks it. • Open concurrency. The user does not lock the row when reading a row of data. Other users can freely access the line at the same time. When a user wants to update a row of data, the application must determine whether other users have made changes since the row was read. Attempting to update a record that has changed can cause concurrent conflicts. The use of pessimistic concurrency-pessimistic concurrency is primarily used for environments where data contention is large and the cost of protecting data by locking is lower than the cost of rolling back transactions when concurrent conflicts occur. If the lock time is short (for example, in a program-processed record), it is best to implement pessimistic concurrency. Pessimistic concurrency requires a persistent connection to the database, and because records can be locked for a long time, you cannot provide scalable performance when the user interacts with the data. Using optimistic concurrency optimistic concurrency applies to environments where data contention is low or require read-only access to data. Optimistic concurrency reduces the number of locks required to reduce the load on the database server and improve database performance. Optimistic concurrency is widely used in. NET to meet the needs of mobile and offline applications. In this case, it is not feasible to lock the data for a long time. In addition, maintaining record locking requires a persistent connection to the database server, which is not possible in an offline application. There are several ways to test open concurrency conflicts with optimistic concurrency conflicts: • Use distributed timestamps. Distributed timestamps apply to environments that do not require coordination. Add a timestamp column or version column to each table in the database. The timestamp column is returned with the query for the contents of the table. When an attempt is made to update, the timestamp value in the database is compared with the original timestamp value in the modified row. If the two values match, the update is performed, and the timestamp column is updated to the current time to reflect the update. If the two values do not match, an optimistic concurrency violation occurs. • Keep a copy of the original data value. Retains a copy of the original data value when querying the data in the database. When you update the database, check to see if the current value of the database matches the original value. • The original value is saved in the dataset, and when the database is updated, the data adapter can perform an optimistic concurrency check using the original value. • Use a centralized timestamp. Defines a set of timestamp tables in the database for recordingAn update to any row in any table. For example, a timestamp table can display the following message: "March 26, 2002 2:56 John updated row 1234 in table XYZ." A centralized timestamp applies to checkout scenarios and some offline client scenarios, which may require explicit locking of owners and alternative management. In addition, a centralized timestamp can also provide auditing as needed. Manually implement optimistic concurrency consider the following SQL query: Select Column1, Column2, Column3 from Table1 to test an optimistic concurrency violation when updating Table1 rows, you can issue the following UPDATE statement: Update Tab Le1 Set Column1 = @NewValueColumn1, set Column2 = @NewValueColumn2, set Column3 = @NewValueColumn3 WHERE Column1 = @OldVal UeColumn1 and Column2 = @OldValueColumn2 and Column3 = @OldValueColumn3 If the original value matches the value in the database, the update is performed. If a value is modified, the WHERE clause will not be able to find an appropriate match, so that the update will not modify the row. You can change this technique slightly by applying a WHERE clause to only a specific column so that if a particular field has been updated since the last query, the data is not overwritten. Note: Always return a value that uniquely identifies a row in the query, such as a primary keyword, for the WHERE clause of the UPDATE statement. This will ensure that the UPDATE statement updates the correct row. If the columns in the data source allow null values, you may need to extend the WHERE clause to check for a matching null reference in the local table and the data source. For example, the following UPDATE statement verifies that a null reference (or value) in a local row still matches a null reference (or value) in the data source. UPDATE Table1 Set Column1 = @NewColumn1Value WHERE (@OldColumn1Value is null and COLUMN1 is null) OR Column1 = @OldColumn1 Value uses data adapters and datasets to implement optimistic concurrency: You can use the Dataadapter.rowupdated event with the preceding technology to inform your application that an optimistic concurrency violation has occurred. Whenever an attempt is made to update a modified row in a DataSet, aRowUpdated event. You can use the RowUpdated event to add special handling code, including handling when an exception occurs, adding custom error messages, and adding a retry logic. The RowUpdated event handler receives a RowUpdatedEventArgs object that has a RecordsAffected property that shows how many rows are affected by the update command for a modified row in the table. If you set the update command to test optimistic concurrency, the RecordsAffected property will be 0 when an optimistic concurrency violation occurs. Set the RowUpdatedEventArgs.Status property to indicate the action to take; For example, you can set the property to UpdateStatus.SkipCurrentRow to skip updating the current row, but continue to update the other rows in the update command. Another way to test concurrency errors using a data adapter is to set the DataAdapter.ContinueUpdateOnError property to True before calling the Update method. When the update is complete, call the GetErrors method of the DataTable object to determine which rows have errors. Then, use the RowError property of these rows to find specific detailed error messages. The following code example shows how the Customer data Access Logic component checks for concurrent conflicts. The example assumes that the client retrieves a dataset and modifies the data, and then passes the dataset to the UpdateCustomer method in the data Access Logic component. The UpdateCustomer method updates the corresponding customer record by calling the following stored procedures, and the stored procedure can update the customer record only if the customer ID and company name have not been modified: CREATE PROCEDURE customerupdate {@CompanyName varchar (@oldCustomerID varchar), @oldCompanyName varchar} as UPDATE Customers Set CompanyName = @CompanyName where CustomerID = @oldCustomerID and CompanyName = @oldCompanyName go in the UpdateCustomer method, the following code example sets the UpdateCommand of a data adapter property is set to test optimistic concurrency and then use the RowupdateThe D event tests an optimistic concurrency violation. If an optimistic concurrency violation is encountered, the application indicates an optimistic concurrency violation by setting the RowError of the row to be updated. Note that the parameter values that are passed to the WHERE clause in the UPDATE command are mapped to the original values of each corresponding column in the DataSet. The UpdateCustomer method in the CustomerDALC class is public void UpdateCustomer (DataSet dscustomer) {//Connect to the Northwind database SqlConnection Cnnorthwind = new SqlConnection ("Data source=localhost;integrated security=sspi;initial catalog=northwind"); Create a data adapter to access the Customers table in Northwind SqlDataAdapter da = new SqlDataAdapter (); Sets the UPDATE command for the data adapter to invoke the stored procedure "UpdateCustomer" Da. UpdateCommand = new SqlCommand ("CustomerUpdate", cnnorthwind); Da.UpdateCommand.CommandType = CommandType.StoredProcedure; Add two parameters to the data adapter's UPDATE command, or specify information for the WHERE clause (used to check for optimistic concurrency conflicts) da. UPDATECOMMAND.PARAMETERS.ADD ("@CompanyName", SqlDbType.NVarChar, "CompanyName"); Specifies the original value of the CustomerID as the first WHERE clause parameter SqlParameter myparm = da. UPDATECOMMAND.PARAMETERS.ADD ("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID"); Myparm.sourceversion = datarowversion.original; Specifies the original value of the CustomerName as the second WHERE clause parameter myparm = da. UpdaTECOMMAND.PARAMETERS.ADD ("@oldCompanyName", SqlDbType.NVarChar, "CompanyName"); Myparm.sourceversion = datarowversion.original; Adds a handler da for the RowUpdated event. RowUpdated + = new Sqlrowupdatedeventhandler (onrowupdated); Update Database DA. Update (ds, "Customers"); foreach (DataRow myrow in DS. tables["Customers"]. Rows) {if (myrow.haserrors) Console.WriteLine (myrow[0] + "" + Myrow.rowerror);} The method that handles the RowUpdated event. If the event is enlisted but not processed, a SQL exception is thrown. protected static void OnRowUpdated (object sender, Sqlrowupdatedeventargs args) {if (args). RecordsAffected = = 0) {args. Row.rowerror = "Encountering open concurrency conflict"; Args. Status = UpdateStatus.SkipCurrentRow; When executing multiple SQL statements in a SQL Server stored procedure, you can use the SET NOCOUNT on option for performance reasons. This option will prevent SQL Server from returning a message to the client each time a statement is executed, which can reduce network traffic. However, this will not check the RecordsAffected property as in the previous code example. The RecordsAffected property will always be 1. Another method is to return the @ @ROWCOUNT function (or specify it as an output parameter) in a stored procedure, and the @ @ROWCOUNT contains the number of records when the last statement in the stored procedure completes, and the function is updated even if the SET NOCOUNT on is used. Therefore, if the last SQL statement executed in the stored procedure is the actual UPDATE statement and you have specified the @ @ROWCOUNT as the return value, you can apply to the applicationThe sequence code is modified as follows://Add another parameter to the data adapter's UPDATE command to receive the return value. You can name the parameter arbitrarily. Myparm = da. UPDATECOMMAND.PARAMETERS.ADD ("@RowCount", SqlDbType.Int); Myparm.direction = ParameterDirection.ReturnValue; Modify the OnRowUpdated method to check the value of the parameter//instead of the RecordsAffected property. protected static void OnRowUpdated (object sender, Sqlrowupdatedeventargs args) {if (args.command.parameters["@RowCount"]. Value = = 0) {args. Row.rowerror = "Encountering open concurrency conflict"; Args. Status = UpdateStatus.SkipCurrentRow; Avoid concurrent conflicts: 1. A condition that ensures that a row can be returned, such as a primary keyword, is used in the WHERE clause to ensure that the UPDATE statement updates the correct row. 2. If the columns used in your WHERE condition may have null values, such as where col1= @col1 and col2= @col2, if col2 is null, the Where condition col2= @col2, no matter what value you pass to @col2, If the value of col2 is NULL, it will cause the where to not match to the row, which will result in an exception that is similar to the following: concurrency violation: UpdateCommand update 0 records. When you use a column in a where condition to allow null values, use the following: UPDATE Table1 Set Column1 = @NewColumn1Value WHERE (@OldColumn1Value is NULL and Column1 is NULL) OR Column1 = @OldColumn1Value