16 Replies Latest reply on Feb 7, 2012 9:42 AM by ACS LLC

    How to stop double clicks registering in a SQL update

    ACS LLC Level 1

      I have a banner server where an end user clicks on a banner, the link goes to my redirect script CLK.CFM, this page records the click through into the SQL dbase and then it has a CFLOCATION to redirect the user to the appropriate web site.

       

      The problem I am seeing is that when there is a delay from the 3rd party site that the end user is clicking multiple times and it's actually recording multiple clicks in the database

       

      I tried writing some simple code where the page that displays the banner sets a session variable, session.click="1", then when they hit the CLK page I check for the value of session.click, if it's empty I don't SQL update, just go straight to the CFLOCATION,  but if there is a value of 1 I update the database and then I set session.click as empty, so in theory only one click should ever record, but even with the longest delay the CFLOCATION will still do it's thing.

       

      Yet after all that, I still see the multiple clicks, and the timestamp shows they happen within a second or or less of each other, so it's definately a multiple press.

       

      Anybody have any idea why my approach above might not work and what I should do to stop this?

       

      Thanks

       

      Mark

        • 1. Re: How to stop double clicks registering in a SQL update
          Dan Bracuk Level 5

          The best way to prevent double submissions is to take the user off the page with the link or form as quickly as possible.  On your click.cfm, you might consider something like this:

           

          output an empty string or a short blurb of text or whatever.

          cfflush

          use cfthread for your insert code

          cflocation to the website.

          • 2. Re: How to stop double clicks registering in a SQL update
            ACS LLC Level 1

            Hey Dan,

             

            When you say as quickly as possible, as you saying that double clicks could still occur? Any idea why my session with cleared session still allows the double records, it looks like even changes to the value of the session variable does not occur fast enough

             

            I've never utilized CFTHREAD will have to read up on that, and I've used CFFLUSH once before, but not sure how that would benefit or how I would use that to counteract this issue?

             

            Thanks

             

            Mark

            • 3. Re: How to stop double clicks registering in a SQL update
              Dan Bracuk Level 5

              A user can click what's available as long as it's available.  Therefore the objective is to make the link/button/whatever unavailable as quickly as possible once it's selected the first time.

               

              The fastest way to achieve this is with js.  We have a custom tag that uses js to do this and it works wonderfully almost all the time.  For us, that's usually good enough but not always.

               

              cffflush could help you because it brings your user to click.cfm before everything has processed.  Once they are there, whatever they selected to get there is no longer available to select again.

               

              Regarding what you are attempting, I'm missing something.  On what page does the banner display?

              • 4. Re: How to stop double clicks registering in a SQL update
                ACS LLC Level 1

                Umm.. I really need to try and make this bullet proof

                 

                Here's what I have

                 

                The banner is shown on here

                popup_showbanner.cfm

                Where I also have <CFSET session.click = "1">

                 

                The hyperlink on the banner goes to

                clk.cfm

                On here I look for session.click IS 1, and if it is the logic within the CFIF session.click IS "1" runs the SQL update on a couple of tables, then it redirects to the 3rd party web site with CFLOCATION

                 

                Just before the CFLOCATION I have <CFSET session.click = "">

                 

                So the theory is, that if somebody hits the banner once, it will hit CLK, and update with session.click IS 1, but then session.click is set to "", so if a second quick click is hit then session.click should be "" and therefore it avoids another SQL hit.

                 

                In practice it appears that the session.click value does not change quick enough to stop the subsequent 2nd and 3rd clicks from registering in CLK.CFM with the value of session.click IS 1

                • 5. Re: How to stop double clicks registering in a SQL update
                  Dan Bracuk Level 5

                  The popup seems a little user unfreindly.  What does the user do to get that to happen and what does he get for his troubles?

                  • 6. Re: How to stop double clicks registering in a SQL update
                    ACS LLC Level 1

                    umm, didn't get my notification of update on the thread..

                     

                    It's a straight forward banner server that displays a JPG or GIF as ad banner, the CLK.CFM is just a click through, database update script and then it's off to the 3rd party site.

                     

                    I've even tried taking the session.click that set on the banner serving page and then set another variable on the CLK page, then look for the value off session.updateclick to decide whether to update or not.. still same problem

                     

                    <CFSET session.updateclick = "0">

                     

                    <CFIF #session.click# IS "1">

                    <CFSET session.click = "">

                    <CFSET session.updateclick = "1">

                    </CFIF>

                     

                    I just don't understand why it would allow the double click up to a second or so later, if the first script should have set the session variable to null and therefore the logic would say do not update the database

                     

                    Mark

                    • 7. Re: How to stop double clicks registering in a SQL update
                      BKBK Adobe Community Professional & MVP

                      ACS LLC wrote:

                       

                      Umm.. I really need to try and make this bullet proof

                       

                      Here's what I have

                       

                      The banner is shown on here

                      popup_showbanner.cfm

                      Where I also have <CFSET session.click = "1">

                       

                      The hyperlink on the banner goes to

                      clk.cfm

                      On here I look for session.click IS 1, and if it is the logic within the CFIF session.click IS "1" runs the SQL update on a couple of tables, then it redirects to the 3rd party web site with CFLOCATION

                       

                      Just before the CFLOCATION I have <CFSET session.click = "">

                       

                      So the theory is, that if somebody hits the banner once, it will hit CLK, and update with session.click IS 1, but then session.click is set to "", so if a second quick click is hit then session.click should be "" and therefore it avoids another SQL hit.

                       

                      In practice it appears that the session.click value does not change quick enough to stop the subsequent 2nd and 3rd clicks from registering in CLK.CFM with the value of session.click IS 1

                      That contains a loophole, even if only a tiny one. Suppose one clicks twice in quick succession on the banner. Then the following event can happen.

                       

                      Two threads are created to open the page clk.cfm, each having first initiated the value session.click = 1. Then both threads open clk.cfm (almost) simultaneously and each proceeds to update the tables. The result is 2 updates. To prevent this, use, for example, a session lock.

                       

                      popup_showbanner.cfm

                       

                      <cflock scope="session" type="exclusive" timeout="5">

                          <cfset session.click = 1>

                          <a href="clk.cfm">Update it</a>

                      </cflock>

                       

                      clk.cfm

                       

                      <cflock scope="session" type="exclusive" timeout="5">

                          <!--- Store click in a local variable to enable you to immediately reset the session variable. --->

                          <cfset variables.click = session.click>

                          <cfset session.click = 0>

                       

                              <! --- Check if variables.click IS 1. If so, run the SQL update. --->

                      </cflock>

                       

                      <!--- Do cflocation to 3rd party site --->

                      • 8. Re: How to stop double clicks registering in a SQL update
                        ACS LLC Level 1

                        I've used CFLOCK once before a long time ago and can't remember why. I read the documents and from what I see it will not allow the CLK script to be called on more than one occassion for that person? Right?

                         

                        a) Do I need to give the CFLOCK a name? And then use the same name on all pages? I have a UUID in one of the session variables that I could use

                         

                        b) Will this only effect that person? It literally does not effect the whole of coldfusion right? So other users won't be effected by it should they visit/click at the same time. It does not make the script totally exclusive and locked?

                         

                        c) Why do I need to change the variable from the session to the variables.click? and not another session.? It seems similar to what I already did in that last attempt but you use variables instead of sessions.

                         

                        Appreciate your clarification

                         

                        Thanks!

                         

                        Mark

                        • 9. Re: How to stop double clicks registering in a SQL update
                          BKBK Adobe Community Professional & MVP

                          ACS LLC wrote:

                           

                          I've used CFLOCK once before a long time ago and can't remember why. I read the documents and from what I see it will not allow the CLK script to be called on more than one occassion for that person? Right?

                          In an exclusive session lock, two separate threads cannot write to the same session variable at the same time. This is restricted to one particular user session.

                           

                          a) Do I need to give the CFLOCK a name? And then use the same name on all pages? I have a UUID in one of the session variables that I could use

                          No, you don't need to. However, in this case, you may use a named lock as an alternative to the session-scope lock.

                           

                          b) Will this only effect that person? It literally does not effect the whole of coldfusion right? So other users won't be effected by it should they visit/click at the same time. It does not make the script totally exclusive and locked?

                          Yes, it will only affect the particular user, the one doing the clicking.

                           

                          c) Why do I need to change the variable from the session to the variables.click? and not another session.? It seems similar to what I already did in that last attempt but you use variables instead of sessions.

                          You actually don't need to. That is just my style.

                           

                          Since the test value is 1, I stuck with the integer theme and set the alternative value to 0. Also, variable variables.click is meant to be used locally on the page clk.cfm, and nowhere else. So it need not be in shared scope.

                           

                          As I have explained, the construction <cfset variables.click = session.click> <cfset session.click = 0> is simply a means to transfer the value to a local variable. That way, you can quickly reset the value of session.click. That way, any other waiting threads will get the new value as soon as possible.

                          • 10. Re: How to stop double clicks registering in a SQL update
                            ACS LLC Level 1

                            umm.. I'm still a little unclear. I get the process. aside from the CFLOCK, it's the same as my approach by switching the value to another variable so I can use it in the logic for the update of the database. but why variables. and not session. Am I correct in saying that there is no functional advantage or disadvantage over using  session. vs variable. with the only difference being that variable. is only available on the page that it is written, which is probably a much cleaner way to do it

                             

                            So this won't effect other users.

                             

                            If I have the CFLOCK on there, do I need the session switching? with the lock does that not stop further sessions/hits being created?

                             

                            Thanks

                             

                            Mark

                            • 11. Re: How to stop double clicks registering in a SQL update
                              ACS LLC Level 1

                              Been a busy day, just getting around to trying this

                               

                              The banner serving page is a template called into another page. The question I now have is, the CFLOCK, does it have to wrap around the entire served page? Or just the hyperlink and the session creation?

                               

                              Thanks

                               

                              Mark

                              • 12. Re: How to stop double clicks registering in a SQL update
                                BKBK Adobe Community Professional & MVP

                                ACS LLC wrote:

                                 

                                The banner serving page is a template called into another page. The question I now have is, the CFLOCK, does it have to wrap around the entire served page? Or just the hyperlink and the session creation?

                                 

                                 

                                The lock relates to just the code that has to do with session.click

                                • 13. Re: How to stop double clicks registering in a SQL update
                                  ACS LLC Level 1

                                  Got it.

                                   

                                  I actually placed it on the CFM page that pulled in the template so the whole page was covered, not sure if that causes any potential issues, but so far it actually seems to have done the trick!!! I'm just keeping a close eye on it

                                   

                                  So basically anything that changes the session.click in anyway, I should have the lock around. I had also cflock'd a good chunk of the CLK click through page, all of it aside from the last command for the CFLOCATION.

                                   

                                  I'll give it 24 hours and see if it pans out, and if so maybe I'll try less CFLOCK'd code to see if that still works

                                  • 14. Re: How to stop double clicks registering in a SQL update
                                    BKBK Adobe Community Professional & MVP

                                    ACS LLC wrote:

                                     

                                    So this won't effect other users.

                                    It affects all users! However, session code affects each user individually, and has no direct side-effects across users.

                                     

                                     

                                    If I have the CFLOCK on there, do I need the session switching? with the lock does that not stop further sessions/hits being created?

                                     

                                    No, locks have nothing to do with session switching or session creation.

                                     

                                    ACS LLC wrote:

                                     

                                    umm.. I'm still a little unclear. I get the process. aside from the CFLOCK, it's the same as my approach by switching the value to another variable so I can use it in the logic for the update of the database. but why variables. and not session. Am I correct in saying that there is no functional advantage or disadvantage over using  session. vs variable. with the only difference being that variable. is only available on the page that it is written, which is probably a much cleaner way to do it

                                     

                                    Let me explain once and for all, using a hypothetical example. You and I are visiting your site. Session management is on. So, for the sake of argument, let's say ColdFusion identifies your session as 812_1B48569F88259944-62E0619E-14DA-E9DD-3FC92D5C5C26147A and mine as 925_F5A44BDC1F56ADC0-A87A3D5B-F1C4-AA55-0111DC25FFBD35AC.

                                     

                                    ColdFusion will pass your ID to every CFM page you open during your session. Likewise, it will pass my ID to every page I open. As you can see, the IDs are usually so randomly generated that there is practically no chance of a mix-up between two users' sessions.

                                     

                                    Now, suppose you open the page popup_showbanner.cfm in 2 separate browser windows. Then, both windows are related to your session ID. This is because ColdFusion would have sent an encoding of your session ID to your browser, as a cookie.

                                     

                                    Imagine that you have a mega-mouse that can click on both banners at the same time. You would then start 2 requests (that is, 2 threads) within the same session.

                                     

                                    An exclusive session lock allows only one such request (usually the first) through. It lets any others wait until the allowed request has completed the code within the lock.

                                     

                                    Suppose then that session.click is currently 1, and that a click on the banner page has generated a new request (call it thread T1). The thread proceeds further to the code on clk.cfm.

                                     

                                    You would ideally want ColdFusion to keep any other threads(for example, requests to the banner page) waiting, until T1 has finished resetting the value of session.click to 0. Also, for efficiency, you want this resetting to be done as quickly as possible. Otherwise, ColdFusion might keep other threads waiting for longer than necessary. This means placing the code session.click=0 as high up in the page clk.cfm as possible.

                                     

                                    However, if you place session.click=0 high up in clk.cfm, then the test {if(session.click is 1) then do update} will become absurd. The trick is therefore to find a way to temporarily hold the value of session.click, to enable you to quickly reset it. That explains the use of variables.click.

                                     

                                    Meanwhile, I, as another user of your site, have been busy doing my own thing. My session ID and cookies are unique to me. So we have been totally oblivious to each other.

                                    • 15. Re: How to stop double clicks registering in a SQL update
                                      BKBK Adobe Community Professional & MVP

                                      ACS LLC wrote:

                                       

                                      Got it.

                                       

                                      I actually placed it on the CFM page that pulled in the template so the whole page was covered, not sure if that causes any potential issues, but so far it actually seems to have done the trick!!! I'm just keeping a close eye on it

                                       

                                      So basically anything that changes the session.click in anyway, I should have the lock around. I had also cflock'd a good chunk of the CLK click through page, all of it aside from the last command for the CFLOCATION.

                                       

                                      I'll give it 24 hours and see if it pans out, and if so maybe I'll try less CFLOCK'd code to see if that still works

                                      Nice to hear it's holding up so far. I would experiment with some quickfire double and triple clicking. Good luck.

                                      • 16. Re: How to stop double clicks registering in a SQL update
                                        ACS LLC Level 1

                                        Looks like it works, I let it run all night and today and I don't see any double clicks that are close to each other. I do get them if they see the same banner at another time, but the time between them is enough to convince me that the problem appears to have been cured.

                                         

                                        I could try moving the CFLOCK over a smaller area, but if it 'aint broke... although I am curious :)

                                         

                                        My only concern was whether I was locking out other users, but it looks as though it's on a session per session basis, so it only locks code to that particular person.

                                         

                                        Thanks for the help, much appreciated. I'll mark this as answered

                                         

                                        Thanks

                                         

                                        Mark