Duplicate Record Inserts

[Ren0420]

The problem is that records are not being inserted only once. The included code
is processed in a page after a form. The following 3 events randomly occur:
1. record inserted once
2. record inserted twice
3. error after record is inserted with a duplicate primary key (empID)

JS alerts before and after each query pop-up only once, and html messages are
written once. The page seems to be processed once, but the queries are randomly
processed. cflock around each query didn't fix it. Has anyone run into this
problem with SQL Server 2000 and how did you solve it?

Thanks for your time,
Ren



<cfquery name="getNextTempEmpID" datasource="#request.dataSource#">
SELECT MIN(EMPLOYEE_ID) AS MinID
FROM TBL_ALL_EMP
</cfquery>

<cfset empID=getNextTempEmpID.MinID - 1>

<cfquery name="addTempEmp" datasource="#request.dataSource#">
INSERT INTO TBL_ALL_EMP (EMPLOYEE_ID,
EMP_FRST_NM,
EMP_LST_NM)
VALUES ('#Trim(empID)#',
'#Trim(FirstName)#',
'#Trim(LastName)#')
</cfquery>

[jdeline]

You can have a race condition where two users, at the "same instant", could
both grab the same value for MinID, both decrement it and only the first one is
inserted correctly. The second one will throw an error since the empID is
already set in the database. Use CFTRANSACTION to resolve the problem. See
[url]http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Tags103.htm#1104164[/url]
for more information.

[Dan Bracuk]

Originally posted by: Ren0420
The problem is that records are not being inserted only once. The included
code is processed in a page after a form. The following 3 events randomly occur:
1. record inserted once
2. record inserted twice
3. error after record is inserted with a duplicate primary key (empID)

JS alerts before and after each query pop-up only once, and html messages are
written once. The page seems to be processed once, but the queries are randomly
processed. cflock around each query didn't fix it. Has anyone run into this
problem with SQL Server 2000 and how did you solve it?

Thanks for your time,
Ren



You are doing some very unusual things. Topping the list is subtracting one
from the min rather than adding one to the max. You also appear to be using a
numeric string as your primary key. Why don't you just use a number?

I don't do very much work with sql server, but doesn't it have a way to just
generate the next number for you?

[MikerRoo]

Protect your data by design and by transaction.

First, go into Enterprise manager or Query analyzer and put an index on
EMPLOYEE_ID requiring unique values. You may have to remove duplicate or
corrupt rows to do this but you don't want such rows anyway.

Next, put another unique index on the combination of columns: FirstName and
LastName (and also middle name if used).
Again, removing bad data as needed.

Then, make one of these indices clustered. Which one depends on what you
search on the most (probably ID).

Now, your DB will reject bad or duplicate data by design and Coldfusion
mistakes will thus be caught quicker.

Finally, the suggestion to use <cftransaction> was a good one. But for SQL
server there is a better way. (Faster too).

Use SQL server's transaction handling like in the attached code:



<CFQUERY name="addTempEmp" datasource="#request.dataSource#">
BEGIN TRAN sqlTranEmpInsert

INSERT INTO
TBL_ALL_EMP
(
EMPLOYEE_ID
, EMP_FRST_NM,
, EMP_LST_NM
)
SELECT
(
SELECT MIN (EMPLOYEE_ID) - 1 <!--- If EMPLOYEE_ID is not
numeric, you will need Cast() here. --->
FROM TBL_ALL_EMP
)
, '#Trim (FirstName)#'
, '#Trim (LastName)#'

COMMIT TRAN sqlTranEmpInsert
</CFQUERY>

[BKBK]

datasource="#request.dataSource#"
Can well give rise to spooky effects. The application scope is more appropriate.