LAN INDEX INTEGRITY

By Phil Talksy and Chuck Litzell

dBASE III PLUS programs which run on a local area network (LAN)
require extra programming consideration if database files are to
be accessed simultaneously by multiple users. The FLOCK() and
RLOCK() functions, which control access to files and records,
respectively, must be used to prevent data collisions.

The dBASE Administrator accesses the DOS 3.x file-handling
services. When a disk file is to be updated, dBASE sends the data
to DOS. Normally, DOS buffers data, writing to the disk file only
when the file buffers are full, or the file is closed.
Record-locking ensures that database records are updated
immediately on the file server. It also prevents users from
attempting to update the same record simultaneously.

Indexes at Risk

However, record-locking does not guarantee that index files open
against a database will be updated properly. If a workstation
updates key fields in a database, the index file must also be
updated. Following normal operation, dBASE sends the data to be
written in the index file to DOS. DOS will buffer the changes
until forced to write them to disk. If several users are updating
key fields in a database, there is a good chance that index files
will be corrupted.

The problems with indexes arise because of the way that the dBASE
Administrator handles them. On a LAN, dBASE maintains a picture
of the index in local workstation memory. When two workstations
are updating the same index, if the local "picture" of the index
is not refreshed after every update by any workstation, the
changes made by the other workstations are overwritten. When this
happens, users get records that drop out of the index or
corrupted indexes.

The program, Bad.PRG, when run by two users, consistently caused
index corruption. (The file and field names have been changed to
protect the innocent.)

To prevent index file corruption, the database should be locked
to ensure that no other workstations have the index file open.
FLOCK() doesn't lock an index file, but if you can lock the
database file, you can be sure its indexes are not in use at
another workstation. Index files should be closed immediately
after key fields have been updated, in order to write the changes
to the file server.

Bad.PRG was modified (producing Good.PRG) to guarantee that
indexes could be updated without collision. The file is locked
prior to the APPEND BLANK and not UNLOCKed again until the key
fields have been REPLACEd and the index file has been closed to
flush the buffers and write changes to the server. Then the
record is locked with RLOCK() and the remaining fields are
REPLACEd.

With these modifications, the program ran successfully with nine
users entering data simultaneously.

Bad.PRG
* Program..: Bad.PRG
* Author...: Phil Talsky
* Date.....: November 1, 1987
* Notes....: Bad.PRG demonstrates the wrong way to use indexes in
* dBASE III PLUS Administrator.
*
SET TALK OFF
ON ERROR DO Cretry
SET EXCLUSIVE OFF
SET SAFETY OFF
CLEAR
m1 = " "
counter = 0
*
* --- Testndx is indexed on F1 + F2.
USE Testfile INDEX Testndx
DO WHILE .T.
counter = counter + 1
m2 = STR(counter, 3)
@ 10, 5 SAY "Initial (X to quit) ";
GET m1;
PICTURE "!"
@ 11, 5 SAY "Number " + m2
READ
IF m1 = "X"
EXIT
ENDIF
APPEND BLANK
mrec = RECNO()
SET INDEX TO Testndx
GO mrec
DO WHILE .NOT. LOCK()
* DO nothing.
ENDDO
*
* ---REPLACEs on fields F3 - F7 are arbitrary.
REPLACE F1 WITH m1,;
F2 WITH m2,;
F3 WITH "XXXXX",;
F4 WITH "YYYY",;
F5 WITH "ZZZZ",;
F6 WITH "OOOOOOOOOOOO",;
F7 WITH REPLICATE("Q-", 10)
UNLOCK
ENDDO
* ---Display to show index corruption.
DISPLAY ALL F2, F1
ON ERROR
USE
RETURN
* ---EOP Bad.PRG.

Good.PRG
* Program..: Good.PRG
* Author...: Phil Talsky
* Date.....: November 1, 1987
* Notes....: Good.PRG demonstrates correct methods to preserve index
* integrity in dBASE III PLUS Adminstrator.
*
SET TALK OFF
SET EXCLUSIVE OFF
SET SAFETY OFF
CLEAR
m1 = " "
counter = 0
*
* --- Testndx is indexed on F1 + F2.
USE Testfile INDEX Testndx
DO WHILE .T.
counter = counter + 1
m2 = STR(counter, 3)
@ 10, 5 SAY "Initial (X to quit) ";
GET m1;
PICTURE "!"
@ 11, 5 SAY "Number " + m2
READ
IF m1 = "X"
EXIT
ENDIF
*
* ---Lock the file for APPEND BLANK. APPEND BLANK issues an automatic
* ---file lock, but then UNLOCKs immediately. Therefore, you want to
* ---force the file lock, to ensure that it remains locked.
*
* ---This method for file locking does not require an ON ERROR routine
* ---to control the DO WHILE .NOT. FLOCK() section because other workstations
. * ---lock the file for a very short time.
*
DO WHILE .NOT. FLOCK()
* ---DO nothing.
ENDDO
SET INDEX TO Testndx
APPEND BLANK
*
* ---After adding the blank record, REPLACE only the key fields.
* ---This ensures that the index is "locked" during its update.
*
REPLACE F1 WITH m1, F2 with m2
*
* ---After you replace the key fields, close the index.
* ---This ensures that all changes are written to disk before the file
* ---is unlocked.
*
CLOSE INDEX
UNLOCK
*
* ---Immediately after UNLOCKing the file, LOCK the record to finish the
* ---REPLACE statements.
*
DO WHILE .NOT. RLOCK()
* ---Do nothing.
ENDDO
*
* ---The following REPLACEs are arbitrary data.
REPLACE F3 WITH "XXXXX",;
F4 WITH "YYYY",;
F5 WITH "ZZZZ",;
F6 WITH "OOOOOOOOOOOO",;
F7 WITH REPLICATE("Q-", 10)
UNLOCK
ENDDO
* ---Display to show index integrity.
DISPLAY ALL F2, F1
USE
RETURN
* EOP Good.PRG