Vlad,

You seem to be itching for a response form me on this one, so here goes. . .

You write:
>This is Michel's case, so there's no deadly embrace here. How can you have a deadly embrace when all it's about the table that keeps the IDs and the table in which the insert is done? I just don't see it.

But I had written:
>. . . then *IF* all updates occur in the SAME SEQUENCE (as per your 'theory' above), then there is also no possibility of a deadly embrace.

In other words, REPEATING what I had written (but it appears you did not care to read: "then there is no possibility of a deadly embrace". I don't know what you don't see, but I do know you will not see a deadly embrace because that is also what I said!

You write:
>This is not at all a VERY LIKELY SCENARIO in any app. At least, not if the app is well designed and developed.

My comment:
You must write "interesting" apps if this is not a very likely scenario in your designs! Let us take, for example, a simple case of a "customer master" table (and let us not worry too much about the "design" of the table in this case):
I (but seemilgly not you) would have a routine which CREATES a new Customer Master record. It might go to some table to get the next (key) Customer number to be assigned, update that record to indicate the number has been taken, and update that file. Then I would write the actual Customer Master record with the key which I had gotten from the 'next key' table.
BUT there would sometimes be a need to REVISE data in a Customer Master record - say the customer's address changes, or his/her phone number or some tally of "service interactions" needs to be updated. SUCH THINGS would be done in a separate routine, possibly even (probably) routineS, which would UPDATE the Customer Master record and, probably, some other record somewhere (perhaps a "log" or some "history" or some other such thing.

SO, *I* content that it IS COMMON and LIKELY that the same file will be updated from several different routines within an app, probably in combination with other different files.

I must admit, though, that I would like to see YOUR design for such scenarios - I am always anxious to learn new and better techniques!

I *will* revise my remark. . .
>>So, the only "safe" way to code things is, when possible, to lock/update/unlock a single table at a time.

to say, instead: The SAFEST way to code things is, when possible, to lock/update/unlock a single table at a time.

I await my esteemed colleague's comments

Jim N


>>Mark,
>>
>>>
>>>i disagree. if table.dbf remains locked durint the insert-sql then no other user can lock table.dbf and therefore can not procede on to the insert-sql step. to me this is better solution -- do not unlock table.dbf until after insert-sql. this will assure only 1 insert-sql can occur at any one time and response times should be acceptable.
>>
>>The above is a very good theory, but in my humble opinion is begging for a deadly embrace situation *somewhere*.
>>Here's how my theory goes. . .
>>You cannot have a deadly embrace when you are updating only ONE table.
>>Since it takes updating at least two tables (update required because that's where locks come in) to get a deadly embrace, then *IF* all updates occur in the SAME SEQUENCE (as per your 'theory' above), then there is also no possibility of a deadly embrace.
>
>This is Michel's case, so there's no deadly embrace here. How can you have a deadly embrace when all it's about the table that keeps the IDs and the table in which the insert is done? I just don't see it.
>
>>However, all that is needed to change that situation for *ALL* participants is one rogue application where the sequence is not maintained. Let's face it, THIS IS A VERY LIKELY SCENARIO in any application set (it will likely not be the case that an app always updates a specific set of tables in a specific sequence.
>
>This is not at all a VERY LIKELY SCENARIO in any app. At least, not if the app is well designed and developed.
>
>>So, the only "safe" way to code things is, when possible, to lock/update/unlock a single table at a time.
>
>This is good if it's possible. But is NOT AT ALL THE ONLY SAFE WAY.
>
>Vlad