File is in user by another user while opening a database

[KALP1]

When my 2 users simultaneously use 1 option which issues Open Database xyz.dbc , then 1 user gets error "file is in use by another user".
What might be causing this error as I am not any dbf file of that database exclusive. Set Exclusive is also OFF.

 

[Olaf Doschke]

Have you implemented an error handler telling you the exact line of error?
Exclusive is a per datasession setting, which is ON by default for the default datasession. So are you sure you set it OFF?

Bye, Olaf.

 

[KALP1]

Yes, with error handler only I came to know point of error. I initially thought append blank was causing file is in use error as 300-400 records were appended in series . But that wasn't true. Error came in a procedure at line no. where I open all necessary databases. I am using only 1 default data session and set exclusive is off.

 

[Dan Freeman]

SET TABLEVALIDATE has been known to cause issues like this.

 

[Olaf Doschke]

>Error came in a procedure at line no. where I open all necessary databases.
One line doesn't open several databases. So please be exact here.

Assumed it really is that line causing the "file is in use by another user" (Error 108), OPEN DATABASE accesses a DBC file, a DBC also only is a DBF. Concurrent use of a file only is a problem at writing or with exclusive access. If I take Dans comment and assume you explicitly SET EXCLUSIVE OFF, the validation of the DBC file would cause this in the end.

Let's make a simple experiment, two session of VFP IDEs doing the following:

SET EXCLUSIVE OFF
SET TABLEVALIDATE TO 3
Do While .T.
   OPEN DATABASE xyz.dbc
ENDDO

Nothing happens, I would expect any VFP IDE to error, but nothing happens.

Do you have database events? Specifically of course related to the open event? I still must strongly assume the error is coming from something else, but you might determine your REPROCESS and TABLEVALIDATE setting and let us know, so we can do further experiments.

Bye, Olaf.

 

[KALP1]

I have
open database abc1.dbc
open database abc2.dbc
open database abc3.dbc ....

I am getting this error at line open database abc2.dbc i.e always at opening 2nd database.

I have following settings :
SET PROCESS TO 5
SET EXCLUSIVE OFF
SET TABLEVALIDATE TO 3

1 thing to note is that my this common procedure is called from so many programs, but error always generates when I run specific program.
I don't have database events for Database.
I point is worth noting is that I am using Parallelfox in this program. Is it causing problem

 

[Olaf Doschke]

Parallelfox means you run this in a separate process. In itself that shouldn't be a problem or change anything in regard of VFP error exceptions being raised.

I still can't produce an error opening two databases. If it's always the same DBC, you should perhaps simply check its integrity, perhaps replace it. VALIDATE DATABASE would be a test, though it may not find anything.

Bye, Olaf.

 

[KALP1]

I have commented out Parallel Fox then too error is occuring. So it is not that issue. In IDE, I don't find error. It occurs in EXE.
Secondly, Validation is not an issue as errors occurs for all my clients( each have their diff. databases) who run that specific programs.

 

[Olaf Doschke]

You could have a systematic error, a dbc already corrupt before you install it, I wouldn't put that aside.

So, you say this only occurs with the runtime in an EXE, but no, there is no such thing. Unless you provide the wrong runtime I can't imagine this happening. The only thing I had difficulties with is queries with sqlbuffering clause and the wrong SP runtime.

A test program like above now runs in 4 processes with each 25% CPU load and doesn't error. I don't see how that would fail only at runtime.

Bye, Olaf.

 

[KALP1]

I am now getting error in IDE also so it not issue in runtime only as I previously thought. Also now problem is not specific to 1 or 2 problems. It is now coming randomly from any of program calling this common procedure and also its frequency of occuring is increased. Please suggest how to trace root cause. How should I proceed further.

 

[Olaf Doschke]

Do you do errorhandlong/logging? In general? If not, establish this.
As this common procedurte is of major interest, a) compile with debug info and b) log ASTACKINFO at the start of this procedure, also AUSED().

>now problem is not specific to 1 or 2 problems
What do you meqan with this?

Bye, Olaf.

 

[KALP1]

