Locking Data

Article
10/23/2006

If you share access to files, you must also manage access to data by locking tables and records. Locks, unlike access permissions, can provide both long- and short-term control of data. Visual FoxPro provides both automatic and manual locking.

Choosing Record or Table Locks

Record locking, whether automatic or manual, prevents one user from writing to a record that's currently being written to by another user. Table locking prevents other users from writing to, but not reading from, an entire table. Because table locking prohibits other users from updating records in a table, it should only be used sparingly.

Choosing Automatic or Manual Locks

In addition to record or table locking, you can also choose automatic or manual locking. Many Visual FoxPro commands automatically attempt to lock a record or a table before the command is executed. If the record or table is successfully locked, the command is executed and the lock is released.

Commands that Automatically Lock Records and Tables

Command	Scope of lock
ALTER TABLE	Entire table
APPEND	Table header
APPEND BLANK	Table header
APPEND FROM	Table header
APPEND FROM ARRAY	Table header
APPEND MEMO	Current record
BLANK	Current record
BROWSE, CHANGE and EDIT	Current record and all records from aliased fields in related tables once editing of a field begins
CURSORSETPROP( )	Depends on parameters
DELETE	Current record
DELETE NEXT 1	Current record
DELETE RECORD n	Record n
DELETE of more than one record	Entire table
DELETE – SQL	Current record
GATHER	Current record
INSERT	Entire table
INSERT - SQL	Table header
MODIFY MEMO	Current record when editing begins
READ	Current record and all records from aliased fields
RECALL	Current record
RECALL NEXT 1	Current record
RECALL RECORD n	Record n
RECALL of more than one record	Entire table
REPLACE	Current record and all records from aliased fields
REPLACE NEXT 1	Current record and all records from aliased fields
REPLACE RECORD n	Record n and all records from aliased fields
REPLACE of more than one record	Entire table and all files from aliased fields
SHOW GETS	Current record and all records referenced by aliased fields
TABLEUPDATE( )	Depends on buffering
UPDATE	Entire table
UPDATE – SQL	Entire table

Record Lock Characteristics

Commands that attempt record locks are less restrictive than commands that lock tables. When you lock a record, other users can still add or delete other records. If a record or table is already locked by another user, an attempted record or table lock fails. Commands that attempt to lock the current record return the error, "Record is in use by another," if the record cannot be locked.

The BROWSE, CHANGE, EDIT, and MODIFY MEMO commands do not lock a record until you edit the record. If you're editing fields from records in related tables, the related records are locked if possible. The lock attempt fails if the current record or any of the related records are also locked by another user. If the lock attempt is successful, you can edit the record; the lock is released when you move to another record or activate another window.

Header and Table Lock Characteristics

Some Visual FoxPro commands lock an entire table while others only lock a table header. Commands that lock the entire table are more intrusive than commands that only lock the table header. When you lock the table header, other users cannot add records, but they can still change data in fields.

Users can share the table without causing a conflict when you issue the APPEND BLANK command, but an error can occur while another user is also appending a BLANK record to the table. You can trap for the error, "File is in use by another," which is returned when two or more users execute APPEND BLANK simultaneously. Commands that lock an entire table return the error, "File is in use by another," if the table cannot be locked. To cancel the attempted lock, press ESC.

Example: Automatic Locking

In the following example, the user automatically locks the table header by appending records from another table, even though customer was opened as a shared file:

SET EXCLUSIVE OFF
USE customer
APPEND FROM oldcust FOR status = "OPEN"

Locking Manually

You can manually lock a record or a table with locking functions.

To manually lock a record or a table

Use one of the following commands:
```
RLOCK()
LOCK()
FLOCK()
```

RLOCK( ) and LOCK( ) are identical and lock one or more records. FLOCK( ) locks a file. The LOCK( ) and RLOCK( ) functions can apply to a table header. If you provide 0 as the record to LOCK( ) or RLOCK( ) and the test indicates the header is unlocked, the function locks the header and returns true (.T.).

Once you lock a record or table, be sure to release the lock by using the UNLOCK command as soon as possible to provide access to other users.

These manual locking functions perform the following actions:

Test the lock status of the record or table.
If the test indicates the record is unlocked, lock the record or table and return true (.T.).
If the record or table cannot be locked, attempt to lock the record or table again, depending on the current setting of SET REPROCESS.
Return true (.T.) or false (.F.), indicating whether the lock attempt was successful.

Tip If you want to test the lock status of a record in your session without locking the record, use the ISRLOCKED( ) or ISFLOCKED( ) function.

If an attempt to lock a record or table fails, the SET REPROCESS command and your current error routine determine if the lock is attempted again. SET REPROCESS affects the result of an unsuccessful lock attempt. You can control the number of lock attempts or the length of time a lock is attempted with SET REPROCESS.

Example: Manual Locking

The following example opens the customer table for shared access and uses FLOCK( ) to attempt to lock the table. If the table is successfully locked, REPLACE ALL updates every record in the table. UNLOCK releases the file lock. If the file cannot be locked because another user has locked the file or a record in the file, a message is displayed.

SET EXCLUSIVE OFF
SET REPROCESS TO 0
USE customer    && Open table shared
IF FLOCK()
 REPLACE ALL contact ;    && Replace and unlock
  WITH UPPER(contact) 
 UNLOCK   
ELSE  && Output message
 WAIT "File in use by another." WINDOW NOWAIT
ENDIF

Unlocking Data

After you establish a record or file lock and complete a data operation in a shared environment, you should release the lock as soon as possible. There are several ways to release locks. In some cases, simply moving to the next record is enough to unlock the data. Other situations require explicit commands.

To unlock a record that's been automatically locked, you need only move the record pointer, even if you set MULTILOCKS ON. You must explicitly remove a lock from a record that you've manually locked; simply moving the record pointer is not enough.

The following table describes the effects of commands on manual and automatic record and table locks.

Command	Effect
UNLOCK	Releases record and file locks in the current work area.
UNLOCK ALL	Releases all locks in all work areas in the current session.
SET MULTILOCKS OFF	Enables automatic release of the current lock as a new lock is secured.
FLOCK( )	Releases all record locks in the affected file before locking the file.
CLEAR ALL, CLOSE ALL, USE, QUIT	Releases all record and file locks.
END TRANSACTION	Releases automatic locks.
TABLEUPDATE( )	Releases all locks after updating the table.

Caution If a record was automatically locked in a user-defined function and you move the record pointer off and then back on the record, the lock will be released. Use table buffering to avoid this problem.

Share via