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]

    &nbsp  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