Multi-User Locking Methods in Visual Basic

原创 2002年04月11日 18:06:00

Multi-User Locking Methods in Visual Basic

By: Kevin Laughton

Id like to thank Don Willits (Microsoft) for his assistance.

Multi-user database access has been a trial and error approach for most of us. While there are really three choices of locking scenarios, only one is a real concern. The first method is to lock the entire Database by opening it exclusively. Obviously, this is not an option in a multi-user environment due to the brutal impact it would have on all but the first user. For pretty much the same reason, option number 2, Table locking, is also out. So the focus of this article is on choice number 3, Page locking. While you may already know about page locking, I urge you to read on since I'll mention a couple of things in here that I don't think you'll find anywhere else.

99% of the time a multi-user system wants to lock the smallest amount of data possible so as to not hinder the performance of other users. Since VB does not support record locking, the smallest amount of data rows that can be locked is a Page or roughly 2K of data. Actually locking the page is not the focus of this article; the reason being that this is done AUTOMATICALLY for you by the Access database engine (JET). However, we must write code that will effectively handle situations where we try to update a record in a locked page.

At this point I should mention the different types of page locking. The default page locking type is Pessimistic Locking. Lets use the Edit and Update methods for manipulating the database. In Pessimistic locking, the page is locked when you make the call to the Edit method and unlocks the page when you make the Update method call. The second locking type is Optimistic locking. Using this type of locking, the page is locked only when you make the Update method call. Well have to do some extra work in this case, but it is an option.

There are a few things to bear in mind that are not particularly clear from the manuals. First, since the entire page is locked, and a given page may contain more then one record, users need not be on the exact same record to cause a locking conflict. Second, these locking methods apply to the index pages as well. What this means is that when the Seek method is used or indexes are being rebuilt, the index pages are locked on a 2K page basis. This can also cause locking errors, which the programmer should handle appropriately. Third, the AddNew method also locks the page the same way an Edit method call does. This prevents two users from adding the same data to a table and causing other types of errors.

Speaking of errors, there are two main run-time errors to trap for with page level locking. The first is error 3260 "Couldnt Update; currently locked by user x on machine y." This error means that another user has already locked the page containing your data.

The second is error 3197 "Data has changed; operation stopped." In this case the data you retrieved and are trying to update has already been changed by another user. In other words, the set of data you are currently dealing with is out of date. This error is just warning you that you are about to overwrite someone elses work. Its as if we both opened a MS Word document and I tried to save my changes just after you did. If I didnt get an error I would overwrite all of your changes with mine. This error occurs only once per operation, so if you try the same operation again, you wont get the error generated again.

One interesting thing about Error 3197 and SnapShots is that you can generate this error just by moving through a SnapShot the contains Memo fields. Since Memo fields can be really large, SnapShots only contain references to them as opposed to placing the whole memo contents into the SnapShot. If I go to record 10 and you update record 5, the next time I visit record 5 by moving to it, I will generate a 3197 error since the data has been changed.

Here is an example of multi-user database page locking and the error handling needed to make it work. Note that the database routines are pulled from my CyberStyle column and can be found in the module DB.BAS. Please refer to the CyberStyle section for an explanation of how they work. Also note that I have not included any code that makes use of the data control supplied with VB3. It has been my experience that the data control is of little value in the real world and that true power and control only comes with using the data access objects in the VB3 professional edition.

Const MB_YESNO = 4
Const IDCANCEL = 2
Const IDRETRY = 4
Const IDNO = 7
Const ERR_RESERVED = 3000
Const ERR_CANT_OPEN_DB = 3051
Const RERR_ExclusiveDBConflict = "-8194"

Sub Command1_Click ()

Dim db As database
Dim ads() As Dynaset
Dim ds As Dynaset 'Used only to keep code simple
Dim ret As Integer
Dim bUnLocked As Integer

ret% = DB_Connect("BIBLIO.MDB")
ret% = DB_Query2Dyna("Authors", ads())

'This next line is not needed but makes the code easier to read without the ads(0) stuff.
Set ds = ads(0).Clone()

