SP Results in "Cannot Insert Duplicate Key Row in the Object"
-
Hi,
We have two tables by name available_sn and serialnumber. The table available_sn will have a list of about 55 lakhs MAC Address.
Now in our Programming application we call a Stored Procedure called usp_GetMac and this procedure will perform the following steps.
1) Select Top 11 [value] from available_sn Where status=0 to a cursor [Status 0 means not allocated]
2) Insert into serialnumber the [value] in the cursor.
3) Update available_sn as status=1 where value=[value] from cursor.
The USP_GetMac Procedure is as below. Actually our Networking equipment will be programmed with these MAC Address and each equipment needs 11 mac address. At any single point of time totally 142 such units will get programmed. So this results in error "Cannot Insert Duplicate Key Row in the Object serialnumber with unique index "serialnumber_IDX01". Can anyone help to modify this SP to make the request to wait in queue and serve the programming application's request.
GetMAC SP is as below
***************************************************************************
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE USP_GetMac
@TraceId Varchar(30), @MacNo Int, @HexOutPut Varchar(1000) OutPut
AS
Declare @Temp Table (Value varchar(200))
Declare @value varchar(30), @iCtr Int, @iLimit int,@iCheck int,@UnitID varchar(200)
Declare @Hex_output varchar(1000)
Select @UnitID=[UnitID] From SerialNumber With (NoLock) Where [value]=@TraceID
Select @icheck=Count(*) from SerialNumber With (NoLock) Where [UnitID]=@UnitID
set @Hex_output = @TraceId
-- To Check whether MAC Address is already allocated for this Equipment, if yes then
--return the previous allocated MAC Address
if (@iCheck > 2)
Begin
DECLARE Mac_Cursor CURSOR FOR
Select Top 11 [value] From SerialNumber With (NoLock)
Where [UnitID]=@UnitID and [SerialNumberTypeID] <> 0
OPEN Mac_Cursor
FETCH NEXT FROM Mac_Cursor INTO @value
WHILE (@@Fetch_Status=0)
Begin
set @Hex_output = @Hex_output + ',' + @value
FETCH NEXT FROM Mac_Cursor INTO @value
END
CLOSE Mac_Cursor
Deallocate Mac_Cursor
End
-- If not allocated already then allocate now
ELSE
Begin
Begin Tran
If (@UnitID <> '')
Begin
DECLARE Mac_Cursor1 CURSOR FOR
SELECT Top 11 [Value] FROM AvailableSN
Where [StatusID]=0 Order By Value
OPEN Mac_Cursor1
Set @iCtr = 0
FETCH NEXT FROM Mac_Cursor1 INTO @value
WHILE (@iCtr < @MacNo)
BEGIN
Insert into SerialNumber Values (@UnitID,(@iCtr+1),@value)
set @Hex_output = @Hex_output + ',' + @value
update AvailableSN Set [StatusID]=1 Where [Value]=@value
Set @iCtr = @iCtr + 1
FETCH NEXT FROM Mac_Cursor1 INTO @value
END
CLOSE Mac_Cursor1
DEALLOCATE Mac_Cursor1
End
Commit
End
select @HexOutPut = @Hex_output
Select @HexOutPut
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
********************************************************************************
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc -
Just to be clear, you have a table called available_sn which has a serial number and a status flag.
You also have another table called serialNumber which has a serial number, unitID and a valueNumber.
Your goal is to be able to select the next 11 values from available_sn with status = 0 and insert them into the serialNumber table. The 11 values from available_sn should then have their status set to 1.
Rather than trying to use a cursor you should think set-based and use appropriate locking levels to avoid the same row being retrieved by concurrent proc executions.
For example
BEGIN TRANSACTION
--retrieve the next 11 IDs - hold an exclusive row lock on the rows involved
declare @SNs table( serial varchar(50), ord int IDENTITY(1,1) )
insert into @SNs( serial )
select top 11 serial
from available_sn with (XLOCK, ROWLOCK)
where status = 0
order by serial
--flag the status of those retrieved rows to be 1
update available_sn
set status = 1
from available_sn
inner join @SNs SNs
on available_sn.serial = SNs.serial
--perform the insert on the other table
insert into serialNumber( unitID, serial, serialIdx )
select @UnitID, serial, ord
from @SNs
COMMIT TRANSACTION
You could also use "set TRANSACTION ISOLATION LEVEL SERIALIZABLE" rather than the locking hints.
If you still wish to create your HexOutput parameter, you could iterate through your @SNs temporary table with a cursor or something like the following code..
declare @HexOutput varchar(4000)
set @HexOutput = NULL
select @HexOutput = Coalesce(@HexOutput + ',', '') + Serial
from @SNs
order by ord
Much easier code without the cursor
-
Joe, your answer is certainly correct - although it is a touch brutal. Yes it is annoying to get saddled with applications that do all sorts of very nasty, ugly, etc things in the background (I have to integrate with some healthcare-based ones that you could just as easily critique), but calling the poor fellow's decisions stupid is a bit strong.
I don't want to start some sort of back and forth as you know a lot more than I do about all things DB-related. Additionally, my answer to the post, whilst giving an answer, didn't give "advice" as it probably should/could have. However, I fear that if a newbie sees such a strong response they may not ask questions in future
I *hope* that SSC is a community where there are some who excel in the field who take the time out to help those just starting out, those who perhaps don't yet understand the difference between a simple database, an RDBMS, a file system and a paper phonebook. Your rare answers to questions are always insightful to read - the design you presented is certainly more complete than most would offer (check constraints, providing a table for storage and correctly providing a view for display purposes rather than mingling the two, etc). Hope to hear more from you on the forums - I just wanted Sakthviel to know he's welcome to post some more questions in future if required.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply