CODEWITHSUNDEEP
sqlsql-server
Abrar Ahmad
Abrar Ahmad

Reputation: 31

Duplicate Record

I'm facing a duplicate record problem; concurrency issue. I'm retrieving the record from database:

SELECT TOP 1 certid, certiname
  FROM certificate
 WHERE issued = 0
   AND year = 2011

After retrieving the record I'm inserting the record different table by using a procedure...

There are multiple tables affecting that reason. I also used SQL transaction either affected all transaction or none, commit or Rollback. In this procedure I am also updating a certificate

UPDATE certificate
SET    issued = 1
WHERE  certid = @certid

but same certificate issued more than one policy..

How can I avoid this problem?

Upvotes: 0

Views: 149

Answers (2)

user966380
user966380

Reputation: 7

IF EXISTS (SELECT * FROM sysobjects WHERE type='U' and name='Temp_Table1') begin Drop table Temp_Table1 end go select * into Temp_Table1 from Table1 go TRUNCATE TABLE Temp_Table1 go CREATE UNIQUE INDEX unqT ON Temp_Table1(id DESC) WITH IGNORE_DUP_KEY go INSERT INTO Temp_Table1 SELECT * FROM Table1 ORDER BY id DESC go

TRUNCATE TABLE Table1 go INSERT INTO Table1 SELECT * FROM Temp_Table1

Upvotes: 0

gbn
gbn

Reputation: 432180

If you are trying to have concurrent processes select one row each without overlap, then you need some hints (ROWLOCK, READPAST, UPDLOCK). More info here: SQL Server Process Queue Race Condition

If you want to select/update in the same transaction, use the OUTPUT clause

UPDATE TOP (1) certificate WITH (ROWLOCK, READPAST, UPDLOCK)
SET    issued = 1
OUTPUT INSERTED.certid, INSERTED.certiname
-- or OUTPUT INSERTED.certid, INSERTED.certiname INTO @SOmeTableVariable 
WHERE  issued = 0
   AND year = 2011

Upvotes: 1

Related Questions