Coldfusion 8 Multi Thread Concurrency

Question

I had a problem with Coldfusion 8 that I posted on Stack Overflow not too long ago Coldfusion 8 doing both CFIf and the CFElse statement that I thought I had narrowed down to a mysql problem, but with (much) further investigation, I have narrowed it down to a Multi threading / Multiple requests per session issue. (That may still be a MYSQL problem, but I have no idea how to fix it)

What is really going wrong with the code :

    <cfif isValid("email", form.email)>     
    <cfquery name="check_user" datasource="#request.dsn#">
         SELECT var_username, var_password
         FROM tbl_users
         WHERE var_username = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.EMAIL#">
         AND var_password = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.PASSWORD#">
      </cfquery>
     <cfif check_user.recordcount EQ 0>
         <cfquery datasource="#request.dsn#" name="insertuser">
             INSERT INTO tbl_users (var_username, var_password)              VALUES
             (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.EMAIL#">,             <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.PASSWORD#">)
         </cfquery>
           <cflogin idletimeout="1800">
         <cfloginuser
                  name = "#FORM.email#"
                 password ="#FORM.password#"
                 roles = "0">
          </cflogin>
         <cflocation addtoken="No" url="#request.secure_url#checkout.cfm">
     <cfelse>
         <cfset client.error_message = "Your Email Address is already registered.">
         <cflocation addtoken="No" url="#request.site_url#New-Account.html">
     </cfif>
  <cfelse>
     <cfset client.error_message = "Your Email Address is not Valid.">
     <cflocation addtoken="No" url="#request.site_url#New-Account.html">
  </cfif>

is that there are 2 concurrent requests being made by the same user. If the requests are slightly staggered, Request A will insert the user into the database, but before Request A can CFLOGIN and CFLOCATION, Request B gets to the CFIF Notices that there is already a user in the database, and creats the CLIENT.ERROR_MESSAGE and CFLOCATION's to New-Account.html . However, if the requests are not staggered, what happens is that the code appears to work, and sends the user to the checkout.cfm page, however in the database, the user is inserted twice.

The steps I have taken to try and resolve this:

1: Using different Databases within the same MYSQL Server (by changing the datasource to one of our other sites that have a similar/identical tbl_users). Same results.

2: Putting the website on a different coldfusion 8/windows 2003 server (but used the same MYSQL Server). Same results.

3: Put a

<cflock name="NewUser" timeout="30" type="EXCLUSIVE">

at the beginning of the code and a

</cflock>

at the end of the code. Same Results.

I really thought that putting a CFLOCK on the code would fix the issue, but it didn't, and now I have no idea what to do next (but it could be because I have never used CFLOCK before, and am using it wrong). Does anyone have any Ideas how to fix this issue so that only one request is sent? Or any ideas why 2 requests are being sent? (I don't think its pebkac, because I am the one doing the testing, and I am not hitting the submit button twice)

Also, I am using a windows 2003 web server, with coldfusion 8. And a seperate windows 2003 server with MYSQL 5.

score 0 · Answer 1 · answered Mar 27 '12 at 23:43

0

Sorry my rank isn't high enough to just post a comment below your question...

I'm wondering why you are getting two or more concurrent requests for a user for that section of code. Are you accessing other CFM files using AJAX or the ColdFusion AJAX functions?

I think that your solution will be to track down why one user will hit that section of code multiple times.

You could use <cflog> to track how this section of code is called.

answered Mar 27 '12 at 23:43

Scott Jibben

2,229
1
14
22

I don't use any of the Ajax functions that coldfusion has on this website. I ran cflog, and like I said, each entry has a double entry, here is a sample from it, it has the email and password attempted to be added- ' "Information","jrpp-632","03/28/12","10:32:50",,"User test@email.com password" "Information","jrpp-630","03/28/12","10:32:50",,"User test@email.com password" ' – Brandon Allison Mar 28 '12 at 14:34
Nice use of cflog. I assume that you have a login form that the user completes one time and clicks the submit button to log in. Now that I think about it, I had a similar problem with one of my projects. It was with IE. IE allowed the user to spam click the submit button and each time it was clicked the form action would get called. My solution was to put in a small JavaScript snippet to disable/hide the button when it was clicked. Could that be the case here? – Scott Jibben Mar 28 '12 at 19:52
No, because I am the person who is doing the testing and experiencing the problems, and I am not hitting the submit button more than once. (however, that would be a good idea to implement for when I put the code live) – Brandon Allison Mar 29 '12 at 14:24
I wonder if you add cgi.HTTP_REFERER to the cflog if it will give an idea where the 2nd submission is coming from. – Scott Jibben Apr 02 '12 at 00:50
I added cgi.HTTP_REFERER to the log file, and it says New-Account.html, which is exactly where it should be coming from, for both entries into the log. – Brandon Allison Apr 02 '12 at 15:18

score 0 · Answer 2 · answered Mar 28 '12 at 16:15

0

You're building a concurrent application. Be happy you found this problem so early in your development cycle. Concurrent applications work best when they use DBMS constraints to manage their concurrency rather than the kind of code you've shown in your example.

I suggest you switch to InnoDB if you aren't using it already, then set up your tbl_users.var_username column as a primary key with a unique constraint in the database.

Then, don't SELECT from your table. Just do the INSERT. You'll get an exception from the <cfquery> insert operation if the username is already there. Handle it appropriately. If the INSERT works correctly, you just added a new user and all is well. If it fails, you tried to add a duplicate user, and you can present an appropriate response on your web user interface.

answered Mar 28 '12 at 16:15

O. Jones

103,626
17
118
172

I am ussing InnoDB, and I decided to try your method, so I made var_username a PK in the database, and have the code do an INSERT, however, it just adds another entry whether or not there is already a user with that username. (it may be because I already have another PK for that table, its a Unique Auto Increase ID). I dont know if having 2 PK's in a table would cause that, but its not working. – Brandon Allison Apr 02 '12 at 15:23
Nevermind, didnt apply the unique key in SQLyog properly, now that I did, it no longer adds 2 entries, however, now there seem to be a whole other mess of issues using this solution. I would rather not bandaid the issue, but find the root cause of the concurrency and solve that. (however, thank you for the unique/Insert approach, I may have uses for that in the future, as soon as I fix these issues) – Brandon Allison Apr 02 '12 at 15:50

Coldfusion 8 Multi Thread Concurrency

2 Answers2