Duplicate Record

Question

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?

Please explain better what you are trying to do. I have a vague idea that you are trying to [use a table as a queue](http://rusanu.com/2010/03/26/using-tables-as-queues/) but this is difficult/impossible to follow. — Martin Smith, Dec 28 '11 at 11:22

score 1 · Answer 1 · edited May 23 '17 at 11:55

1

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

edited May 23 '17 at 11:55

Community

1
1

answered Dec 28 '11 at 11:36

gbn

422,506
82
585
676

thanx dude... can u tel me how i can set OUTPUT INSERTED.certid, INSERTED.certiname to local variable because this statement always updating top 1 record when will be my certificate has over that this statement nothing will be update .. it's reason i want to store value to local variable – Abrar Ahmad Dec 29 '11 at 07:06
@AbrarAhmad: you'd need to load a table variable, then select from that or join to it. – gbn Dec 30 '11 at 09:01

score 0 · Answer 2 · answered Mar 30 '12 at 06:53

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

Duplicate Record

2 Answers2