WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:
Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users
Course Of The Month
9 days, 18 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
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 18 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.