(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