>I have a VFP8/SQL-server application, with new PK values allocated via. a 'control table' on the server which stores the last-allocated PK values for all tables.
>
>Occassionally I am getting duplicate values for a PK, and I cannot work-out why. To exaggerate the problem, I wrote a test program to create 1000 new pk values for one table, and ran two tests simultaneously. To my surprise around 7-10% of the new values were duplicated! Maybe I've been lucky so far because duplications have been rare (and most of the input is manual, not in a tight loop), but I need to get to the bottom of the problem.
>
>From the code below, if the same id is allocated twice, both processes incremented the same value, but during the update the 2nd process should not have found the record, because nLastNumber would have been since updated by process 1. Ideally I would like to use an SQL equivalent of RLOCK(), but as there isn't one, and record locking in SQL looks (IMO) unnecessarily complicated, I went for the idea of using the "key and modified fields" in the update WHERE clause. As far as I know, SQL-server always locks the record during an update, and if no record is found with the original value(s), no record will be updated. Is my thinking correct? If so, where is the flaw in my thinking and/or code?
>
>Any help would be appreciated. TIA.
>
>Alan
>
>---------------------------------------------------------------------
>
>The next-id allocation function is as follows...
>
>

>FUNCTION NewId             && Returns next id value for passed key field.
>
>LPARAMETER tcIdField                     && field or key to increment
>
>LOCAL lcFieldName, lcFilter, lcResult, lnConnHandle, lnNewId, lnOrigArea, lnRetVal
>lcFieldName = UPPER(tcIdField)               && set view parameter
>lcFilter = [UPPER(cfieldname)=?lcFieldName]  && filter for SELECT and UPDATE
>lnOrigArea = SELECT(0)                       && area to return to
>lnConnHandle = DefaultConnection()           && get connection handle
>
>IF lnConnHandle<0
>	RETURN -1
>ENDIF
>
>DO WHILE .T.                 && loop until update successful
>	=SQLEXEC(lnConnHandle, ;
>	 [SELECT * FROM UniqueId WHERE ]+lcFilter, ;
>	 'UniqueIdV')             && (re)select record into view
>
>	SELECT UniqueIdV
>	lnOrigId = nLastNumber    && store original value for update comparison
>	lnNewId = nLastNumber+1   && get next value
>
>	* Use "key and modified fields" comparison for update.
>	* If record not found, record has changed, program loops to try again.
>	lnResult = ;
>	 SQLEXEC(lnConnHandle, ;
>	 [UPDATE UniqueId SET nLastNumber=?lnNewId ;
>	  WHERE ]+lcFilter+[ AND nLastNumber=?lnOrigId])
>
>	IF lnResult>0
>		EXIT                      && exit loop if successfully updated
>	ENDIF
>
>	* If program got this far, try again
>	WAIT WINDOW NOWAIT "Waiting for "+lcFieldName+" record lock"
>ENDDO
>
>WAIT CLEAR
>=SQLDISCONNECT(lnConnHandle)
>SELECT (lnOrigArea)             && restore original area
>
>RETURN lnNewId
>

>
>The test program (simultaneously running in 2 separate VFP sessions) is as follows...
>

>LPARAMETER tcTable
>
>SET SAFETY OFF                 && to overwrite existing table
>CLOSE DATA ALL
>CREATE TABLE (tcTable) (nId I)
>
>LOCAL X, lnNewId
>
>FOR X=1 TO 1000
>	lnNewId = NewId('nTestId')
>	INSERT INTO (tcTable) (nId) VALUE (lnNewId)
>ENDFOR
>
>WAIT WINDOW NOWAIT tcTable+" loaded"
>

IF 1 # SQLEXEC(lnConnHandle, ;
	 [;
DECLARE @newId INT;
UPDATE UniqueId SET @newId = nLastNumber = nLastNumber + 1 WHERE ]+m.lcFilter;
+ [ SELECT @newId AS nNewNumber] , 'UniqueIdV')
  * ERROR
ENDIF
* nNewNumber