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"