• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 22. Transaction Processing > Using Transaction Processing in a Multiuse...

Using Transaction Processing in a Multiuser Environment

In a multiuser environment, transaction processing has implications beyond the protection of data. By wrapping a process in a transaction loop, you ensure that you are in control of all records involved in the process. The cost of this additional control is reduced concurrency for the rest of the users of the application. Listing 22.4 illustrates this scenario.

Listing 22.4. A safe way to do transactions in a multiuser environment that sacrifices concurrency.

Sub MultiPessimistic()
   On Error GoTo MultiPessimistic_Err
   Dim wrk As Workspace
   Dim db As DATABASE
   Dim rst As Recordset
   Dim intCounter As Integer
   Dim intChoice As Integer
   Dim intTry As Integer

   Set wrk = DBEngine(0)
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Select OrderId, ProductID, UnitPrice " & _
            "From tblOrderDetails Where ProductID > 50", _
               dbOpenDynaset)
   rst.LockEdits = True

   'Begin the Transaction Loop
   wrk.BeginTrans
   'Loop through recordset increasing UnitPrice
   Do Until rst.EOF
      'Lock Occurs Here for Each Record in the Loop
      rst.Edit
      rst!UnitPrice = rst!UnitPrice * 1.1
      rst.UPDATE
      rst.MoveNext
   Loop
   'Commit the Transaction; Everything went as Planned
   'All locks released for ALL records involved in the Process
   wrk.CommitTrans
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub

MultiPessimistic_Err:
   Select Case Err.Number
      Case 3260
         intCounter = intCounter + 1
         If intCounter > 2 Then
            intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
            Select Case intChoice
               Case vbRetry
                  intCounter = 1
               Case vbCancel
                  'User Selected Cancel, Roll Back
                  Resume TransUnsuccessful
            End Select
         End If
         DoEvents
         For intTry = 1 To 100: Next intTry
         Resume
      Case Else
         MsgBox "Error # " & Err.Number & ": " & Err.Description
   End Select

TransUnsuccessful:
   wrk.Rollback
   MsgBox "Warning: Entire Process Rolled Back"
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub

End Sub

					  


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint