File is in Use by Another User Error

[ejhemler]

I have created some software where multiple users need to access the data at the same time and make changes to the same table. I have all the files open shared so that multiple people can use see the data at the same time, but if someone tries to write to the file by modifying a record or adding a new record the the file, the error File is in Use by Another User will come up every once in a while. It does not happen always. But I thought that if the files were open shared, that this was not supposed to happen.

I am using VFP 6.0 and the data and software is on a windows 2000 server environment. It seems like this happens when there are a lot of people on it and making changes at the same time. Am I missing something here? Please help.

Thanks,
Erik

 

[audiopro]

Could it be that two users are accessing the same record at the same time. Two users will be able to read the same record but the second user to access it will be prevented from writing to it as it will be locked by the first user.

Hope that makes sense.

Keith

http://www.studiosoft.co.uk

 

[ejhemler]

And if it's not that error, it will be File Access is Denied.

 

[ejhemler]

Yes, I thought about that, but wouldn't that give the error Record is in use by Another User?

 

[ejhemler]

Plus it wouldn't make sense that people are in the same records because all the users are limited to accessing only the records they are responsible for and should not be in the records they are not responsible for. I hope this is making sense. If not let me know and I will try to explain better.

Erik

 

[Mike Lewis]

Erik,

Is it possible that one user is trying to write to the file while another is trying to append a new record to the same file? The append operation locks the file's header, which might give rise to the error you mentioned.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[audiopro]

Some VFP commands lock a record and some lock the whole table Have you been able to track down where the actual 'clash' is?
You can be sure the app will run without a glitch if you are stood next to it.


Keith

http://www.studiosoft.co.uk

 

[ejhemler]

Wow, I didn't realize the append command would lock the file's header. Is there a way to do it so that append does not lock the header file? If not, maybe I will have to look into using the INSERT command, but that will be a pain to rewrite. Oh well.

 

[audiopro]

Maybe one of the gurus can explain the correct use of RETRY. I have never had to use it and am always wary that this type of command can lock up the app. in some circumstances.

Keith

http://www.studiosoft.co.uk

 

[ejhemler]

I've actually set up an event handler that will let the user execute a "Retry" command on there own so that if they get this message, they can hit Retry until it goes through. But I want this to only be temporary until I figure this out.

Erik

 

[ramani]

Hi Erik

The problem could be the way you are making additions to records.

If the table has PRIMARY index and you use APPEND BLANK command, this can create a conflict when more than one person simultaneously try to add a record. Because, two blank records cannot exist with a primary index. The resolution for this is use the command.. INSERT with a key value for primary key field. This will not create a conflict.

Another is the buffer method or locking method you use. Read on SET MULTILOCKS and SET REPROCESS commands.

I am sure you can fix that.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[ejhemler]

I think I will just clean up the code with replacing the append blank command's with INSERT command's. I never liked that command in the first place and could never understand why they wrote it like that. Well, thanks for helping me get to the bottom of this everyone.

 

[Mike Lewis]

Erik,

In general terms, the usual approach to locking problems is to use buffering. You could choose to adopt buffering with pessimistic locking, in which case locking conflicts shouldn't cause a problem. Read up on buffering in the Help.

Having set the buffering, you could SET REPROCESS to, say, two seconds.That way, if two people hit the table at almost exactly the same time, VFP will simply wait until the first one finishes before letting the second one proceed.

You should also write an error handler that traps for errors 108 and 109. These errors will arise if, despite the delay initiated by SET REPROCESS, one user tries to edit the record or table while another user has it locked. Your error routine should advise the second user of the situation, and suggest that they try again later (obviously your user interface will have to handle this).

The other approach is to use buffering with optimistic locking. That means that you will never lock anyone out just because someone else is editing the record, and you should never get errors 108 or 109. But you run the risk of the second user overwriting the first user's edits, so your code will have to test for this and take some appropriate action.

All this is explained in the Help, under the heading "programming for multi-user access" (or something similar).

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com