>>>Hi,
>>>
>>>Codemine framework takes care of file and record locking so I am dusty on the subject and need help.
>>>
>>>A one record file holds several app variables including counters for lot number, transaction number, etc. The app normally uses table buffering across the board, including for that table. The routine that increases the counters has not been using file or record locking
>>>
>>>You can see what this leads to. Normally there have been only one or two users updating data, but now there are several larger sites and duplicate transaction and lot numbers have appeared.
>>>
>>>What is the best way to use locking to prevent duplicate generated numbers?
>>>
>>>Another question in the same case. As a precaution the routine that increases the counters doublechecks that the generated number is not already present in any of the tables that store it. This raises the question of how to know the value currently in the drive when a table is buffered. One way is using SQL SELECT without BUFFERING = .T. Is there another way?
>>>
>>>Thank you very much,
>>>
>>>Alex
>>
>>Alex,
>>I think there is a sample called nextid?
>>
>>The process is simple (and a more simplified version might look like this):
>>
>>

set reprocess to 1
>>declare void Sleep in win32API integer dwMillis
>>use theTable
>>* Try getting lock
>>do while !rlock() && if failed
>>   Sleep(10)   && delay a bit before retrying
>>enddo
>>replace lotNumber with lotNumber+1
>>local lnNewLot
>>lnNewLot = lotNumber
>>use
>>return m.lnNewLot

>>
>>Other part I think is impossible to check when there are multiple users + buffering. You would try inserting with a primary or candidate index and if you get error, increase, retry.
>>Cetin
>
>Thank you for the help Cetin. I've been digesting your response and figuring out the best way to integrate your advice into our situation.
>
>Please clarify your response to my second question, namely how to know the value currently in the drive when a table is buffered and there are multiple users. I want to make sure I get your answer right. Did you mean that it is altogether impossible to know the value currently on the drive or only that it is imposible by any method other than an SQL SELECT without BUFFERING = .T.? I think you meant the second case.
>
>Thanks a lot.
>
>Alex

When the table is buffered and there are multiple users, your only chance is to get on disk value. You cannot access to other users' buffer. It boils down to:

-Try locking "enumerator" record.
-Lock, increment, get the new value (value is allocated to caller at this point), unlock.
-Use the new value and commit or discard (in case it is discarded that value is a 'gap')

Second strategy (same as autoinc or SQL server identity):
-Do not lock anything. You don't know the value initially.
-Insert (all fields except the 'enumerator' -maybe key- field) and commit (written to disk)
-Get back 'server' assigned value

Third strategy:
(If that number is not an "enumerator" but a key to uniquely identify rows in a table - aka primary key)
-Do not lock anything
-Create a value that is unique even if you are not connected to database (aka GUID) using winAPI. ie:

Function UniqueID
Local pGUID,rGUID
Declare Integer UuidCreate In 'RPCRT4.dll' String @pguid
Declare Integer StringFromGUID2 In 'Ole32.dll' ;
	string rguid, String @lpsz, Integer cchMax

pGUID=Replicate(Chr(0),16)
rGUID=Replicate(Chr(0),80)

UuidCreate(@m.pGUID)
StringFromGUID2(m.pGUID,@m.rGUID,40)
RETURN CHRTRAN(Strconv(Left(m.rGUID,76),6),'{}','')
endfunc

-Insert

If you were asking that for primary key 3rd is my preference. No lock, no need to check others' keys.
Cetin