>>In your first response you said, "Yes. There is nothing to prevent two people from getting the same key value." Do you have any support for that claim?
>>
>>>You didn't get it. The problem is not with the UPDATE, but the original INSERT. There is nothing to stop two people from inserting the same record.
>>
>>Yes there is: it would cause a primary key violation of the KeyCtr table.
>
>Carl,
>
>At this point all I'm going to say is use it if you want. I would not rely on it to work. My method has been tried and tested by people all over and works perfectly.

Craig,

I would have dropped this, but I think you will want to know that there is a problem with your code. I am using the version found at http://members.home.net/foxpro/kb/kb0006.html

SEEK UPPER(tcTable)
IF FOUND()
liRetVal = NextId
REPLACE NextId WITH NextId + 1
ELSE
INSERT INTO Keys (Table, NextId) VALUES (tcTable, 2)
liRetVal = 1
ENDIF

You seek upper(), but Insert the base value. If you pass in a mixed case table name, "For example, GetKey("Client")", It always will fail the seek, and try and insert it again, which violates the primary key of Keys.

You may also want to know the results of some time tests. The code below found that in a low traffic environment (no contention) mine was at least 5 times as fast, and in a high traffic it was still over 10% faster. I am surprised with the second result, I figured that locking would meter the allocations and smooth out the contentions. Let me know if you can create an environment where yours is faster, I am interested in what I missed.

set func 10 to "canc;clear prog;do "+sys(16)+";"
close database all

* set coverage to ("mkkey"+substr( sys(0), 1, 2)+".log" )
set talk off

if !file( "test.dbc" )

create database test

create table KeyCtr ;
( kKeyCtr c(30) primary key, ;
nCtr i )
create table test1 ;
( kTest i primary key default MkKey( "test.kTest" ) )

create table KEYS ;
( Table c(20) primary key, ;
NextID i )
create table test2 ;
( kTest i primary key default GetKey( "test.kTest" ) )

close database

endif

?
? datetime()

use test!test1 shared
* Wait for file 'start' to get created (echo>start1)
? "waiting for start1"
do while !file('start1')
enddo
ltSrt = datetime()
for lnI = 1 to 1000
append blank
endfor
? datetime() - ltSrt

use test!test2 shared
* Wait for file 'start' to get created (echo>start2)
? "waiting for start2"
do while !file('start2')
enddo
ltSrt = datetime()
for lnI = 1 to 1000
append blank
endfor
? datetime() - ltSrt

close databases all
set coverage to
return

function MkKey( tcFld )

local ;
llDone

llDone = .f.
do while !llDone

* Get the current value of the Counter for this key
select nCtr ;
from KeyCtr ;
where kKeyCtr = tcFld ;
into array laCtr

if _tally = 0
* If we didn't get one, there is no counter yet.
* So make one and try again
debugout "Added Key Counter: " + tcFld
insert into KeyCtr ( kKeyCtr, nCtr ) values ( tcFld, 0 )
loop
endif

* Bump the counter.
* The counter holds the next key value, we use the original value
* The kKeyCtr identifies which counter,
* the nCtr make sure noone else has bumped it allready
update KeyCtr ;
set nCtr = laCtr[1] + 1 ;
where kKeyCtr = tcFld and nCtr = laCtr[1]

if _tally = 0
* The update failed.
* Probably because someone other process has grabbed the same key,
* and bumped the counter first.
* So we will try again.
* debugout "Counter assignment contention at " + transform( laCtr[1] )
* Close the KeyCtr table to assure a fresh version of the key counter data.
* Otherwise, we get the same key from the buffer, not a new one from disk.
use in KeyCtr
loop
endif

* Once we get to here, we have a good new key.
llDone = .t.

enddo

return laCtr[1]


FUNCTION GetKey
LPARAMETERS tcTable

LOCAL lnArea, llOpened, liRetVal

* Save current work area
lnArea = SELECT()
llOpened = .F.

IF !USED("Keys")
USE Keys IN 0 SHARED
llOpened = .T.
ENDIF
SELECT Keys
SET ORDER TO TAG Table

* FLOCK seems to work a bit better than RLOCK.
* I've never had a problem with it.
DO WHILE !FLOCK()
ENDDO

SEEK tcTable
IF FOUND()
liRetVal = NextId
REPLACE NextId WITH NextId + 1
ELSE
INSERT INTO Keys (Table, NextId) VALUES (tcTable, 2)
liRetVal = 1
ENDIF

FLUSH
UNLOCK

IF llOpened
USE IN Keys
ENDIF

SELECT (lnArea)
RETURN liRetVal