A brief talk on the ado.net of the article Series II-Concurrent update conflict processing

ADO Disclaimer: This only makes a simple analysis of concurrency conflicts, so in front of the word "talk", I hope you can see the general treatment methods and attention to the matter, if there are any omissions, it is quite natural, after all, my level is limited, there are many need to improve the place, I hope that friends do not hesitate to correct!

A Why concurrency occurs when we use ado.net to manipulate data in a database, it is very likely that such operations or similar operations are carried out among other users of the network, so it is difficult to avoid the failure of the update operation. Because in order to better improve performance, Ado.net has adopted a disconnected approach. This means that to read the copy of the data to the client first, it is also easy to cause multiple users to update a record at the same time to produce data concurrency exceptions. This operation, it is most likely that other users have deleted the row record, or have modified a field. This issue is a high-level topic and has always been a difficult point in data manipulation. I'll just do a simple discussion and analysis below. Two The solution typically handles concurrent conflicts with two methods. One is pessimistic concurrency processing and one is optimistic concurrency processing. The so-called pessimistic concurrent processing is to use a lock so that a record is read out and locked until the user submits the update. It's as bad as its name is, quite conservative. Because if the user reads the data and then leaves or forgets to click "Update" for something, the other user waits until he comes back or is reminded or thinks for himself. Obviously, this is not what we want to see (of course, the method has its advantages under certain requirements and conditions, otherwise it is not necessary to exist). On the contrary, optimistic concurrency is the focus of our discussion, and this is the recommended method for Ado.net. What is an optimistic concurrency approach? My own understanding is to face the emergence of concurrent anomalies, the development of processing methods to solve or prompt users of this abnormal appearance. Since concurrency is unavoidable, we need to deal with concurrent conflicts. This is like an optimist, he has a disease, his attitude is still optimistic, he should focus on how to treat rather than completely avoid (this analogy may not be appropriate, laughed!) OH). Before writing a program to consider the possibility of concurrent occurrence, according to their own needs to choose the update logic and mode. Three A brief discussion on the place to be noticed in the open concurrent processing. (This is also the most easy to cause problems, we have to make more comments Ah!) ）

 1.   Update options   Generally, there are two kinds of update methods, cache update and immediate update. Cache update is the use of the Dataadapter.updte () method to update the data. We may have modified a lot of records in the DataGrid, but only one commit, and the longer the distance from the start of the read data (or the last update operation), the more likely it is to cause concurrent conflicts. In contrast, immediate updating, that is, using the Command.executenoquery () method to directly submit changes to a record should be quicker, but still not completely avoid conflict, but it is still the way I recommend it. 2.   Update logic selection   UPDATE logic: What I understand is how to update the data in the updated way. It can be all columns, it can contain only primary key columns, it can contain primary key columns and updated columns, or it can contain primary key columns and timestamp columns. Let's look at the differences in the 4 update logic. If you've used PB for programming before, you might be able to understand it relatively easily. Because there are a lot of similarities between the three kinds of update logic in PB and here. To better illustrate the problem, let's give an example. The tamp of a table table1 (id,name,sex,address,salary,tamp) is a timestamp column. Then the corresponding update logic is: (1)                  Update table1 set id=?,name=?,sex=?,address=?,salary=? Where id=? and name=? and address=? and salary=? (2)                  update Table1 Set id=?,name=?,sex=?,address=?,salary=? Where id=? (3)                  update tablE1 set id=?,name=?,sex=?,address=?,salary=? Where id=? and name=? (This assumes that only the Name column is modified and the other columns are unchanged) (4)                  update Table1 Set id=?,name=?,sex=?,address=?,salary=? Where id=? and tamp=?     let's give a brief description of the 4 logic: The 1th is also the default one, all the fields are included, then when a user A, B read the data, a successfully changed the name of a row of records, then if B to change the peer record, Because the where condition requires all of the fields to be met, the name has changed so that he will fail the update. For the 2nd method, it contains only the primary key, which means that if you do not modify the primary key (or delete it) (Modifying the primary key is dangerous, you should avoid it!). Will succeed, but a update succeeds, B is also updated successfully, but B's update covers the update of a, this time B even do not know the original update when the record has changed, a also do not know that their records have been covered. This method is also called "from behind". That is, the preceding operation is covered by the following. Here also say, I generally use this method, but not to say how good it is, but for rapid development. The 3rd method contains both the primary key and the updated field. Assuming that a has updated the name field of a record, b updates the sex field of the row record, B will also successfully implement its own update. But again, if the display is not refreshed, they do not know that the corresponding field of the row record has changed. In Pb, this approach is advocated for use. But in. NET in this way the construction is relatively troublesome, write code is also more, is a comparison of consumption of time and energy. For the last method, it takes a timestamp column as the update condition, and the timestamp reflects the change of the record update. If the timestamp changes, the record is naturally updated by someone else. This method is recommended here. Of course, each update logic has its own advantages, we can not deliberately use which, according to the situation of their own system to choose. In general, the 1th Way is to DataAdapter the default generation, and the 2nd, 3rd, 4th are to be set ourselves. Create update logic manually (that is, specify the corresponding InsertCommand CommandText and updThe Atecommand of CommandText and DeleteCommand CommandText) is a time-consuming and laborious process, but the update efficiency is also relatively high.