Do Until ds.EOF = True
'Attempt to access records, checking for possible page locking conflicts
bUnLocked = False
'Disable any previous error handler and instead, just resume next
On Error Resume Next
While Not bUnLocked
Err = 0
Select Case Err
Case 0
'No error happened...OK
bUnLocked = True

ret% = MsgBox("Record has been updated. Overwrite?", MB_RETRYCANCEL)

ret% = MsgBox("Record in use by another user. Try Again?", MB_RETRYCANCEL)

Case Else
MsgBox "Unexpected error" & Str$(Err) & " editing record."
Exit Sub
End Select

Select Case ret%
'Cancel means quit the Functon
Exit Sub
'Retry means try again in the loop. Note that the retry for the ERR_DATA_CHANGED
' will always work the second time through since the Edit method will
' not generate this error again for this series of events.
bUnLocked = False
End Select
'disable error trapping OR place On Error statements
'pointing to a new error handler here
On Error GoTo 0

ds("Author") = ds("Author")

' With Optimistic locking you would check locking on Update vs. Edit

ret% = DB_CloseDyna(ds)
ret% = DB_CloseDatabase()
End Sub

For Optimistic locking, you would want to check for locking errors on the Update method, rather then the Edit methods.

Visual Basic快捷教程——流程控制

  • baimafujinji
  • baimafujinji
  • 2017年04月04日 15:14
  • 3093

怎样解决无法初始化Visual Basic环境的问题

经常使用word软件,难免是遇到这样那样的问题,今天笔者在录制新宏时出现错误提示“无法初始化Visual Basic环境”,对于此问题我们该如何来解决。 工具/原料 ...
  • kyfvc
  • kyfvc
  • 2013年10月30日 09:57
  • 1532

Visual Basic快捷教程——函数与子程序

函数是结构化程序设计的基础。函数的本质就是为了把程序切分成若干个相对独立的模块各司其职,而各个函数的作用也就是仅仅专门负责自己份内的那份功能。在Visual Basic中,函数是一组以Function...
  • baimafujinji
  • baimafujinji
  • 2017年04月19日 22:30
  • 2897

【Visual Basic】计时器与窗体屏幕尺寸的取法

主要是利用一个小例子讲解VB中计时器Timer的运用。 如下图所示: 有一个显示当前时间,在屏幕的边缘不停地移动。 首先是窗体设置,直接从工具箱拖入Label与Timer控件。Timer...
  • yongh701
  • yongh701
  • 2015年08月06日 09:30
  • 1305

数据库——SQL server2008安装与visual basic连接

  • u013030601
  • u013030601
  • 2014年06月29日 11:32
  • 1487

Visual Basic快速入门

Basic语言的全名是Beginner’s All-purpose Symbolic Instruction Code(初学者通用的符号指令代码),是几十年前流行过的一门语言。我高中时候一门数学选修课...
  • u011054333
  • u011054333
  • 2018年01月05日 18:26
  • 48

Visual Basic快捷教程——异常处理机制

  • baimafujinji
  • baimafujinji
  • 2016年01月23日 19:12
  • 6264


汇编语言:         汇编是第一个计算机语言。汇编语言实际上是你计算机处理器实际运行的指令的命令形式表示法。这意味着你将与处理器的底层打交道,比如寄存器和堆栈。如果你要找的是类英语且有相关的自...
  • u013679128
  • u013679128
  • 2014年03月10日 19:25
  • 1614

Visual Basic快捷教程——变量、常量和运算符

  • baimafujinji
  • baimafujinji
  • 2017年07月25日 17:08
  • 1037

双重检查锁定失败可能性——参照《The "Double-Checked Locking is Broken" Declaration》

双重检查锁定失败可能性——参照《The "Double-Checked Locking is Broken" Declaration》  双重检查锁定在延迟初始化的单例模式中见得比较多(单例模式实...
  • gerryzhu
  • gerryzhu
  • 2013年12月24日 09:50
  • 2797
您举报文章:Multi-User Locking Methods in Visual Basic