Duplicate entry inserted in the SQL database while calling an ASP

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!

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

Noemi wrote:
> 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.

Before we go any further, let's talk fundamentals:
By "duplicate", I am assuming you have a field or a combination of field
that can be used to uniquely identify a record. Correct? And a duplicate
record refers to a record that has the same data that exists in those fields
in another record. Correct?
Well, this is one of the functions of a relational database: to prevent
duplicates. Only it cannot do it on its own: the database designer has to
tell it how to identify unique records. This involves identifying the "key"
field or fields in a table and creating a unique index or primary key
consisting of those key fields. That way, when an attempt is made to insert
a "duplicate", the database engine will reject it, raising a "violation of
primary key or unique index" error.

Since you have not disclosed your backend database, I cannot get into
specifics: you will need to read the documentation for that database to
discover how to create this primary key or unique index.

OK, let's read on.

> 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.

Really? Why? Can't the process communicate with the database directly?

> 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.
>
None of these "inferences"/guesses seems likely. Again, with a correctly
designed unique index or primary key, part of this problem would be solved,
and the other part would be a little easier to troubleshoot since you could
now identify where in the process the "violation" error is being raised.

If you are using SQL Server as your backend, you can also create a stored
procedure that checks for the existence of a record containing the data
about to be inserted before attempting to insert the new data. If a record
already exists, you can have it update the existing record rather than
attempting to insert a new one.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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

Hi Bob,

Thanks for your reply.

The backend db is SQL Server. Yes, the records are identified by a primary
key (CustID). And yes, the duplicates are two records that have the same
data but identified by two different CustID. I've learned that one can set
the primary key to increment on its own every time inserts are done. Its
weird because right before inserting the record, the code shows that it first
fetches a control number from a Control table, set that as the primary key
for record to be inserted and increments the control number. The process of
checking for the existence of a record and saving of a new record (with the
primary key acquired from the Control table) were done by executing SQL
commands using ADO.

Yeah, I would really like to implement your suggestion of the stored
procedure. But, I can only do so much as to tweaking the existing code. And
what's more complicated, I'm doing changes on the live site so it is quite
risky.

Much appreciated!

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

Noemi wrote:
> Hi Bob,
>
> Thanks for your reply.
>
> The backend db is SQL Server. Yes, the records are identified by a
> primary key (CustID). And yes, the duplicates are two records that
> have the same data but identified by two different CustID. I've
> learned that one can set the primary key to increment on its own
> every time inserts are done.

Ah, so you have an artificial primary key. You also need a unique index
on the records' natural key, i.e., the fields that you are using to
determine that dupes have been inserted.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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

Noemi wrote:
> Hi Bob,
>
> Thanks for your reply.
>
> The backend db is SQL Server. Yes, the records are identified by a
> primary key (CustID). And yes, the duplicates are two records that
> have the same data but identified by two different CustID.

Let me expand on my previous message. Say you have a table, Table_1
containing columns PK, col1, col2 and col3, with a primary key created
on PK (similar to your scenario). The table creation script would look
like this:
CREATE TABLE dbo.Table_1
(
PK int NOT NULL,
col1 varchar(50) NOT NULL,
col2 int NOT NULL,
col3 varchar(50) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
PK
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO


You now have a table whose rows are uniquely identified by the PK
column.
However, you also have a business rule that two rows with the same data
in col1 and col2 are to be considered duplicates and should be
disallowed. You can have the database engine enforce that by creating a
unique index on those two columns, like this:

CREATE UNIQUE NONCLUSTERED INDEX IX_Table_1_U ON dbo.Table_1
(
col1,
col2
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

With that index created, the process that attempts to insert a duplicate
will receive an error message, allowing it to be more easily identified.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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