3. Description of the update logic if the update logic is generated using the DataAdapter Wizard, you can remove the checkbox "Use optimistic concurrency" in the advanced options. This will implement the use of this update policy. For those programs that simply set the DataAdapter SelectCommand.CommandText and use the CommandBuilder constructor to generate (or use methods such as the associated GetUpdateCommand) to update the logic, this is not recommended, although this comparison Convenient, but also a discount for the efficiency and control of the update. I hope you will pay attention to it, do not use it.  4. If the Update method and the update logic are all selected, then you should consider using the transaction. The result of an update operation in a transaction is either complete or unsuccessful. Simply say is "either all do, or do not do (hehe, I remember just graduated from work in Jinan, the project leader asked me, I was so answered!" Therefore, the program code that uses the transaction is relatively safe. Code similar to the following:

Dim Mytransaction as OleDbTransaction

Try

Conn. Open ()

Mytransaction = conn. BeginTransaction

Cmd1. Transaction = Mytransaction ' Here assumes that the Cmd1 object has been established

Cmd1. ExecuteNonQuery ()

Mytransaction.commit () ' Submit transaction

Catch ex as Exception

Mytransaction. Rollback () ' ROLLBACK TRANSACTION

Return-1 ' do some other processing

Finally

Conn. Close ()

End Try

Description: How do I use a transaction for the DataAdapter.Update method? In fact, the DataAdapter itself does not update the data, but its insertcommand,updatecommand,deletecommand. Then set the transaction property of the three command objects just like the one above. The others are no different from the above.

5. Now that we're in front of it. Using open concurrency processing, it is necessary to catch the exception, give the corresponding information and processing methods. It's a good practice to include code that might cause an exception to be included in a try ... end Try block. You can generally take actions similar to the following:

Try

Sqldpr1.update (DS1. Tables ("table1"))

Catch Ex as data.dbconcurrencyexception ' concurrency conflicting exception

' Do the appropriate processing End Try Description: Here's how to do this, you can read the latest rows from the database to update the existing rows (of course, if the row is removed), you can also repopulate the data (fill operation). This time we can make a decision that the row is deleted, and you can use a function to pass the Ex.row ("ID") as a parameter to the past, Use a Command.executeschar method to determine whether a record exists, use ExecuteReader or fill to get a changed record, or refresh all records. The exception that is thrown by the update () method is mentioned here, and is handled the same way if it is caused by an immediate update.

Summary: Because of the time and my level of problems, this problem can not be discussed too deeply, please forgive us. There may be less sample code involved (huh, really little), but I think it might be a bit of a headache to read, but I thought if you were familiar with the principles of data updating, you would understand the meaning. Finally, I hope we can make more suggestions! Have time to certainly give the relevant sample code.