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

ADO事务处理案例

最新推荐文章于 2025-04-17 09:10:06 发布

原创最新推荐文章于 2025-04-17 09:10:06 发布 · 551 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#integer #security

SQL 专栏收录该内容

30 篇文章

订阅专栏

本文介绍了一个使用ADO进行数据库操作的具体案例，包括开启事务、更新库存、插入销售记录等步骤，并探讨了不同方式下数据库锁定机制的影响。

(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