Solved

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

Posted on 2013-12-09
8
357 Views
Last Modified: 2013-12-10
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?
0
Comment
Question by:milestonetech
  • 5
  • 3
8 Comments
 
LVL 24

Accepted Solution

by:
dgrafx earned 500 total points
ID: 39708641
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
 

Author Comment

by:milestonetech
ID: 39709222
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
 
LVL 24

Expert Comment

by:dgrafx
ID: 39709365
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
 

Author Comment

by:milestonetech
ID: 39709383
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:dgrafx
ID: 39709409
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
 
LVL 24

Expert Comment

by:dgrafx
ID: 39709414
so what's with the low grade ???
0
 

Author Comment

by:milestonetech
ID: 39709419
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
 
LVL 24

Expert Comment

by:dgrafx
ID: 39709424
and the low grade?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now