Duplicate Records inserted in a table
-
Hi There,
I have one stored procedure (used to insert/update a record) performing following steps.
Parameter: PK_CatId
Step#1: Check if there is any record in Table_1 for PK_CatId
Step#2: If there is any record, update Table_1 where CatId=PK_CatId
Step#3: If Not, Insert into Table_1 for PK_CatId
Note: Table_1 is not having any primary key on PK_CatId
In a web application scenario when there is two requests are coming to insert one PK_CatId(value is same for both the requests) it is inserting 2 records for the same PK_CatId. Because when in first request step1 is executed no records were found and it is about to insert record and that time step#1 of second request is performed so request 2 also inserting the record.
I didn't not put any primary_key to this table because both the data is very important for me and in the above scenario if primary key violation exception occurs I will be losing the second request data.
Please provide me a solution for this...
Thank you in advance.
- Ramesh U
-
Hey Ramesh
Is PK_CatId meant to be a incrementing number? If so, use an IDENTITY column instead to create unique numbers.
Other that that, what happens before step #1 ? Where does PK_CatId come from ?
Julian Kuiters
juliankuiters.id.au -
Hi Veteran,
Thank you for your reply. More or less the stored procedure is excuting as expected. But, Here the problem occurs only when two requests were executing on the server at the same time. If I put the primary key it will cause primary key violation exeception and the second request data will be lost. Is there any way that I can catch that exception and run update statement when that exception occurs. - Thankyou
-
You could catch the exception in the applications ADO Connection object, however that won't scale well. (You're talking about two requests executing at once... i've worked with hundreds executing at once).
Because I don't know what happens before Step #1, there's a number of things you could do.
If you are creating the PK_CatId by getting Max(Table_1.CatId) + 1 then you'll constantly be getting this problem. Creating the CatId as an INT IDENTITY(1,1) column would solve the problem. Pass in NULL when you want to create a new CatID, and Change step#3's logic to "If Not or PK_CatID IS NULL, Insert into Table_1 for PK_CatId". Pass in the PK_CatId only when you are updateing a record.
Otherwise, if PK_CatId is a value of another table, and is used here as a foreign key, then duplicate values would be ok.
Julian Kuiters
juliankuiters.id.au -
Hi Julian,
Thank you for your suggession but I am not getting the Cat_id as identity column. I am explicitly passing the id and I need only one record in that table for that catid. Actually that is my requirement. - thank you
-
Ah. In that case I'd put a unique constraint against the column, and handle the errors in the web app. If you get two requests at once, only one will succeed, the other will generate an error which you can trap and handle in the web app.
Julian Kuiters
juliankuiters.id.au -
Hi,
If Data is non numeric :
Insert your record without PK_CatId and create a trigger for insert which will update newly inserted record with [logical] Id.
If Data is numeric : Create The Column An Identity Increament
-
try this code .... it will insert rows from table1 to table2 if doesn't exist.
insert into table2
select * from table1
where not exists
(select * from table2 where table2.eid = table1.eid)
Jaimin
-
If I have understood your problem correctly, what you need to do is to encapsulate all three of your steps into a single transaction and ensure that you lock the table explicitly by using a locking hint when you begin step 1:
Something like this:
BEGIN TRAN
Step#1: Check if there is any record in Table_1 for PK_CatId (WITH XLOCK)
Step#2: If there is any record, update Table_1 where CatId=PK_CatId
Step#3: If Not, Insert into Table_1 for PK_CatId
COMMIT TRAN
In this way, if a second process tries to do the same, it will be blocked for a period of time until the first process completes the entire transaction and it is commited. In other words, the second process should not be allowed to start the transaction until every single component (all 3 steps) of the transaction have completed successfully (or rolled back if that's the case).
-
Hi Grambow,
Thankyou for your reply. I followed the same steps except maintaining the Transaction in Stored procedure.
I got the solution now. I have done like this.
put the primary key on the column
Step1: Insert statement to insert record
Step2: Iff error code is 2627 then run update statement.
error code : 2627 stands for primary key failure.
insert INTO Category values (@iCatId, @CatStatus)
set @iERROR=@@error
if @iERROR = 2627
BEGIN
Update Category set Status=@CatStatus Where catid=@icatid
return(0)
END
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply