>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.

Do you mean SELECT-SQL when you say SQL? A SELECT SQL doesn't lock the table/record (or, I don't see when and why would it lock the table and I never saw a lock from a SELECT SQL.)

>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.

No, VFP will try continously to lock for 2 seconds. If it cannot lock in 2 seconds, the operation fails. Although, I don't understand why you get a time longer than 2 seconds. Probably because of the network or multitasking. You may also try to see the statistics for your network retries. What kind of network do you have? Are these tables locally?

If you can be sure that ANY transaction will end in a finite time, you can use SET REPROCESS AUTOMATIC. But you said yesterday something about 2 minutes, which seems to be "for ever".

How do you generate the unique keys for THREAD? This can be another place where a shared table is locked. (You must lock the entire table/file if you use the technique of "last used key + 1".)

Anyway, this problem you have is one of the most interesting locking problems I heard!

Vlad