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

Not if you INDEX ON UPPER(Table) TAG Table. Works every time. IMO, Character fields should always be indexed on UPPER() to avoid these kinds of problems that you are describing. I can't tell you the exact number of people using my code, but it is quite a few and no one reports problems.