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

Chapter 16. 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 Using Transaction Processing in a Multiuser Environment 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 16.5 illustrates this scenario.

Listing 16.5. A Safe Way to Do Transactions in a Multiuser Environment That Sacrifices Concurrency

Sub MultiPessimistic()
    On Error GoTo MultiPessimistic_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim intCounter As Integer
    Dim intChoice As Integer
    Dim intTry As Integer
    Dim boolInTrans As Boolean


    boolInTrans = False
    Set rst = New ADODB.Recordset
    Set cnn = CurrentProject.Connection

    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.CursorLocation = adUseServer
    rst.LockType = adLockPessimistic
    rst.Open "Select OrderId, ProductID, UnitPrice " & _
        "From tblOrderDetails Where ProductID > 50"

    'Begin the Transaction Loop
    cnn.BeginTrans
        boolInTrans = True
        'Loop through recordset increasing UnitPrice
        Do Until rst.EOF
            'Lock Occurs Here for Each Record in the Loop
            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
    cnn.CommitTrans
    boolInTrans = False
    Set cnn = Nothing
    Set rst = Nothing
    Exit Sub

MultiPessimistic_Err:
    Select Case cnn.Errors(0).SQLState
        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:
    If boolInTrans Then
        cnn.RollbackTrans
    End If
    MsgBox "Warning: Entire Process Rolled Back"
    Set cnn = 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