Do Multi-core processors cause "Record in Use" errors in Visual FoxPro?

smithwahl used Ask the Experts™
We have an 11 year old Visual FoxPro application that this company developed.  I started working on this 2 years ago and I am noticing a lot of the same consistent errors at our customer sites.  The application is a single thread single tear application that accesses a local Visual FoxPro database.  The users access the application by logging into a server via Terminal Server.  So the standard setup is one BIG server with the app and DB local with multiple terminal server sessions accessing the app.  In the last two years my team has addressed a lot of the big errors but we are baffled by the “record in use” error.  Our larger clients (20 to 60 users) appear to encounter it much more than the smaller clients.  When they encounter the error, we reindex tables in the database and it makes the error go away.  However, within weeks the errors come back.  We then reindex again and it works.  I know, you’re asking yourself why we don’t have them reindex every night.  Well we can but that does not solve the problem it just hides it.  I would prefer to fix the problem.

I hear from others in the company that it appears that we have been having more of these error in the past 4 years than ever before.  The main difference that we have is really the hardware.  Now our customers have much bigger, faster servers.  I believe that the main culprit is the speed of the processors and the multi-core hardware.  I have found documents about Visual FoxPro and multithreading that lead me to this suspicion.  According to Microsoft docs, every time a “select” statement is made, the OS runs that in another thread if available.  Without coding for that, it appears to me that this could cause errors like “record in use” and “sequence out of sync”.

If there is anyone out there that knows VFP, can you shed some light on this for me?  Am I on the right track or out to left field?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
CaptainCyrilFounder, Software Engineer, Data Scientist

This error happens when you are trying to open a file when it is in use.

Are you sure you are opening the files in shared mode?



Yes, we do use SET EXCLUSIVE OFF
"Record in use" can be caused by delays in data processing. One app instance is writing data (and updating indexes) and appropriate records are locked whereas another instance is waiting for record lock and if this waiting period exceeds timeouts set by SET REPROCESS and other commands then above error appears. Large and unbalanced indexes slow down the data access for sure.

You should investigate why delays occur in your app. Maybe index files are too large and their update is time consuming, maybe antivirus software is blocking file access, maybe ...

Higher number of concurrent users must have bad influence on this error. You should record some statistics which will say where to begin. Does the error occur on all DBF files or on just some of them? Does the DBF and index size affects error occurence? How often are you cleaning (PACKing) your data?

How often the  “sequence out of sync” appears? This error means corrupted index file. It can happen when the application is forced to disconnect from its data. Do you always close your app standard way or could it happen users just switch computer off? You should record all logins and logouts and if you don't find apropriate pairs then you have to force REINDEX operation.

Hardware speed should not have bad influence. Number of threads used for SQL commands is irrelevant. If you are using terminal services then SQL commands from different clients are probably executed on different CPU kernels but the bottle neck is physical data access on disk which depends on file system and its speed optimization.

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Are you using table buffering. That would decrease the record locking time. What is the value of SET REPROCESS. Maybe you should increase it.
CaptainCyrilFounder, Software Engineer, Data Scientist

It also happened with a client of mine who just disabled scan on DBF, FPT and CDX files or the whole Fox Data folder and it worked like a charm.
Olaf DoschkeSoftware Developer
Multicor processors would not be the reason. A VFP EXE will have some threads, but data access within the same VFP EXE will not be done using more than 1 core, if you don't explicitly use multithreading eg via and then do process the same DBF within several threads.

Even if, the "Record in Use" basically is hinting a file locking failure of the process wanting to write to a record and that typically would be from some other user, which is not only within another thread or process, but even from another computer. So that is not a multicore or threading problem.

You are aware, that SET EXCLUSIVE is among the settings session specific, and you need to redo SET EXCLUSIVE OFF everytime you start a new data session, eg by a form set to private data session or by a Session class?

As you experience, the problems rise with larger data and more users. It's more of a structural problem, than configuration, if it's as consistent, as you observe, then probably you haven't cared much about automatic or manual locking and conflict handling of failed tableupdates. Eg TABLEUPDATE() does report update conflicts. Tushar already mentioned REPROCESS.

The basic problem is, that only one user can have the write access to a DBF file at a time, so keep automatic or manual locks as short as possible, that's the one thing you can do, the other is to increase timout for a lock to inifinite. After all the write access must be serialised somehow, and there is no server side component, which does that, so the way is to lock the file.

On the file system and network protocol side, SMB2 and especially opportunistic file locking is known to be problematic with any file based database, be it VFP DBFs or Access MDBs.

Bye, Olaf.


The question was answered but no good reason was given to counter the conclusion that the evidence suggest.  I am satisfied with the answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial