Ok, after having checked all my log files, here's the full detail concerning the INSERT INTO SQL which is generating "File is in use by another user." about twice a month.
At each occasion, you'll see that someone else was inserting at the same time.
Also, the code being executed was coming from the same application having 2 instances.
At 04/09/97 20:15:30, I have a record that a message was inserted in the table where the INSERT INTO SQL was successful. The user who inserted the record began the transaction at 20:15:30 which last for 17 seconds. Because it last so long, the 2nd instance of the application took over the next request at 20:15:36 which is the transaction that the 2nd user wanted to do the INSERT INTO SQL as well. That 2nd transaction only last for 6 seconds, which end at 20:15:42, resulting in the error message "File is in use by another user.".
At 05/09/97 11:29:33, I have a record that a message was inserted in the table where the INSERT INTO SQL was successful. The user who inserted the record began the transaction at 11:29:33 which last for 18 seconds. Because it last so long, the 2nd instance of the application took over the next request at 11:29:37 which is the transaction that the 2nd user wanted to do the INSERT INTO SQL as well. That 2nd transaction only last for 8 seconds, which end at 11:29:45, resulting in the error message "File is in use by another user.".
As you see, the same patern occurs when I get this error message.
However, after the transaction that is doing the INSERT INTO SQL, some other code are processed before the transaction end. I am doing some other INSERT INTO SQL into other tables where one value for the field is defined like THREAD.NUMERO. So, that line looks like this: INSERT INTO TABLE2 (somefield,...) VALUES (THREAD.somefield,...). So, another INSERT INTO SQL is using a direct call to THREAD which is the table where the main problem is.
I have also another SQL which is using a where clause which is making reference to THREAD. I think this is where the problem is.
So, having some other INSERT INTO SQL using a call the THREAD or having a SQL which is having a where clause with THREAD seems to avoid the 2nd application to be able to process the INSERT INTO SQL for THREAD.
As for the direct call to THREAD.NUMERO in the other INSERT INTO SQL, I will be able to store this in a variable at first and use that variable to optimize a little bit.
But, I think the problem is mostly related with the where clause involving a condition to THREAD. That SQL is having a GROUP BY clause which may explain why the transaction may take up to 17 or 18 seconds in some occasions. So, if this is where the problem is, this means that an application can't process a INSERT INTO SQL while another application is doing a SQL on a table which involve a where call to the table that we need to use in the INSERT INTO SQL. Is it the problem?
I have SET REPROCESS TO 2 SECONDS.
However, I can't find any reason that justify the 6 to 8 seconds time before the 2nd application end with an error. Does this mean that Visual FoxPro will only retry 3 or 4 times before returning the error? Having SET REPROCESS TO 2 SECONDS, when doing INSERT INTO SQL, if collision occurs, Visual FoxPro is suppose to retry at each 2 seconds until it succeed.
