<< Click to Display Table of Contents >> Navigation:  Application development > Locking > SQL-specific locking behavior

With dBASE, Paradox, FoxPro, and Access, a record lock prevents another user from updating the record. However, SQL deals with record locking differently. If a record in a SQL table is not in the record cache, the record is fetched from the server. The client has a local (cached) copy of the record, but that copy can become immediately out-of-date if another client retrieves the same record from the server, and modifies or deletes it before the first client is able to submit changes.

BDE SQL drivers (and some ODBC drivers) use optimistic locking. An optimistic lock actually allows the locked record to be updated by another user, but when the application that placed the lock attempts to update the record, BDE notifies the application that the record has changed and that the requested operation cannot be performed because someone else has modified the data. The application then has the option of inspecting the new record and deciding whether to submit its changes or not.

Optimistic locking avoids the performance and concurrency penalties incurred by a lock that ties up record access for the duration of time that it takes to complete a single user’s modifications. At the same time, the application is protected from inadvertently changing data that has never been inspected.

You can use keyed updates to control optimistic locking for improved performance.