I am not sure if I have put right words in title to explain. But here it is.
Problem exceptionally occurred during bad internet reception which triggered double requests before the first one completed. In result two database entries were made.
Application is only handling double request with a SELECT query first to check if there is already a same record in db. In that case old record is updated with an UPDATE statement. Otherwise an INSERT statement is used to insert a new record. The SELECT/INSERT/UPDATE are enclosed within CFTRANSACTION.
<cftransction> <cfquery></cfquery> //SELECT <cfif SELECT.found> <cfquery></cfquery> //UPDATE <cfelse> <cfquery></cfquery> //INSERT </cfif> </cftransaction>
In most cases when there is some delay in two requests, it works fine. To reproduce the problem,
- I first tried initiating two simultaneous requests via browser and it didn't trouble in many tries.
- To make two requests quick, I used two post statements right after one on the page (it's an AJAX app). It also worked as expected as second request was only updating the previously entered record.
- What I could do to make those two requests even quicker, I reached out at the server itself (where both the ColdFusion and MySQL are running) and opened the browser. Now one browser request with two post statements started frequent double entries.
Above code might insert two entries when the REQUEST.2 SELECT runs before REQUEST.1 INSERT. What could be possible solution on the server end?
Handling on the client side with disabling subsequent requests before the first request completes. but want to secure it on the server.
So I used the solution porvided by @Leigh in comments to original question. Just answering to make it standout and what other things I had to do to actually use this solution.
While both the solutions seemed good to solve the problem but REPLACE was more attractive as it only required me to change "INSERT" keyword in the queries with "REPLACE"
With a quick overview from the docs, REPLACE exactly replaces and works like INSERT statement but performs the purpose only when a row is found with duplicate PRIMARY KEY or UNIQUE index.
1- As I required three columns to be unique in that sense which I was using in a WHERE clause with SELECT before inserting them. I had to add a UNIQUE index on those three columns before REPLACE could work to restrict duplicate rows.
2- If duplicate row found, REPLACE first deletes the old row and then inserts the new one. This process also could loose some values from other columns in the table. But in my application, these columns are updated on every REQUEST while the values for those are derived from other tables. So it was not problem for me as the data can be updated for the new row inserted.