把以下内容另存为Form1.frm,工程中引用ADO。VERSION 5.00 Begin VB.Form Form1 Caption = "Form1" ClientHeight = 3195 ClientLeft = 60 ClientTop = 345 ClientWidth = 4680 LinkTopic = "Form1" ScaleHeight = 3195 ScaleWidth = 4680 StartUpPosition = 3 '窗口缺省 Begin VB.CommandButton Command3 Caption = "Command3" Height = 375 Left = 1200 TabIndex = 2 Top = 1680 Width = 2055 End Begin VB.CommandButton Command2 Caption = "Command2" Height = 375 Left = 1200 TabIndex = 1 Top = 1080 Width = 2055 End Begin VB.CommandButton Command1 Caption = "Command1" Height = 375 Left = 1200 TabIndex = 0 Top = 480 Width = 2055 End End Attribute VB_Name = "Form1" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = False '----------------------------------------------------------------- '说 明:本程序用于测试锁定 ' '测试环境:1.建立一个数据库,名为Test; ' 2.建立一个表,名为LockTest; ' 列名 类型 允许Null 说明 ' ----------------------------------------------------- ' ID int No 主键(PK)!!! ' Descr varchar(50) No ' 3.向LockTest表添加2条记录; ' ID Descr ' ----------------------------------------------------- ' 1 descr1 ' 2 descr2 ' ----------------------------------------------------- ' 4.引用ADO,编译本程序。 ' '其它说明:1.要测试锁定,必须在不同的进程中进行测试,即打开本程序2 ' 次,在各自独立的进程中测试; ' 2.只有设置了主键(PrimaryKey)的表才能锁定记录; ' 3.要观察锁定结果,可以在查询分析器中执行SP_LOCK存储过程; ' 4.在事务中对记录的锁定会保持到事务提交或回滚才会解锁; ' 5.不考虑锁定读取记录:select * from table with (nolock) ' '作者主页:http://junwhj.myrice.com '-----------------------------------------------------------------Option ExplicitPrivate Conn As ADODB.Connection Private Rs As ADODB.RecordsetPrivate Sub Command1_Click() On Error GoTo ErrHandler
If Command1.Caption = "锁定整个表" Then '在事务结束前,锁定一直保持 Conn.BeginTrans Conn.Execute "SELECT * FROM LockTest WITH (TABLOCKX)" Command2.Enabled = False Command3.Enabled = False Command1.Caption = "解锁" Else Conn.RollbackTrans Command2.Enabled = True Command3.Enabled = True Command1.Caption = "锁定整个表" End If
Exit Sub
ErrHandler: Select Case Err.Number Case -2147467259, -2147217900, 3704 Conn.RollbackTrans MsgBox "锁定失败" Case Else Err.Raise Err.Number, , Err.Description End Select End SubPrivate Sub Command2_Click() On Error GoTo ErrHandler
If Command2.Caption = "锁定ID=1的记录(xLock)" Then '在事务结束前,锁定一直保持 Conn.BeginTrans Conn.Execute "SELECT * FROM LockTest WITH (XLOCK) WHERE ID=1" Command1.Enabled = False Command3.Enabled = False Command2.Caption = "解锁" Else Conn.RollbackTrans Command1.Enabled = True Command3.Enabled = True Command2.Caption = "锁定ID=1的记录(xLock)" End If
Exit Sub
ErrHandler: Select Case Err.Number Case -2147467259, -2147217900, 3704 Conn.RollbackTrans MsgBox "锁定失败" Case Else Err.Raise Err.Number, , Err.Description End Select End SubPrivate Sub Command3_Click() Dim Test As String
On Error GoTo ErrHandler
If Command3.Caption = "锁定ID=1的记录(ADO)" Then '使用服务器端游标、adLockPessimistic类型的锁 '在RecordSet关闭前一直保持锁定 Set Rs = New ADODB.Recordset Rs.CursorLocation = adUseServer Rs.Open "SELECT * FROM LockTest WHERE ID=1", _ Conn, _ adOpenKeyset, _ adLockPessimistic '对RecordSet进行一些操作,若无下面这句,则不会立即锁定记录 Test = Rs(0).Value Command1.Enabled = False Command2.Enabled = False Command3.Caption = "解锁" Else Rs.Close Set Rs = Nothing Command1.Enabled = True Command2.Enabled = True Command3.Caption = "锁定ID=1的记录(ADO)" End If
Exit Sub
ErrHandler: Select Case Err.Number Case -2147467259, -2147217900, 3704 Set Rs = Nothing MsgBox "锁定失败" Case Else Err.Raise Err.Number, , Err.Description End Select End SubPrivate Sub Form_Load() Set Conn = New ADODB.Connection Conn.ConnectionString = "Driver={SQL Server};" & _ "Server=Developer;" & _ "UID=SA;" & _ "PWD=;" & _ "Database=Test" Conn.Open Conn.Execute "SET LOCK_TIMEOUT 1" '设定锁定超时时限
Command1.Caption = "锁定整个表" Command2.Caption = "锁定ID=1的记录(xLock)" Command3.Caption = "锁定ID=1的记录(ADO)" End SubPrivate Sub Form_Unload(Cancel As Integer) On Error Resume Next Rs.Close Set Rs = Nothing Conn.Close Set Conn = Nothing Set Form1 = Nothing End Sub
adLockOptimistic
adLockBatchOptimistic
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 3195
ClientLeft = 60
ClientTop = 345
ClientWidth = 4680
LinkTopic = "Form1"
ScaleHeight = 3195
ScaleWidth = 4680
StartUpPosition = 3 '窗口缺省
Begin VB.CommandButton Command3
Caption = "Command3"
Height = 375
Left = 1200
TabIndex = 2
Top = 1680
Width = 2055
End
Begin VB.CommandButton Command2
Caption = "Command2"
Height = 375
Left = 1200
TabIndex = 1
Top = 1080
Width = 2055
End
Begin VB.CommandButton Command1
Caption = "Command1"
Height = 375
Left = 1200
TabIndex = 0
Top = 480
Width = 2055
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'-----------------------------------------------------------------
'说 明:本程序用于测试锁定
'
'测试环境:1.建立一个数据库,名为Test;
' 2.建立一个表,名为LockTest;
' 列名 类型 允许Null 说明
' -----------------------------------------------------
' ID int No 主键(PK)!!!
' Descr varchar(50) No
' 3.向LockTest表添加2条记录;
' ID Descr
' -----------------------------------------------------
' 1 descr1
' 2 descr2
' -----------------------------------------------------
' 4.引用ADO,编译本程序。
'
'其它说明:1.要测试锁定,必须在不同的进程中进行测试,即打开本程序2
' 次,在各自独立的进程中测试;
' 2.只有设置了主键(PrimaryKey)的表才能锁定记录;
' 3.要观察锁定结果,可以在查询分析器中执行SP_LOCK存储过程;
' 4.在事务中对记录的锁定会保持到事务提交或回滚才会解锁;
' 5.不考虑锁定读取记录:select * from table with (nolock)
'
'作者主页:http://junwhj.myrice.com
'-----------------------------------------------------------------Option ExplicitPrivate Conn As ADODB.Connection
Private Rs As ADODB.RecordsetPrivate Sub Command1_Click()
On Error GoTo ErrHandler
If Command1.Caption = "锁定整个表" Then
'在事务结束前,锁定一直保持
Conn.BeginTrans
Conn.Execute "SELECT * FROM LockTest WITH (TABLOCKX)"
Command2.Enabled = False
Command3.Enabled = False
Command1.Caption = "解锁"
Else
Conn.RollbackTrans
Command2.Enabled = True
Command3.Enabled = True
Command1.Caption = "锁定整个表"
End If
Exit Sub
ErrHandler:
Select Case Err.Number
Case -2147467259, -2147217900, 3704
Conn.RollbackTrans
MsgBox "锁定失败"
Case Else
Err.Raise Err.Number, , Err.Description
End Select
End SubPrivate Sub Command2_Click()
On Error GoTo ErrHandler
If Command2.Caption = "锁定ID=1的记录(xLock)" Then
'在事务结束前,锁定一直保持
Conn.BeginTrans
Conn.Execute "SELECT * FROM LockTest WITH (XLOCK) WHERE ID=1"
Command1.Enabled = False
Command3.Enabled = False
Command2.Caption = "解锁"
Else
Conn.RollbackTrans
Command1.Enabled = True
Command3.Enabled = True
Command2.Caption = "锁定ID=1的记录(xLock)"
End If
Exit Sub
ErrHandler:
Select Case Err.Number
Case -2147467259, -2147217900, 3704
Conn.RollbackTrans
MsgBox "锁定失败"
Case Else
Err.Raise Err.Number, , Err.Description
End Select
End SubPrivate Sub Command3_Click()
Dim Test As String
On Error GoTo ErrHandler
If Command3.Caption = "锁定ID=1的记录(ADO)" Then
'使用服务器端游标、adLockPessimistic类型的锁
'在RecordSet关闭前一直保持锁定
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseServer
Rs.Open "SELECT * FROM LockTest WHERE ID=1", _
Conn, _
adOpenKeyset, _
adLockPessimistic
'对RecordSet进行一些操作,若无下面这句,则不会立即锁定记录
Test = Rs(0).Value
Command1.Enabled = False
Command2.Enabled = False
Command3.Caption = "解锁"
Else
Rs.Close
Set Rs = Nothing
Command1.Enabled = True
Command2.Enabled = True
Command3.Caption = "锁定ID=1的记录(ADO)"
End If
Exit Sub
ErrHandler:
Select Case Err.Number
Case -2147467259, -2147217900, 3704
Set Rs = Nothing
MsgBox "锁定失败"
Case Else
Err.Raise Err.Number, , Err.Description
End Select
End SubPrivate Sub Form_Load()
Set Conn = New ADODB.Connection
Conn.ConnectionString = "Driver={SQL Server};" & _
"Server=Developer;" & _
"UID=SA;" & _
"PWD=;" & _
"Database=Test"
Conn.Open
Conn.Execute "SET LOCK_TIMEOUT 1" '设定锁定超时时限
Command1.Caption = "锁定整个表"
Command2.Caption = "锁定ID=1的记录(xLock)"
Command3.Caption = "锁定ID=1的记录(ADO)"
End SubPrivate Sub Form_Unload(Cancel As Integer)
On Error Resume Next
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
Set Form1 = Nothing
End Sub