Update to a table but get the record deleted.
-
I have a table A and two store procedures that may be possible to update the same record in table A at the same time.
I found sometimes the record is deleted instead of updated. There is no difference whether I add [with rowlock] to the update line or not.
Can anyone give me a hint on this? Can a transaction help to resolve the issue? But I am worry about the performance of the store procedure if add the transaction.
Thanks.
coby
-
How do you mean the row gets deleted? An UPDATE can never delete a row entirely. Sometimes an update is split into a delete/insert pair of operations, but that is on a much lower level, and in any case the row would still exist afterwards.
-
Thanks for the reply. I am curious but it did happen. I am using sql2000.
-
Could you post the DDL of your table and the procedure you are executing?
-
The table is:
CREATE TABLE [dbo].[SUTStatus] (
[BarCode] [varchar] (10) COLLATE Latin1_General_BIN NOT NULL ,
[TimeStamp] [datetime] NOT NULL ,
[StageCode] [int] NOT NULL ,
[PhaseID] [int] NOT NULL ,
[MacAddress] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL ,
[NWAddress] [varchar] (8) COLLATE Latin1_General_BIN NOT NULL ,
[WCID] [int] NOT NULL ,
[StepID] [int] NOT NULL ,
[ErrText] [varchar] (128) COLLATE Latin1_General_BIN NOT NULL ,
[RsltCode] [int] NOT NULL ,
[LoopCount] [int] NOT NULL ,
[DBRcdTime] [datetime] NULL ,
[ComputerID] [int] NULL ,
[PartNum] [varchar] (10) COLLATE Latin1_General_BIN NULL ,
[FamilyID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SUTStatus] WITH NOCHECK ADD
CONSTRAINT [PK_SUTStatus] PRIMARY KEY CLUSTERED
(
[BarCode]
 
ON [PRIMARY]GO
The store procedure 1 calls below lines:
if exists ( select BarCode from SUTStatus where BarCode = @BarCode )
begin
update SUTStatus set [TimeStamp] = @TimeStamp, StageCode = @StageCode, PhaseID = @PhaseID, MacAddress = @MacAddress,
NWAddress = @NWAddress, WCID = @WCID, StepID = @StepID, RsltCode = @RsltCode, LoopCount = @LoopCount, ErrText = @ErrTxt, DBRcdTime = @DBRcdTime, ComputerID = @ComputerID,
PartNum = @PartNum, FamilyID = @FamilyID
where BarCode = @BarCode
end
else
begin
if exists ( select barcode from wipedown where barcode = @BarCode )
begin
return;
end
insert into SUTStatus (BarCode, [TimeStamp], StageCode, PhaseID, MacAddress, NWAddress,
WCID, StepID, ErrText, RsltCode, LoopCount,DBRcdTime, ComputerID, PartNum, FamilyID)
values(@BarCode, @TimeStamp, @StageCode, @PhaseID, @MacAddress,@NWAddress,
@WCID, @StepID, @ErrTxt, @RsltCode, @LoopCount,@DBRcdTime, @ComputerID, @PartNum, @FamilyID)
end
Store procedure 2 calls below lines:
update SUTStatus set WCID = @WCID0 where MacAddress = @MacAddress.
Thanks.
coby
-
Nothing special there as far as I can see. Perhaps there are triggers that deletes the row?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply