File Locking....

[Paul Squires]

I have been adding locking support to my program by locking and
unlocking ranges of bytes as needed. I have noticed that programs
like Microsoft Access create an additional file that seems to
provide info about locks that are in place.

Is there a good reason why these separate type of locking files
are used? I assume they store info about which records are
locked, when they were locked and by whom so maybe it is this
additional info that the lock file provides that makes it worthwhile.
I guess if you just lock a range of bytes then you won't know who
locked them and when.

What would happen if there was a power outage or some other
interruption that caused the program to not to delete this lock
file? When you reboot the file will still be there with the
locks - the locks would never go away unless you manually
delete the file.

If anyone can point me in a good direction on this I would appreciate
it - or if they have some good links to additional info.

Thanks,



------------------
Paul Squires
www.PlanetSquires.com
mailto:[email protected]
[email protected]
</A>

[Paul Squires]

I just downloaded a document called Microsoft Jet Locking from http://www.wotsit.org/ so I will give that a read.

I guess other database programs like Paradox probably use a
similar strategy.



------------------
Paul Squires
www.PlanetSquires.com
mailto:[email protected]
[email protected]
</A>

[Don Dickinson]

These programs might use this for some sort of transaction logging or maybe they're just locking a file instead of locking records.

I has been my experience that the latest consumer M$ os's (95, 98, me) are not good at handling record locks. There might be some tweaking that would improve upon the default settings, I don't know for sure. I do know that I've seen far, far, far, far fewer errors since I switched to using an external, exclusively locked file instead of record locking.

Here's what I do ...
If i need to update/append a record in somedatafile.dat, I try to open somedatafile.dat.lock exclusively and retry X number of times until it succeeds (or abort on failure).
Then I update/append somedatafile.dat
Then I do a flush on that file.
THen I close my exclusive open of somedatafile.dat.lock

I'm sure this is slower than doing a record lock (single or range), but it has proven much more reliable for me.

Best Regards,
Don

------------------
dickinson.basicguru.com

[Timm Motl]

Paul:

When coding multi-user support into Tsunami, I settled on an approach almost identical to the one described
by Don in his posting above. I had originally written the multi-user portions assuming each file should be
physically opened in shared mode with record or file locks placed as needed to secure exclusive access rights
while modifying files. This seemed like the correct way to do things.

However, testing of file modifications performed across a network connection were disappointing at best...
somewhere around 13-14 records could be inserted per second in multi-user mode using "lock" calls, while
around 120 records could be inserted across a network when the file was opened in single-user mode.

I eventually came up with an idea for a "virtual open" for multi-user access. When a file is opened in
multi-user mode in Tsunami, it is physically opened, header info is read into memory and the file is then
physically closed. Even though the file is physically closed, Tsunami internally treats the file as "open"...
positioning info is maintained just as if the file were physically open. Subsequent accesses to the file
require Tsunami to internally perform exclusive open and close operations (as well as a header check to see
if the file had been modified since last read), but this approach provided much improved performance...
56-57 records could be inserted per second across a network connection.

The "virtual open" approach not only proved substantially faster, but added the reliablity mentioned by Don...
the file is absolutely "flushed" to disk and directory structures are updated by the physical close before
any other user or thread can access the file.

This required a lot of rewriting (and a major shift in the way I thought about file access) but in the end
proved to be much faster and very solid during massive multi-user "attacks" on a file.

I don't know if this approach is feasible in your situation, but I was very happy with it.

Timm




[This message has been edited by Timm Motl (edited August 19, 2001).]

[Paul Squires]

Thanks guys

Timm, how do you handle the following scenario.... Say one user
gets a record from the database with the intention of making
modifications to it. The info could be loaded into an editing
form on the screen. The user takes about 5 minutes to make the
changes and then commits the changes back to the database.

In the meantime, other users are also writing to your database
and even to the same record that is still sitting on the screen
in the data entry form. If I read your post correctly then Tsunami
does not lock the record (the whole file is just opened in exclusive
mode) - so there is no way to be 100% sure that the data that is
on the screen in the edit form is actually the very latest?? My
thought would be to place a lock on those records bytes until
the user released the lock by commiting the changes to disk. Of
course there could be some logic to allow "read through" access
so other users who simply need to retrieve the record for viewing
would be able to do so.

Am I correct in assuming this about Tsunami ???



------------------
Paul Squires
www.PlanetSquires.com
mailto:[email protected]
[email protected]
</A>

[Timm Motl]

Paul:

I'm using passive concurrency... in other words, if two users load the same record into their editing
screens, and user "A" changes the record and updates the file, user "B" would get a result code 22 from
Tsunami (lost record position) if he/she also tried to update (or delete) the record. The programmer has
to handle that situation if it arises... a message could be presented to the user explaining that the record
on screen has been modified since it was initially read, or (if it makes sense for the application in question)
the update could still be done by reloading the record to get current positioning and then performing an update
using the info on screen. That's really an application specific decision, although I imagine the first would
be most common.

Timm

[Paul Squires]

Understood, thanks Timm.

I just read the Microsoft Jet Locking document and it will take
another couple of reads to digest the info. The versions of Access
referenced in that document uses page-locking instead of record
locking (therefore it is possible for more than one record to be
locked because the whole page is locked). The latest version of
Access now allows for record locking.

I'm going to start toying around with a custom version of a lock
file like Don described that would be read prior to a critical
event (like updating/writing to disk) and its records would be
scanned to see if connected users are doing anything that would
prevent the operation. The file could be composed of "X" number
of records with each record defining a connected user, and any
locks currently in place (or a value of zero if no locks). I can
then use different values for the type of lock. I would use
QUAD integers for this. For example, a value of 1000000050 could
mean the user is currently attempting write on record 50. Similar
logic could be put in place to handle read locks or if the user
wants exclusive access to the file (i.e. to perform a reindex).

