同系列的第十篇,上一篇在http://blog.csdn.net/jiluoxingren/article/details/48606399
【增补篇】游标Cursor和锁Lock简介
说起来,我自己对游标并不怎么了解。这里简单就我所知道的做一些介绍。之所以要说一下,是因为趁着出增补篇的机会,将游标和锁也要提及一下,尽管这在VB中的重要性并不明显,但是游标的功能你每一次读写都在用,锁在背后影响着你的操作。这篇增补篇也是为了修正以前代码上的错误,虽然看不到影响,那是数据库引擎在背后修正的结果。
游标
在RecordSet对象的Open方法中,第三,四个参数就是游标和锁。如下图所示
关系数据库中的操作会对整个记录集起作用。例如,由SQL:Select语句返回的记录集包括满足条件的所有记录。应用程序并不总能将整个记录集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一条记录或一部分记录。游标就是提供了这种机制。
在VB中的操作是一个典型的例子,之前用ListBox来显示的时候,我们需要用循环逐条记录进行提取和显示。而且,MoveNext,MovePrevious等移动操作都是由游标提供的。
按照游标的支持类型,可以分为Transact-SQL游标,API游标,客户游标。
1. Transact-SQL游标:由T-SQL的DECLARE CURSOR语句定义,主要用在服务器。
2. API游标:支持在OLE DB,ODBC等,主要用在服务器上。客户端调用API游标函数,本地引擎会将这些请求传送到服务器(不一定是联机服务器,这里的服务器甚至可能指的是自己的电脑,由本地的数据库引擎负责这些功能)进行处理。
3. 客户游标:主要在客户机上缓存结果集时才用的。
前两者因为一般用在服务器上,故称服务器游标。而Recordset默认使用服务器游标。VB中,Recordset对象的CursorLocation属性可以设置使用服务器游标(adUseServer)还是客户游标(adUseClient)。客户游标的功能一般比服务器游标弱。
按照游标的特性,游标有四种,分别是:静态游标,动态游标,只进游标,键集游标。这四种游标实现的效果各不相同。
1. 静态游标:对应VB的adOpenStatic常量。静态游标将查询的结果记录集生成一个快照,所以别人对原始数据的增删改都不会对我已经读出来的结果有影响。静态游标的数据是只读的,不能做其他的任何操作。
2. 动态游标:对应VB的adOpenDynamic常量。动态游标是消耗资源最大的。但是别人对原始数据的增删改都能表现出来,当然自己的操作肯定也是可以表现出来的。
3. 只进游标:对应VB的adOpenForwardOnly常量。这是最省资源的游标。但是他只读的,而且只能往下一条记录读,如果想倒回去,那是不支持的。所以调用MovePrevious方法将会失败。
4. 键集游标:对应VB的dOpenKeyset常量。这种游标介于动态与静态之间。你无法看到别人增加的数据;如果别人删除了数据,你访问被删除的数据就会出错;如果别人修改了数据,你将能够看到更改。
能不能看到增删改的结果,是对于别人做的增删改而言的。如果游标支持增删改,那么自己通过游标所做的所有变动都可以看到。在VB上通过recordset对象进行的增删改,相当于是自己通过游标进行操作,所以在update之后,用MoveLast就能读到新的数据。
另外,VB中还有一个用于说明不指定特定的游标的常量adOpenUnspecified,让数据库引擎自己去选。
锁
既然静态游标是只读的,那么为什么adOpenStatic,adLockOptimistic的搭配却能够增删改呢?
这里我特别说明了是adOpenStatic,adLockOptimistic的搭配。adLockOptimistic是锁的选项。锁的选项与数据能否被修改密切相关。
但是游标的设置与锁的设置并不一定能够兼容,这种情况下数据库引擎并不会报错,而是会主动选择一种合适的游标。也就是说,锁的设置会影响游标的设置(仅仅在ADO中是这样,其他地方不清楚)。而之前说的adOpenStatic, adLockOptimistic的搭配正是不兼容的搭配之一。
在说完锁之后,相信大家就能理解为什么是不兼容的了。而至于在这种搭配下,数据库引擎到底选择了那种游标。在第三部分【锁与游标搭配】中在说。
在并发操作中经常会出现如下的问题:A读取数据Data,B也读取了数据Data。A修改并提交了,B随后也修改数据提交了。最终数据只保留了B的结果,A的结果丢失了。而我们需要的结果可能是:A修改之后,B更新数据,然后再根据当前的情况做修改。注意A,B可能并不知道彼此的存在,也不可能事先知道彼此的先后关系,他们的关系只有数据库管理系统(DBMS)知道。
锁是为并发操作而生的。数据库的一大特性就是高度的数据共享。为了避免上述的情况(实际上并发操作还会引起其他的问题),引入了锁机制。引入锁机制之后是这样的:A读取数据Data,并宣布我要修改数据。B只是晚了点,不过DBMS已经因为A而锁定了数据,B先等等吧。
锁也有很多种,ADO中不是提供你选择使用什么锁,而是让你选择锁的工作方式。锁工作的方式有以下4种:
常数(VB) | 锁的工作方式 |
adLockReadOnly | 默认值,只读。无法更改数据。 |
adLockPessimistic | 保守式记录锁定(逐条)。提供者执行必要的操作确保成功编辑记录,通常采用编辑时立即锁定数据源的记录的方式。 |
adLockOptimistic | 开放式记录锁定(逐条)。提供者使用开放式锁定,只在调用 Update 方法时锁定记录。 |
adLockBatchOptimistic | 开放式批更新。用于与立即更新模式相反的批更新模式。 |
adLockPessimistic和adLockOptimistic的不同在于锁定的时机不同。前者保守,以防万一,从事务开始就锁定数据。后者开放,我改我的,只要我还没提交(Update),你随便弄。而他们两者都是以一条语句为单位的。
adLockBatchOptimistic与上面两者不同的是以批(多条语句的集合)为单位进行提交,一个批是一个整体。
锁与游标搭配
如果锁的工作方式与指定的游标不兼容,数据库引擎会改变游标。于是,在Open方法中提供的游标和锁就会有不同的搭配,这些不同的搭配中有不少都是不兼容的。下面列出所有的服务器游标(adUseServer)搭配,以及对应的Jet数据库引擎实际采用的游标类型:
设定的游标 | 设定的锁 | 实际使用的游标 |
adOpenUnspecified | adLockReadOnly | adOpenForwardOnly |
adLockPessimistic | adOpenKeyset | |
adLockOptimistic | ||
adLockBatchOptimistic | ||
adOpenForwardOnly | adLockReadOnly | adOpenForwardOnly |
adLockPessimistic | adOpenKeyset | |
adLockOptimistic | ||
adLockBatchOptimistic | ||
adOpenKeyset | adLockReadOnly | adOpenKeyset |
adLockPessimistic | ||
adLockOptimistic | ||
adLockBatchOptimistic | ||
adOpenDynamic | adLockReadOnly | adOpenStatic |
adLockPessimistic | adOpenKeyset | |
adLockOptimistic | ||
adLockBatchOptimistic | ||
adOpenStatic | adLockReadOnly | adOpenStatic |
adLockPessimistic | adOpenKeyset | |
adLockOptimistic | ||
adLockBatchOptimistic |
这个表是通过代码试验出来的,代码见最后附录。由这个表可知,Jet数据库引擎并不支持动态游标,它与任何一个锁方式搭配都不会使Jet数据库引擎使用动态游标。
而adOpenStatic与adLockOptimistic的组合也是不兼容的,最终Jet数据库引擎选择了adOpenKeyset(键集)游标。而键集游标是可以修改的。
以上都是服务器游标的效果,如果修改CursorLocation属性为adUseClient,那么无论怎么搭配,结果使用的都是键集游标。
最后强调一点,前面几段开始,一直强调这是Jet数据库引擎的行为,并不是所有引擎都是如此!!!
不同游标的效果
前面介绍的四种游标,在上一节已经确定了一种——动态游标是不被Jet数据库引擎所支持的。这并不难理解,不止这个引擎不支持,还有别的一些引擎也不支持,因为这种游标太过强大,但是其缺点也非常明显。
另外的三种,可以发现键集游标(adOpenKeyset)是应用得最为广泛的。而adOpenStatic与adLockOptimistic的组合,实际上使用的也是键集游标。所以以后recordset的Open语句都改为使用键集游标和开放式的锁。如下语句所示:
VB代码开始:
' adOpenKeyset(键集游标),adLockOptimistic(开放式锁)
rs.Open "select * from Student", cn, adOpenKeyset,adLockOptimistic
VB代码结束
尽管之前我们并没有直接使用键集游标,但是由于数据库引擎的主动修正,我们认为自己用的是静态游标adOpenStatic,实际上用的是键集游标。键集游标的性质:可以反映自己所做的增删改,可以反映别人所做的修改。自己的增删改已经毋庸置疑,也不需要演示了,参见第四章即可,用recordset对象的属性进行增删改本质就是借助了游标。
然后我们来看一下,键集游标反映别人做的修改的能力。基本思路是这样。建立两个recordset对象,执行一样的查询。然后用一个recordset对象对一条记录进行更改;另一个对象重新输出自己的数据,观察结果。界面如下:
Text1用于输入StudentID,Text2用于输入StudentName。修改的时候只用到Text2,而且为了方便定第一条记录为被修改的记录。新增的时候两个文本框都要填。
VB代码开始:
Dim Cnn As New ADODB.Connection
Dim rec1 As New ADODB.Recordset
Dim rec2 As New ADODB.Recordset
'用rec1修改第一个人的名字
Private Sub Command1_Click()
'移动回开头
rec1.MoveFirst
rec1.Fields("StudentName").Value = Text2.Text ' 修改第一条记录的名字
rec1.Update
'清空List1重新填充数据
rec1.MoveFirst
List1.Clear
Do Until rec1.EOF
List1.AddItem rec1.Fields("StudentName").Value
rec1.MoveNext
Loop
'清空List2重新填充数据
rec2.MoveFirst
List2.Clear
Do Until rec2.EOF
List2.AddItem rec2.Fields("StudentName").Value
rec2.MoveNext
Loop
End Sub
'用rec1新增记录
Private Sub Command2_Click()
'新增一个人的信息
rec1.AddNew
rec1.Fields("StudentID").Value = Text1.Text
rec1.Fields("StudentName").Value = Text2.Text
rec1.Update
'清空List1重新填充
rec1.MoveFirst
List1.Clear
Do Until rec1.EOF
List1.AddItem rec1.Fields("StudentName").Value
rec1.MoveNext
Loop
'清空List2重新填充
rec2.MoveFirst
List2.Clear
Do Until rec2.EOF
List2.AddItem rec2.Fields("StudentName").Value
rec2.MoveNext
Loop
End Sub
Private Sub Form_Load()
'注意要记住该数据库目录为你数据库文件当前的位置
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Sample.mdb;Persist Security Info=False"
'打开student表的全部字段
rec1.Open "SELECT * FROM Student", Cnn,adOpenKeyset, adLockOptimistic
rec2.Open "SELECT * FROM Student", Cnn, adOpenKeyset,adLockOptimistic
'读取数据
Do Until rec1.EOF
List1.AddItem rec1.Fields("StudentName").Value
rec1.MoveNext
Loop
Do Until rec2.EOF
List2.AddItem rec2.Fields("StudentName").Value
rec2.MoveNext
Loop
End Sub
Private Sub Form_Unload(Cancel As Integer)
rec1.Close
rec2.Close
Cnn.Close
End Sub
VB代码结束
代码并不复杂,主要思路就是建立两个Recordset,Open的时候按一样的方式打开。用一个来修改,重新读取另一个看看数据有没有更新。现在运行程序,填写Text2为王伟。单击【用rec1修改第一个人的名字】(默认修改第一个记录)。
对比一开始的界面图,rec2只是重新读了一遍数据,同样地第一条记录林则徐被改成了王伟。在Access将王伟还原为林则徐,这些数据项以后还要用的!
填写Text1为161725(不要与已有的重复就行),Text2填写吴曦,然后按【用rec1新增记录】。结果Access刷新一下显示出来了,左边的rec1的列表也显示出来了,但是右边的rec2列表却没有显示出吴曦。证明键集游标不能反映出别人的新增,同样删除也是不能反映出来的。如下图所示:
在Access将吴曦的PhoneNo填为14328745601,Age填为17,CreateDate填为2015/09/25。这样我们就多了一条记录。
在说了键集游标之后。来看一下真正的静态游标。通过【锁与游标搭配】一节的表,我们知道,只有adOpenStatic和adLockReadOnly的组合会使数据库引擎使用静态游标。静态游标的特点是只读,但是与只进游标不同的是,静态游标可以前后随意检索。前后随意检索在MovePrevious,MoveNext方法中得以体现,所以就不演示了。现在我们来尝试一下用静态游标尽行新增。
代码还是用上面的,只是rec1的open方法的最后两个参数改成adOpenStatic和adLockReadOnly,其他的代码都保持不变。填好Text1和Text2之后,按【用rec1新增记录】按钮来尝试新增。
当单击【用rec1新增记录】按钮的时候出现错误3251,错误描述为:不支持更新。这个不支持更新既是静态游标的效果,也是adLockReadOnly锁的效果。
最后一项是只进游标。顾名思义只前进不退,所以MovePrevious是无法使用的。将rec1的open方法的最后两个参数改成adOpenStatic。在Form_Load的最后调用一下MovePrevious就可以看到效果了。
VB代码开始:
Private Sub Form_Load()
'……,节约位置省略了
'打开student表的全部字段
rec1.Open "SELECT * FROM Student", Cnn, adOpenForwardOnly, adLockReadOnly
'……,节约位置省略了
Do Until rec2.EOF
List2.AddItemrec2.Fields("StudentName").Value
rec2.MoveNext
Loop
'新加的这句在这里并没有什么用,只是调用一下这个方法看看效果
rec1.MovePrevious ' 这里会出错
End Sub
VB代码结束
单击上图中的调试按钮,就可以看到出错的代码是MovePrevious的调用了。
只能前进,不能后退,同时也不允许任何增删改,这就是只进游标的特点。
虽然SQL Server支持动态游标,但是由于Jet数据库引擎不支持,Access本身暂时无法验证,所以也就不演示了,即便在SQL Server演示也意义不大。大家知道动态游标能反映所有的变动就可以了。
但是值得一提的是,只进游标是效率最高的游标,也是资源消耗最小的游标类型。如果只是为了读取数据(如用于显示),并不打算作变动,建议使用只进游标(adOpenForwardOnly,adLockReadOnly),毕竟用于显示的话,只进取出每条数据来显示就足够了。在需要回退的情况下用静态游标(adOpenStatic, adLockReadOnly)。在需要增删改的情况下用键集游标(adOpenKeyset, adLockOptimistic)。
所有游标类型功能由弱到强,资源消耗由小到大排序如下:
只进游标<静态游标<键集游标<动态游标(Jet不支持)
这一章的内容作为了解。最后一段红色字就是文章的结论。如果目前无法理解文章的内容,并不要紧。先记住结论,再来慢慢看也不迟。文章以后为了方便,统一使用键集游标。之前几篇文章中,adOpenStatic和adLockOptimistic的组合已经全部将adOpenStatic修正为adOpenKeyset。
虽然这是一篇增补篇,但是却比正篇还要长。毕竟这是一般的数据库书会用两章(书的一章等于博文的两三章的篇幅)甚至更多来讲述的内容。但是我们目前还不需要了解得特别详细。
附录
得出不同游标与锁常数的组合实际使用的游标的代码。在对象浏览器中,选择库ADODB。其中可以看到游标类型的常数以及锁工作方式的常数,这里给出其VB定义:
VB代码开始:
Public Enum CursorTypeEnum
adOpenDynamic = 2 '动态游标(Jet数据库引擎不支)
adOpenForwardOnly = 0 '只进游标
adOpenKeyset = 1 '键集游标
adOpenStatic = 3 '静态游标
adOpenUnspecified = -1 '不指定游标
End Enum
Public Enum LockTypeEnum
adLockBatchOptimistic = 4 '开放式批锁定
adLockOptimistic = 3 '开放式锁定
adLockPessimistic = 2 '保守式锁定
adLockReadOnly = 1 '只读锁定
adLockUnspecified = -1 '不指定锁定
End Enum
VB代码结束
接下来是试验用的代码。注意不要将上面的定义也写到代码里,否则就和ADODB库的重复了。
VB代码开始:
Dim Cnn As New ADODB.Connection
Dim rec As New ADODB.Recordset
Private Sub Form_Load()
'注意要记住该数据库目录为你数据库文件当前的位置
Cnn.Open"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Sample.mdb;Persist Security Info=False"
'按不同方式打开记录集
For i= -1 To 3'游标类型,从-1到3
For j = 1 To 4'锁工作方式,-1(没测试),1到4
rec.Open "SELECT * FROM Student", Cnn, i, j
List1.AddItem i & "&"& j
List2.AddItem rec1.CursorType &"&" & rec1.LockType
rec.Close
Next
'每一个游标后插入一个空行,方便看
List1.AddItem ""
List2.AddItem ""
Next
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cnn.Close
End Sub
VB代码结束
运行界面很简单,就是上面演示的界面少了那两个文本框和按钮。