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
  • 3640

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

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

Visual Basic快速入门

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

Visual Basic 的基本数据类型

  • bodohacker
  • bodohacker
  • 2006年08月26日 13:47
  • 1755

Visual Basic 数字后跟#,&是什么意思 (For 菜鸟)

代表了一种数据类型,是隐式声明Currency  的类型声明字符为at号 (@)  Double  的类型声明字符是数字符号 (#) Integer  的类型声明字符是百分比符号 (%)    Lon...
  • tanaya
  • tanaya
  • 2006年11月01日 00:47
  • 2222

Visual Basic for Applications 学习笔记(一)

一 创建新工作簿 如果要用 Visual Basic 创建新的工作簿,应使用 Add 方法。下述过程创建了新工作簿。Microsoft Excel 自动将该工作簿命名为“BookN”,其中 N 是下一...
  • DeRoshia
  • DeRoshia
  • 2007年01月19日 14:16
  • 922

Visual Basic的类对于面向对象的支持

VB最为人垢病的是它的面向对象特性。实际上VB是一种基于对象的开发工具。在VB中 建立的类是支持继承的。下面是范例: 首先建立一个新工程,然后添加一个新的类模块(Class Module),类名称设定...
  • IceMe
  • IceMe
  • 2004年10月30日 14:49
  • 878

Visual Basic for Application是什么

  • clj198606061111
  • clj198606061111
  • 2010年04月03日 11:45
  • 4162

Visual Basic 常用函数介绍

一、常用内部函数 在VB内部提供了大量函数。在这些函数中,有些是通用的。有些则与某种操作有关的。 大体分成五类,分别是:转换函数、数学函数、日期函数、时间函数和随机函数。 (1)转换函数 函数...
  • oBuDaoWeng
  • oBuDaoWeng
  • 2012年11月17日 22:12
  • 759

Visual Basic 基本控件属性说明之一二

Label控件Label 控件主要用于在窗体中显示提示信息,常与Text控件一起使用。 属性: Caption 表示标签的内容。例:Label1.Caption="N&ame:"控件在窗体中显示Nam...
  • qvbhdnvto
  • qvbhdnvto
  • 2007年04月20日 17:12
  • 1770
您举报文章:Multi-User Locking Methods in Visual Basic