When a database is closed the lock file would be checked to see
if there are now no remaining connected users. If there are not
any then the lock file would be deleted.

Just thinking out loud............

Thanks,




------------------
Paul Squires
www.PlanetSquires.com
mailto:[email protected]
[email protected]
</A>

[Paul Squires]

Don,

I just read your post again. How would you handle the scenario
myself and Timm just spoke about where user "A" updates a record
that user "B" is currently editing?????

Any thoughts about this concurrency issue.

Thanks,



------------------
Paul Squires
www.PlanetSquires.com
mailto:[email protected]
[email protected]
</A>

[Timm Motl]

Paul:

What I described above was Tsunami's internal method of handling concurrency conflicts. When I need a more robust
way to handle it, I do it programmatically with a "lock" file similar to what you are talking about. I've been
doing this with Btrieve files for years, since I'm reluctant to depend on record locking calls.

What I do in commercial apps is create a lock file that is always there, but empty when no locks exist. Any time
a user reads a record that requires exclusive rights, I insert an entry in the lock file that contains a file ID
(to identify which file the record belongs to), the record ID (usually the record's unique key), a station number
(if applicable) and a user log-in ID (if applicable). My application opens this lock file in single-user, exclusive
mode and attempts to "lock" a record by inserting an entry in the lock file. It must first see if the same record
is already in the lock file and return "locked status" back to the user if it is. By doing it that way, a user is
informed right up front if the record he/she is requesting is in use by another user at another station on the
network. My apps often allow a user to load a "locked" record into their editing screen for viewing, but restricts
editing, updating or deleting it.

This scenario requires a self-timer in the app to avoid "long lunch" locking problems. If a user "locks" a record
by loading it into his/her editing screen, the timer monitors inactivity and will "unlock" the record when a pre-set
time limit has passed, first giving an audible alert to the user with an on-screen 10 second countdown to avoid the
automatic unlock. Any other user who received a "locked status" on that record could also have a thread or timer
event invoked to test the lock file periodically, trying to secure a lock on the record and gain exclusive access.
That depends on the app.

I've done these things in literally dozens of commercial apps using Btrieve with great success and am now doing them
with Tsunami as well. It was very interesting to hear from many others via e-mail about how they use very similar
methods for "locking" records instead of using actual lock calls on the shared data file.

Timm

[Don Dickinson]

I usually create a "lock" field in the table structure to handle something like this. When the lock is placed, I write the user's initials and date/time to that field. If someone else requests the record, I ask them if they want to override the "lock" and let them in if they say "yes". If a user finishes with a record, I clear the lock field. If they crash out or something and leave the lock field in place, the next user can just over-ride it. The only time I have to employ this is the situation you describe - when it's possible for two people to edit the same record.

Best Regards,
Don

------------------
dickinson.basicguru.com

[Paul Squires]

Thanks Don,

I remember reading stuff about this in Ethan Winer's old BASIC
programming book. Basically, implementing a "soft lock" type of
system within the database tables themselves.

I think I may stick to an external lock file because it would
make it easier to remove all locks (simply delete the file) in
case a client program terminates abnormally and the lock information
is left written in tthe database record.

Thanks,



------------------
Paul Squires
www.PlanetSquires.com
mailto:[email protected]
[email protected]
</A>

[Fred Oxenby]

I use a field in my database-record called "LastUpdate" (double) and "LastUser" (String)
When creating a record "LastUpDate" is filled with currentTime as VariantTime
LastUpdate and LastUser is invisible to the user.
------
When its time to physically save=update the record I lock/read this field
and compare it to current value. If value is changed since the user got it
for editing, I return a message and give user a chance to read back
the changed record.


------------------
Fred
mailto:[email protected][email protected]</A>
http://www.oxenby.se

[Charles K. Kincaid]

The file locking provided by the LOCK stament does not realy lock a file.

What happens is that in the system file tables on the machine that has the file, an entry is placed for that lock. I has a pointer to the file, a start byte position, and a length count. What is prevented is that no one else, not even yourself ,can place another entry into the table that conflicts with or overlaps one that is already there.

Physical access to the file IS NOT prevented by file locking. This is very easy to test and so I won't bore your with the details.

Here is the way that we (at my old company) handled concurrency. User A reads a record. User B reads the same record. Both go into edit. User B writes changes. Thet user A writes changes.

The record is locked during the change write back. The sequence is to lock, re-read the record, update only the fields changed by the user, write back the record, unlock.

This takes work on the part of the program to keep track of what fields were changed by a user. The only downside is that user B (the first writer) does not see the changes made by user A until such time as user B has reason to redisplay the record. After each update, we would re-read the changed record and redisplay all the fields. Thus user A sees both the changes.

This was tested extensively. As a matter of fact, we had six people that spent over two weeks on this issue alone. We had nearly a thousand installations of our product with an average of 6 users per install. One of our installs had 20 users. Our commandment was "Thou shalt not lose the users data."

We dealt with medical (dental) records so data integrity was very important. We were able to call up the same patient by two users, one changed the address and one the phone number. Then: "Three, two, one, save." No data loss. Now if both changed the same phone number field, then the last one to save was the winner. Now we had data collision problems down to one specific field.


------------------
ATB

Charles Kincaid



[This message has been edited by Charles K. Kincaid (edited August 31, 2001).]