库存减少销售增加的事务例子

(1)

Private Sub Do(ByVal SL As Integer)
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim oRs2 As New ADODB.Recordset

On Error GoTo ErrorHandler
oConn.ConnectionTimeout = 15
oConn.Open "Provider=SQLOLEDB.1;Password=***;Persist Security Info=True;User ID=***;Initial Catalog=testdb;Data Source=***.***.***.***"
oConn.CommandTimeout = 15
oConn.IsolationLevel = adXactSerializable
oConn.BeginTrans

oRs.CursorLocation = adUseClient
oRs.Open "SELECT * FROM STOCK", oConn, adOpenKeyset, adLockPessimistic
oRs("KCS") = oRs("KCS") - SL           
oRs.Update

If oRs("KCS") <0 Then
  oRs.Close
  oConn.RollbackTrans
  oConn.Close
  Set oConn = Nothing
  Exit Sub
Else
  oRs2.Open "SELECT * FROM SALE", oConn, adOpenKeyset, adLockPessimistic
  oRs2.AddNew
  oRs2("SL") = SL
  oRs2.Update
  oRs.Close
  oRs2.Close
End If

oConn.CommitTrans
oConn.Close
Set oConn = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Description
oConn.RollbackTrans
oConn.Close
Set oConn = Nothing
End Sub

 

(2)
Private Sub Do(ByVal SL As Integer)
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim oRs2 As New ADODB.Recordset

On Error GoTo ErrorHandler
oConn.ConnectionTimeout = 15
oConn.Open "Provider=SQLOLEDB.1;Password=***;Persist Security Info=True;User ID=***;Initial Catalog=testdb;Data Source=127.0.0.1"
oConn.CommandTimeout = 15
oConn.IsolationLevel = adXactSerializable
oConn.BeginTrans

oRs.CursorLocation = adUseClient
oRs.Open "UPDATE STOCK SET KCS=KCS-SL", oConn, adOpenKeyset, adLockPessimistic
oRs.Open "SELECT * FROM STOCK", oConn, adOpenForwardOnly, adLockReadOnly
If oRs("KCS") < 0 Then
  oRs.Close
  oConn.RollbackTrans
  oConn.Close
  Set oConn = Nothing
  Exit Sub
Else
  oRs2.Open "SELECT *  FROM SALE", oConn, adOpenKeyset, adLockPessimistic
  oRs2.AddNew
  oRs2("SL") = SL
  oRs2.Update
  oRs.Close
  oRs2.Close
End If

oConn.CommitTrans
oConn.Close
Set oConn = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Description
oConn.RollbackTrans
oConn.Close
Set oConn = Nothing
End Sub

 

'在事务中表一旦被锁住，只有事务状态改变才会解锁，单纯oRs.Close是没有作用的;
'只要使用了WITH(TABLOCKX)，再如何WHERE都是锁住整张表;
'锁住的表，并不影响同一个事务连接的其他oRs的读写删改;
oRs.Open "SELECT * FROM STOCK WITH(TABLOCKX) WHERE KCS>123", oConn, adOpenForwardOnly, adLockReadOnly

 

'不用事务，多个进程并发执行下列语句也会报错，说：KCS在修改前其值已经改变。
'缺点是没有事务就不可能自动回退可能已经增加的销售记录；
oRs.Open "SELECT KCS FROM STOCK", oConn, adOpenForwardOnly, adLockReadOnly
oRs("KCS")=oRs("KCS")-SL
oRs.Update