VFP - File is in use by another user

The default value for SET REPROCESS is 5 locking attempts in VFP OLE DB provider. And yes, it causes above exception sometimes. Obvious work around is:
1) set reprocess to longer interval as Olaf suggested
2) catch the exception and retry the insert attempt
3) insert records in a stored procedure which can handle manual locking and returns success/failed flag
4) insert records from each connected user into a separate table and create standalone process which inserts records from several standalone tables into the "main" table

If you are inserting records to one table only then transactional processing does not help.

oalf and pceiba, thanks a lot for your suggestions.

I am not a FoxPro wizzard myself but have been researching this issu and, during my research, I found out about the IsolationLevel.ReadCommitted option (something like my code below).

Would something like this work?

 Dim command As New OleDbCommand(sql, con)
        command.CommandType = CommandType.Text
        command.CommandTimeout = 0
        con.Open()
        Dim trans As OleDbTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted)
        command.Transaction = trans
        command.ExecuteNonQuery()
        con.Close()
        command.Dispose()

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

No, above code does not work in VFP OLE DB provider.

But, maybe, I am not correct. It should depend on available connection properties. I am testing it on ADODB.Connection object which could have different properties than your OleDbConnection...

More important for transactional support in VFP is a database container. If the table has no associated database container than it does not support transactions. The ReadCommited value is a default for VFP connection, so no need to set it again.

And, if you are inserting record to one table only then transaction is not necessary.

Trying righ now.

Thanks, Pcelba!

I see, if 5 retries is the OLE DB Default, with a large number of concurrent users and even only mild query times this can easily cause 5 retries not to work and therefore the file is in use error 103.

I wonder if 5 seconds will be a better value, but in fact in a webapp environment surely a timeout is good anyway. You don't want a webserver to get stuck.

I haven't found where the default is described, and I haven't setup an oledb connection just to try. But I found this, which might also help. In fact you can already set REPROCESS=X in the connection string. This seems to be limited to X retries, but you could for example set it to REPROCESS=200...

http://www.vfug.org/Newsletters/OLEDBProviderWaysToChangeSettings.htm

Bye, Olaf.

So, I tried the code below and I am not getting any error so far.

I will monitor the app tonight and tomorrow morning, with heavy usage, and hopefully this will fix it.

In the mean time, I will appreciate if you correct me if I am wrong on the way to use the code.

Thank you very very much...

  Dim con As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("FoxPro_DB").ConnectionString)
        con.Open()
        Dim ReprocessCommand As New OleDbCommand("EXECSCRIPT('SET REPROCESS TO 5 SECONDS')", con)
        ReprocessCommand.CommandType = CommandType.Text
        ReprocessCommand.CommandTimeout = 0
        ReprocessCommand.ExecuteNonQuery()
        ReprocessCommand.Dispose()
        Dim command As New OleDbCommand(qry, con)
        command.CommandType = CommandType.Text
        command.CommandTimeout = 0
        command.ExecuteNonQuery()
        con.Close()
        command.Dispose()

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Looks okay, I only see one overhead: You can also send the command "SET REPROCESS TO 5 SECONDS" without the Execscript. An Execscript would only be useful for sending a script, at least two commands, but a single command doesn't need to be wrapped in an EXECSCRIPT.

Bye, Olaf.

Thanks for the correction Olaf.

I agree the simplest and fastest way is to add REPROCESS=50 into connection string. The optimal reprocess value should be tested, of course. We know 5 is too small, 200 is most probably too large because it would cause long delays in case of problems.

You're right. This also depends on the time VFP waits before retrying, which can be set via SYS(3051, nMilliseconds) The default is 333, so 50 would already be 16,666 seconds timeout before error 103. 200 retries will be 66,666 seconds. The minimum you can set is 100 ms, then 50 retries would be 5 sec and 200 retries 20 seconds maximum retry time.

Bye, Olaf.

Applying that knowledge to the default of 5 retries, this means some SQL's or other commands you execute must take over 2,666 seconds. Well, roughly said. In detail this timeout without achieving a timelock could also be caused be two or more other users successfully attempting locks at the right time. Unfortunately requests are not queued or prioritized.

But it's only inserts and updates locking the header, selects just read. So one recipe for optimising parallel access is to limit inserts and updates to single records each. Eg no insert into..from select and no update on all records.

Also in general the right indexing is needed to optimize overall performance and lower issues of concurrent file access. Just remember the low level problem: A file can't be written to shared, so all write operations first need a lock on the file, which they will make as short as possible, but that can also be macroscopic time intervals. If you avoid that things will be better.

Bye, Olaf.

The lock attempt interval is set to 333 ms and cannot be changed in VFPOLEDB. It corresponds to 1.667 seconds for one insert and 5 retries. Such interval seems to be long enough but just 5 heavily inserting concurrent users caused the "File is in use by another user" error during my tests... Index and memo fields on such table can even cause more problems.

Increasing number of connections affects the performance with almost linear dependency but the occurence of unsuccessful lock attempt is not linear at all. So, to increase REPROCESS value can help up to certain number of concurrent users and application must be redesigned after the critical level trespassing...

...Cannot be changed in VFPOLEDB...

Really ?  SYS() is a supported function, so you say you can't call a SYS(3051,100) via above script?

What certainly is true is, that after a certain critical level extending the reprocess time will not have a good try/success ratio anymore and the webserver may be polluted by file lock attempts. You need to foresee a program logic handling fails anyway.

Bye, Olaf.

I can call SYS(3051,100) but with no effect... :-)
And SYS(3051) returns empty string.