Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.
Course Of The Month
3 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Coldfusion duplication issues! How should I deploy my inserts to avoid this?
Posted on 2013-12-09
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?
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
3
8 Comments
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.someot
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?
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.someot
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?
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
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
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)?
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)?
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!
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!
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 ...
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 ...
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!
Featured Post
Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Anyone familiar with PhotoSwipe? | 3 | 42 | |
| REST call Failing | 1 | 41 | |
| Set css in function | 11 | 56 | |
| Ajax success not firing alert | 6 | 45 |
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand.
Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will receive an overview of the basics of CSS showing inline styles.
In the head tags set up your style tags:
(CODE)
Reference the nav tag and set your properties.: (CODE)
Set the reference for the UL element and styles for it to ensu…
Suggested Courses
Course of the Month3 days, 13 hours left to enroll
751 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.