Yes, I use error handler and I have logged ASTACKINFO. With its help only I came to knew that error was occuring in my procedure Assign.prg in which I assign variables and open required databases(here error occurs). Which info to see in Aused as I can suspend program and check that.

>now problem is not specific to 1 or 2 programs
I mean programs making use of parallelfox are not alone creating the problems. Other programs calling-> Assign.prg are also creating problems

 

[Olaf Doschke]

If you log erro number, is it really error 108 happening here?

If you log ASTACKINFO, can you see a pattern of callees? Does the error happen in assign.prg no matter where it is called from or does it happen more often when coming a certain path, from certain other classes/methods? This is what you should check with your ASTACKINFO, similarly you should check whether this always is occuring when certain tables are open. You have to find out what conditions make the error happen.

Besides, it would really help to reveal your assign.prg code, not only the error line. Also: Are you aware of the difference of LINENO() vs LINENO(1)? Are you really looking at the right line of code in assign.prg?

Bye, Olaf.

 

[Olaf Doschke]

One thing to notice is still the "File is in use by another user" just happens when a lock (mostly automatic) is needed to write to a file. OPEN DATABASE might just be very indirectly causing that, because it might cause closing of the database and reopening, which in turn causes closing of those databases DBFs open, which in turn causes buffered changes to save, which in turn causes automatic locks, even in other tables due to triggers. Edit: I tested with the CloseData event and opening an already open DBC does not cause VFP to go through closing and reopening it, as USE of a DBF already open in a workarea does. Still some side effect of opening a database might happen and cause that error. Something that does not involve another line of code failing, like closing/opening a table can do, because of the automatic buffer handling triggerred by this action. I often enough had just the APPEND of a record or the SKIP of a row cause an effect because only row buffering was used. Such things cause errors happening at no concrete line of code, as skipping a row is also caused by clicking in a grid, for example. The culprit then mostly was a wrong constellation in the row, which was left, not in the new/current row. Those are hard to spot errors. Another reason to only use table buffering mode.

Also, if opportunistic locking is involved you can have defects in DBFs and/or DBCs, also for many customers. Notice opportunistic locking is not meaning optimistic locking/buffering of VFP, but a file/smb protocol locking/caching mechanism of the OS/file system. It's known to mainly cause problems with file corruptions, more often with indexes than anything else. The first thing out of order from a DBC therefore would be it's dcx file. Anyway, only when you write to the dbc itself, which only happens with creation of tables and views, or alterations of database objects and other operations on the mere structure of database object, once a dbc is build up it typically is static, unless you do such things as creating tables and views on the fly, freeing or adding tables, etc.

Bye, Olaf.

 

[KALP1]

Olaf, I am not using any type of buffering - pessimistic or optimistic. You mean to say opportunistic locking that is happening at OS level might cause error. Secondly, my database is of static nature as you already said and I am not adding/freeing tables on the fly which might cause writing in .dbc file.
Attaching assign.prg - error occurs at line no. 215 and 217

 

[KALP1]

Analysing ASTACKINFO contents, 1 scenario I found in common where this problem occurs. Normally a user works in 1 unit for which assign is called only once. When program to consolidate units is run which scans list of units (each may share same or diff. database depending on the architecture) say 100 nos. causes this error. Each scan in turn calls assign.prg which opens databases . Since I am scanning records time lag b/w 2 calls to assign.prg is in microsecs.

Is there something which could be concluded from above.

 

[Olaf Doschke]

OS opportunistic locks and buffering have nothing to do with each other, aside of problems occuring when writing to DBFs/CDXes and similar files, opportunisitc locking on the OS level can affect any file, but VFP of course mainly acts on table files. I mentioned buffers just because they are automatically processed in the moment a dbf is closed, so the error can be caused without any code explicitly doing something, the opening of a database could cause many things which in turn cause the error.

Each scan in turn calls assign.prg which opens databasea

Well, my tight WHILE loop does nothing else but repeatedly open databases without any error.

When you identified these lines as causing the error, would you care and put them in TRY..CATCH blocks? It could help pinning it down further.

Previous lines are calling your routine COPY_DATABASE, is any of the databases you open just recently copied to it's path? Then you may simply have a timing problem with continuing before copies are really made.
I wouldn't like to maintain such code, you have a very messy codebase here on first glimpse.

