Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.
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
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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.
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month4 days, 14 hours left to enroll
670 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.