>>>Thanks Jim,
>>>The locking description is too vague, I think. It starts by saying "Perform an insert or append operation", but since 4 rows later it says "append operation completes", it should, at least say "BEGIN an insert...". It does not specify where the reading of the current value occurs, i.e. is it before or after the header lock. The first thing the insert process should do is to not allow any other insert to start, and the start is to me the read of the current autoinc value.
>>
>>And shouldn't it just error out if it can't lock the header? I mean, the scenario should be
>>
>>- lock the header or error out
>>- get the autoinc value
>>- insert the record
>>- update the header
>>- unlock the header
>>
>>This may or may not be wrapped in a transaction, which shouldn't matter at this level - transaction may be affected by how this went, but not vice versa.
>>
>>Now let's conduct a little mental experiment to see whether there may be other possible scenarios.
>
>That's the scenario I had in mind when talking to Al and saying that the network connection speed shouldn't matter. One way to get a duplicate in this scenario was:
>*User1
>- lock the header or error out
>- get the autoinc value - from disk, value 5
>- insert the record - cached
>- update the header - cached autoinc value 6
>- unlock the header
>*User2
>- lock the header or error out
>- get the autoinc value - get a duplicate 5 either from disk, or from the wrong cache, if 5 was also only in cache.
>- insert the record
>- update the header
>- unlock the header
>Of course I may be 100% wrong.

Doru,

AlD said he was talking strictly about HD (write-behind) cache.
In which case your scenario the cache used would have to be on the file server's HD only.
In theory what you describe, if limited to the file server's HD cache, cannot happen. Specifically the part about "get a duplicate 5 either from disk, or from the wrong cache, if 5 was also only in cache". If there is a cached record with a value of 6, the old record with 5 in it should be invalidated. This is because ALL gets/puts or reads/writes for the same record would all refer to the latest 'version' of the record.
If the latest was in cache, that's the record that is to be provided to any requester of the (same) record.
If the latest was both in cache and on the HD it doesn't matter where it comes from.
If the latest is only on the HD (the cache having been overwritten by some other record) then the requester is to be provided with the HD's record.

That's the theory. But I maintain that basic HD caching (at least on the cheaper drives) is unreliable. The errors that I have seen do not necessarily mean that wong records were provided, but rather that some synchronization problem (or other problem... who knows) happened and Windows decided to report it as a "Delayed write failure...".