Coldfusion duplication issues! How should I deploy my inserts to avoid this?

Hi all -
I have a pretty serious issue with data duplication on form submit that I have to resolve.  I'm using ColdFusion 9, and we found that the root of the problem was using javascript to submit a form on older IE browsers was double submitting, and, thus, running requests concurrently (I'm assuming) which was creating the duplicate.

We fixed the javascript, but the way I look at it, its just merely a Band-Aid for a large problem I have on my server side stuff (ColdFusion).

My current script runs something like this:
- Cfquery ---> check to see if serialized form is defined (I have a unique ID I'm passing in the form, check to see if that unique ID is defined in the database).
- CFIF recordcount of query is greater than 0
- Cfquery ----> Insert
- Cfelse
- Cfquery ----> Update

What I'm GUESSING is happening is that when the double click happens, the queries to check are happening simultaneously, and by the time the first click inserts, the second click has already checked and found zero recordcount, thus inserting a duplicate.

I've tried to do some digging on how to solve this, I'm clueless.  Someone said stored procedure, someone said use IF NOT EXISTS in the SQL (but I, believe it or not, managed to generate a duplicate with this method somehow as well).

Time to ask the experts...what is the BEST way to avoid duplicate inserts when working with ColdFusion?  How should I resolve?
milestonetechAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
dgrafxConnect With a Mentor Commented:
i believe you have your insert / update logic backwards.
if recordcount is gt 0 then you update - otherwise you insert.
it may be that you just worded it wrong in this post - but if not then that is a big issue!

it's hard to guess what javascript you are using and if it is problematic or not.
but as far as sql for an insert / update - you would do something like:

IF NOT EXISTS (select 1 from yourtable with (nolock) where ID = '#form.ID#')

BEGIN
INSERT INTO yourtable
(ID,someotherfield)
VALUES
('#form.ID#','#form.someotherfield#')
END

ELSE

BEGIN
UPDATE yourtable
SET someotherfield = '#form.someotherfield#'
Where ID = '#form.ID#'
END

note that you may still have faulty javascript submitting the form.
did you try removing it and using a standard submit button to test if the problem is the javascript or not?
0
 
milestonetechAuthor Commented:
hi Dgrafx, can you explain the nolock portion?  THe above is actually to a tee how we currently have it....minus nolock.  Still something managed to squeak in.  Maybe the query was just cached?  Will the above catch it every time?  Is that 100% certain to work?

I was avoiding going into depth on the javascript portion, but ultimately it was older browsers using onClick submit form for whatever reason double clicks unless you put return false at the end.  So that problem is solved... however....

I'm really more interested in the backend issue with this because, hypothetically, with the amount of traffic I have there is a very real possibility that two people could submit the same form at the same time without javascript issues.  So all external factors put aside for a minute, can you (or anyone else) explain the logic of how SQL works wtih the IF NOT EXISTS function?  Does SQLs formula continue to check the truth to the "if not exists" during the insert?  

Otherwise, I could have the same scenario.  If two people execute that SQL at the same moment, SQL starts to execute one persons call first, the other call looks to see IF NOT EXISTS (and currently it doesnt, but meanwhile an insert is happening on the first users click)...does SQL continue to look to make sure that statement is true?  AKA this is a fail safe way to avoid duplicates?

Thanks much, I may be hyper paranoid but I need to be thorough with this (you usually do when your job is on the line :)  )..
John
0
 
dgrafxCommented:
yes it's certain to work ...
and db records will be unique based on ID passed in.
==========================
what is this ID that's coming in from the form?
you are now implying that it might not be unique - more than 1 user submitting the same ID ???
Why?
it may be that you need to redo your logic.
what is the purpose of it?
is it a random id or ???
are you simply wanting a unique identifier for each record in the database and nothing more?
if yes then you are going about it incorrectly.

please explain

also i think you are glossing over your javascript submit problem.
even if you have back end code to catch problems - why would you not want to fix it (i.e. do it properly)?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Go Premium
 
milestonetechAuthor Commented:
dgrafx, I'm not glossing over it, I've already fixed it...hence the above " was avoiding going into depth on the javascript portion... that problem is solved."  And regarding all of the additional questions, it would take 20 minutes to explain the environment and the surrounding factors which 1) cannot be changed and 2) are outside of the scope of things I can actually affect on this project so there isnt an "ability" to redo the logic.  Its a pre-existing system.

The bottom line is that I'm only interested in understanding the SQL pattern of checking values and storing simultaneously. If you're saying that its certain to work based on those checks and executions, that's all I was looking for.  Thanks!
0
 
dgrafxCommented:
ok - fair enough ...

considering so many variables - remove the "with (nolock)" in the code i posted.
that is code I use to speed up a query by not performing a complete check (long story) - with your iffy environment remove it so that all checks are made.
and the code will then be 100%.

you realize though that if 2 people come in with same id - the 2nd will be updating the first users insert.
just fyi - and don't know if that's a concern in your environment.

good luck ...
0
 
dgrafxCommented:
so what's with the low grade ???
0
 
milestonetechAuthor Commented:
Thanks Dgrafx, its not, they have different IDs, but with the inner joins it would create a duplicate entry, not write to each other (also a long story :) )...just trying to avoid dups based on the scenario.  Thanks again!
0
 
dgrafxCommented:
and the low grade?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.