Re: Duplicate entry inserted in the SQL database while calling an ASP

Noemi wrote on Tue, 22 Jan 2008 22:16:02 -0800:

Hi All,



I hope I posted this inquiry under the correct forum.



To start off, I have been working on a bug where duplicate inserts
happen when calling an ASP page that does the DB insert processing.
We have a software where a user is given two options to register: 1)
via the software's panel and 2) over the internet. Option 1 is known
to be the "Auto
Registration" - user is requested to fill out a form, supply the
details such as customer name, SN(serial number), site code etc., then
clicks "Register
Now" button. A background process (implemented in C++ socket
programming) is started that sends out a request to an ASP page. This
ASP page (RegModule.asp) does the registration process: it does checks
like SN validity, maximum of number of allowed registrations reached,
etc. If the conditions were satisfied, then it inserts the sent
values in the DB. In the event that the website doesn't send back a
"200 OK" response, the client's software panel retries sending a
request to the server. The retry is done twice, and if still
unsuccessful, prompts the client error messages like "Auto
Registration has failed" or something like that. There were some
records that have duplicates - every field are exactly the same up to
the seconds of its dateFirstRegistered field. I was asked to
investigate how did this happen.



I have made a number of inferences like:



1. Did the client clicked the "Register Now" button a number of times?

- This is not possible because the moment the "Register Now" button is
clicked, it becomes disabled.



2. How about disabling the connection retry?

- The rate of successful registration will be too low.



3. What if: The ASP page is called on the first request, began
processing up to the point where it assigns the query values (customer
name, serial number, etc) in the fields of the recordset when suddenly
the connection is interrupted. The software control panel then tries
again to connect and send out a second request. The second request
catches up to what the first request was doing before the
disconnection, and at almost the same time they both inserted their
records into the database.



Is inference 3 possible? Or could it be that the both request were
processed on different threads?



Hope someone could shed some light on the matter. Thanks in advance!


I'd say #3 is your most likely reason.

As Bob has pointed out, if you set a natural unique constraint on the rows
then you can avoid this - a second insert would fail because the same data
already exists in the table, and you could this gracefully in the
application (either at the ASP end sending the same response as if the
insert succeeded, or in the client application in response to a "duplicate
entry" response from the ASP should you need to be able to record this for
debugging or other reasons) as a successful registration. Just pick all the
columns that are considered representative of a duplicate registration, and
create a unique constraint on those columns.

--
Dan


.

Loading