When to Use Freelocks in a Multi-User Environment

ID: Q122958

The information in this article applies to:

Microsoft Visual Basic Standard and Professional Editions for Windows, version 3.0

SUMMARY

This article explains when and where to use the FreeLocks statement in your database program to prevent unintentional read locks.

MORE INFORMATION

The database engine in Microsoft Visual Basic relies on background processing to keep all records current in a recordset and to remove read locks. Usually, read locks are removed and data in local dynaset objects is updated only when no other actions (including mouse moves) are occurring. If data processing is so intense that normal background management of locks is unable to keep up, you may encounter various lock errors, usually Error 3186:

Couldn't save; currently locked by user '<UserName>' on machine '<MachineName>'.

or Error 3260:

Couldn't update; currently locked by user '<UserName>' on machine '<MachineName>'.

Certain operations, such as using a Seek method on a Table object, temporarily lock a recordset and prevent others from accessing the table. Performing a Seek places a read lock on the index while the seek is performed. After the Seek is finished, if the database engine is not allowed time to release its read lock, other users may be locked out from manipulating the table.

This is where the FreeLocks statement comes in handy. According to the Visual Basic online Help, the FreeLocks statement "suspends data processing, allowing the database to release locks on record pages and make all data in your local Dynaset objects current in a multiuser environment."

In addition to FreeLocks, if your program is tying up Windows and the CPU, you should follow the FreeLocks command with a DoEvents statement. FreeLocks allows the database engine to release its read locks; DoEvents allows Windows to catch up on processing background tasks such as repainting portions of the screen or processing user input. DoEvents should be used carefully, however, because it allows your VB code to be reentrant.

You will want to implement the FreeLocks statement after the following operations:

Opening Recordsets: Using CreateDynaset and CreateSnapShot, places a read lock on the table while the object is initialized. For example:


   Dim db As Database, ds As DynaSet
   Set db = OpenDatabase("Bibio.MDB")
   Set ds = db.CreateDynaset("Select * From Authors")
   FreeLocks

Searching: Use FreeLocks after performing a Seek, FindFirst, FindPrevious, FindNext or FindLast. Searches place a read lock on an index, if available, or on the table while the search is performed. For Example:
```
   Dim db As Database, tb As Table
   Set db = OpenDatabase("Biblio.MDB")
   Set tb = db.OpenTable("Titles")
   tb.Index = "Au_ID"
   tb.Seek "=", 6
   FreeLocks
 
```

Deleting and Updating records: Performing a Delete or Update on a recordset locks the page that the current record is on while the record is removed or added to the table. Use FreeLocks immediately following the Update. For example:
```
   ds.Edit
   ds("Favorite Music").Value = "Jazz"
   ds.Update
   FreeLocks
 
```

Error Events: If you implement error trapping in your program, you should add a FreeLocks statement in the On Error event to handle the case where a database error occurs. For example:
```
   Error_Handler:
      If (Err = 3186) Or (Err = 3260) Then   'Currently Locked DB Errors

         FreeLocks
         ...
      End If
      Resume
 
```

NOTE: while it is useful to implement the FreeLocks statement, you must be careful not to implement too many of them. Overuse of FreeLocks may result in poor performance.

Additional query words: 3.00


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: June 4, 1999