Bye, Olaf.

 

[KALP1]

What to write in CATCH block.When I suspend when error occurs and again resume program, it continues with the rest lines without error . COPY_DATABASE Code runs once in a while when data path of some unit changes so it is not executed when error occurs.
Any tips for sweet code.

 

[Olaf Doschke]

Here we go, this errors quite often and reproducible:

ON SHUTDOWN QUIT
IF ADIR(laDummy,ADDBS(GETENV("TEMP"))+"errors.log")=0
   STRTOFILE("",ADDBS(GETENV("TEMP"))+"errors.log",0)
ENDIF
ON ERROR errhandling(ERROR(), MESSAGE(), PROGRAM(), LINENO(), LINENO(2))
SET REPROCESS TO 5
SET EXCLUSIVE OFF
SET TABLEVALIDATE TO 3
testloop()

PROCEDURE testloop()
   Do While .T.
   OPEN DATABASE (ADDBS(_samples)+"Northwind\northwind.dbc") 
   OPEN DATABASE (ADDBS(_samples)+"Tastrade\Data\tastrade.dbc")
   CLOSE DATABASES ALL && this is essential to let the error occur!
ENDDO
 
PROCEDURE errhandling(tnErr, tcErrMsg, tcPrg, tnLineNo, tnLineno2)
   STRTOFILE(;
   TEXTMERGE("<<tnErr>>: <<tcErrMsg>> in line <<tnLineNo>>(<<tnLineno2>>) of <<tcPrg>>")+;
   CHR(13)+CHR(10),ADDBS(GETENV("TEMP"))+"errors.log",1)

So it's not the opening of databases over and over again, which causes that error, it's opening a recently closed dbc.

It does NOT happen, if you only run this once in the IDE or as EXE, so it's essential opening/closing is done from several processes.

What could be done? Retry open database in a loop, test other tablevalidate settings.

Bye, Olaf.

 

[Olaf Doschke]

I now checked the same code with these settings:

1. as above
SET REPROCESS TO 5
SET TABLEVALIDATE TO 3

This errors often

2. with other tablevalidate setting
SET REPROCESS TO 5
SET TABLEVALIDATE TO 14

This doesn't error!

3. with infinite reprocess setting:
SET REPROCESS TO 0
SET TABLEVALIDATE TO 3

This again errors often, so infinite reprocessing doesn't stop error 108 from happening. Actually this is what the help topic about REPROCESS tells about error 108, once error handling is in place error 108 does cause error handling and not reprocessing.

3. infinite reprocessing but not open validation tests and error suppressing.
SET REPROCESS TO 0
SET TABLEVALIDATE TO 14

This again does not error. It makes clear, the TABLEVALIDATE setting is the important one.

Tablevalidate 14 setting is having bit #2 set, which suppresses error 3 (which is closely related to error 108, maybe the help even is inaccurate about it and means 108 and 3) also it has not set bit #0, which means no validation of the reccount of the DBC file when opening it. TABLEVALIDATE 14 is good setting in having bit #2 set and bit #0 unset, other bits are depending on your taste and paranoia about reccount header errors. Bit #1 and #3 seem to do the same thing - checking reccount at the moment of writing - I remember wOOdy saying VFP8 only had one of these bits and VFP9 made things better, so I assume bit #3 is more important, most important should be to not test with each opening of tables and suppressing file is in use errors. I assume 14 and 12 are good values for TABLEVALIDATE.

Finally, Dan has hit the nail on suspecting table validation to be the root problem and tablevalidation also occurs when opening a DBC, as the DBC itself is a free table.

I also looked up, what wOOdy recommends instead of the default 3 setting. He prefers SET TABLEVALIDATE TO 11, which means doing all validation options and NOT suppressing errors. But in your situation it's clearly good to have bit #2 set. This would mean setting all bits could be OK, too, and indeed a test with setting 15 also doesn't error. Finally testing with 11 errors pop up again. So the most important bit for your case seems to be the suppression of error 3, it also works for error 108.

Bye, Olaf.