Multi-User Locking Methods in Visual Basic

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_RETRYCANCEL = 5
Const MB_YESNO = 4
Const IDCANCEL = 2
Const IDRETRY = 4
Const IDNO = 7
Const DB_DENYWRITE = &H1
Const DB_DENYREAD = &H2
Const ERR_RESERVED = 3000
Const ERR_CANT_OPEN_DB = 3051
Const ERR_CANT_LOCK_TABLE = 3262
Const ERR_DATA_CHANGED = 3197
Const ERR_RECORD_LOCKED = 3260
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
ds.Edit
Select Case Err
Case 0
'No error happened...OK
bUnLocked = True

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

Case ERR_RECORD_LOCKED
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%
Case IDCANCEL
'Cancel means quit the Functon
Exit Sub
Case IDRETRY
'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
Wend
'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
ds.Update
ds.MoveNext
Loop

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.