Foreword: Database concurrent issues http://www.9cbs.net/develop/read_article.asp?id=24366 has explained the severity and harm of concurrency. The following describes the actual cases of VB ADO to handle concurrency operations: In the previous DAO, the database can be recorded, the page lock, and the table lock will handle concurrency operations, and you can use transaction processing, then how to use ADO to detect and process databases Concurrent operation? Related background knowledge: ADO is also implemented by locking methods for the database, and can be done in transactions. There is a feature of the transaction is: Either success, either fail. Then there may be only a few or a small number of records in the actual work, as long as it is handled in the small number of records. ADO also is also implemented using locks. So what is the lock? Lock is a process, DBMS limits access to rows in multi-user environments through this process. When a row or a column is exclusively locked, other users are not allowed to access locked data before the lock is released. This ensures that two users cannot update the same column in a row. From a resource point of view, the cost of locking may be very high, and this feature should be used only if it needs to maintain data integrity. There are hundreds or thousands of users attempt to access a recorded database (for example, a database connected to the Internet), and unnecessary lock will soon lead to the decline in application performance. The appropriate lock options can be selected to control the data source and how the ADO cursor library management concurrency. Set the LockType property before opening RecordSet to specify the lock type used when the provider opens it. Read this attribute to return the type of lock used in the open Recordset object. The provider may not support all lock types. If the provider does not support the requested LockType settings, it is replaced with another lock type. To determine the actual lock functionality actually available in the Recordset object, use the Soppurts method with AduPdate and AduPDateBatch. If the CursorLocation property is set to AduseClient, the AdlockPessimistic setting is not supported. If an unsupported value is set, no errors are generated, and the most similar LockType supported. The LockType property is read / written when Recordset is closed, and is read only when the Recordset is open. Lock Type Category (LockType Properties): 1.AdlockbatchOptimistic indicates open batch update. Need a batch update mode. Many applications extract multiple lines at a time, then need to be updated, including all a complete collection of rows that need to be inserted, updated, or deleted. Using a battles only a round-trip server, thus leading to an increase in update performance and a decrease in network traffic. Use the batch library to create a static cursor, then disconnect to the connection of the data source. You can make changes to the data source, and then publish the changes to the data source in a batch. The 2.adlockoptimistic provider uses an open lock to lock the record only when the Update method is called. This means that another user may change the data using the time interval for editing and calling Update, which will cause conflicts. The critical chance of conflicts when using this lock type, even if a conflict occurs, will be resolved. 3.adlockpessimistic instructs a conservative lock. The provider must ensure that the record editing is successful, usually locks the record immediately before editing. Of course, this means that once you start editing records, these records are not available to other users, knowing that you are unlocked by calling Update. This lock type can be used if your system does not provide concurrent changes to the data, such as a predetermined system. 4.adlockReadOnly Indicates read-only records. Unable to change the data.
Read-only lock is the speed "fastest" lock type because it does not require the server to remain locked for the record. Conflict 5.adlockunSpecified is not specified. Detection and resolution Conflicts: If you handle Recordset in immediately mode, you have few concurrent problems. On the other hand, if the application uses a batch mode update, editing another user that edits the same record before saving the user who is editing records, editing the record. In this case, the application is required to exact conflicts. In either case, you can use the UnderlyingValue and OriginalValue properties of the Field object provided by Ado to process these types of conflicts. Use these properties to work with the Recordset's Resync method and Filter properties. Detection error: When the ADO encounters conflicts during the batch update, the warning will be placed in the ERRORS collection. Therefore, after calling BatchUpdate, be sure to check if there is an error, if an error is found, it should assume that a conflict has been encountered and the test is started. The first step is to set the refordset's Filter property equal to AdfilterConflictingRecords. This setting will make the RecordSet object to only show only those collisions. If the RecordCount property is equal to zero after this step, the error is caused by other reasons other than conflict. When calling BatchUpdate, the ADO and the provider will generate the SQL statement that performs updates to the data source. Next, call the Recordset's Resync method, and set the AffECTRECORDS parameter to be equal to AdaffectGroup, set the RESYNCVALUES parameter equal to AdResyncunderlyingValues. The RESYNC method will refresh the data in the current RECORDSET object with data from the data from the basic database. By using AdaffectGroup, you can ensure that only records visible with the current filter settings (ie, only conflict records) will be resynchronized with the database. If the processing is a large Recordset, the operation has a big impact on performance. By setting the resyncvalues parameter to AdResyncunderlyingValues, make sure that the underlyingValue property will contain the (conflict) value in the database, and make sure the value of the value is still entered by the user, and the originAlue property will hold the original value of the field. (Ie the value owned by the field before the UpdateBatch call before the last success). You can then use these values to resolve conflicts by programming, or require the user to select the value to be used. Example: This example changes the value in the basic table by using a separate recordset before calling the UpdateBatch, and manually creates a conflict.
'Begin On Error GoTo ErrHandler: Dim objRs1 As New ADODB.Recordset Dim objRs2 As New ADODB.Recordset Dim strSQL As String Dim strMsg As String strSQL = "SELECT * FROM Shippers WHERE ShipperID = 2"' SQL objRs1.CursorLocation = adUseClient inquiry ' Set the client game objrs1.open strsql, strconn, adopenStatic, adlickbatchoptimistic, and adcmdtext 'executing query generation OBJRS1 record set OBJRS1 ("Phone") = "(111) 555-1111" Change the value of a record Phone field in the table OBJRS2. Open strsql, strconn, adopenkeyset, adlockoptimistic, and adcmdtext 'perform query generation OBJRS2 record set OBJRS2 ("Phone") = "(999) 555-9999" Change table records the value of the phone field Objrs2.Update objrs2.close set objrs2 = Nothing On Error Resume Next objRs1.UpdateBatch If objRs1.ActiveConnection.Errors.Count <> 0 Then Dim intConflicts As Integer intConflicts = 0 objRs1.Filter = adFilterConflictingRecords intConflicts = objRs1.RecordCount objRs1.Resync adAffectGroup, adResyncUnderlyingValues If intConflicts> 0 Then strMsg = "A conflict occurred with updates for "& intConflicts &" records. "& vbCrLf &" The values will be restored "" to their original values. "objRs1.MoveFirst While Not objRs1.EOF strMsg = strMsg &" SHIPPER = "& objRs1 (" CompanyName ") & vbcrlf stramsg = strmsg &" value = "& objrs1 (" phone "). Value & vbcrlf strmsg = strmsg &" underlyingValue = "& _ objrs1 (" phone "). UnderlyingValue &
vbCrLf strMsg = strMsg & "OriginalValue =" & _ objRs1 ( "Phone"). OriginalValue & vbCrLf strMsg = strMsg & vbCrLf & "Original value has been restored." MsgBox strMsg, vbOKOnly, _ "Conflict" & objRs1.AbsolutePosition & _ "of" & intConflicts objRs1 ( "Phone"). Value = objRs1 ( "Phone"). OriginalValue objRs1.MoveNext Wend objRs1.UpdateBatch adAffectGroup Else strMsg = "Errors occurred during the update." & _ objRs1.ActiveConnection.Errors (0 ) .Number & "" & _ objRs1.ActiveConnection.Errors (0) .Description End If On Error GoTo 0 End If objRs1.MoveFirst objRs1.Close Set objRs1 = NothingExit SubErrHandler: If Not objRs1 Is Nothing Then If objRs1.State = adStateOpen Then objrs1.close set objrs1 = Nothing End if if not objrs2.State = adservationenin1 objrs2.close set objrs2 = Nothing endiff err .source & "" end IF'End can also use the status attribute of current Record or specific field to determine the type of conflict that has occurred. The records of conflict have been checked out, then the next step is to process the failure update. How to solve errors will depend on the nature and severity of the error and the logic of the application. The actual database is a number of users sharing. Typical errors are other people to modify the field before you modify a field. This type of error is called "conflict." ADO will detect this situation and report errors. If there is an update error, it will be captured by an error handling routine. Filter RECORDSET by using AdfilterConflictingRecords, you can display only conflicts.
