When to Use Freelocks in a Multi-User Environment

Last reviewed: June 21, 1995
Article ID: Q122958

The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic 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 reference words: 3.00
KBCategory: kbprg
KBSubcategory: APrgData

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.