楼上的做法太不规范了!'----------------------------------------------------------------- '说 明:本程序用于测试锁定 ' '测试环境: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
给纪录加多2个字段,Locked,LockTime。分别表示是否被锁以及何时被锁。
1、锁定纪录
修改Locked为True,LockTime为当前时间。
2、判定被锁
查询Locked标志,如果为True,被锁;否则没有被锁。
3、掉电恢复
数据库每隔一段时间查询一下数据表,看看时候有的纪录已经被Lock了很长时间,如果发现,自动解锁(修改Locked标记)。
'说 明:本程序用于测试锁定
'
'测试环境: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