why an update doesn't update a row if the where clause exists?

Question

I'm working with SQL Server 2012 SP2 and I have a problem with a stored procedure.

I have a table where we do a lot of things in a second: we insert and update rows very often.

I have a stored procedure to update a row, and it works fine most of the time, but sometimes it doesn't update a row but @@ERROR is zero. This is the update statement (@isAuto is another parameter):

-- Set new Commisioning Flag value depending on is an auto or manual read.
if (@isAuto = 1)
    set @newCommFlagValue = 20  -- Commissioning Auto
else 
    set @newCommFlagValue = 120 -- Manual Commissioning.

[ ... ]

-- Get Code PK to do fast queries. @code is a stored procedure parameter
-- Serial has an unique constraint
set @codeId = (select CodeId from Code where Serial = @code);

-- Get current flag.
SET @currentCommFlag = (SELECT CommissioningFlag
                            FROM Code
                            WHERE CodeId = @codeId);

if (@currentCommFlag is null)
begin
    if (@@TRANCOUNT > 0)
        rollback transaction

    SELECT @message = 'ReadCode ' + @code + ' return -17';      
    EXEC master..xp_logevent 60000, @message, informational;

    return -17 -- @code doesn't exist on Code table.
end

[ ... ]

begin try
    if (@helperCodeId is not null)
        UPDATE Code
            SET CommissioningFlag = @newCommFlagValue
                        , Source = @source
                        , UserName = @username
                        , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                        , SentToNextLevel = 0
                        , HelperCodeId = @helperCodeId
        WHERE CodeId = @codeId
    else
        UPDATE Code
            SET CommissioningFlag = @newCommFlagValue
                        , Source = @source
                        , UserName = @username
                        , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                        , SentToNextLevel = 0
        WHERE CodeId = @codeId

    SELECT @ErrorVar = @@ERROR  
            , @RowCountVar = @@ROWCOUNT; 
end try
begin catch
    SELECT @message = 'ReadCode ' + @code + ' error ' + ERROR_NUMBER() + ' - ' + ERROR_MESSAGE();
    EXEC master..xp_logevent 60000, @message, informational;
end catch
-- If it hasn't been updated...
IF @ErrorVar != 0
BEGIN

    SELECT @message = 'ReadCode ' + @code + ' error update Code';
    EXEC master..xp_logevent 60000, @message, informational;

    SET @code = NULL
    if (@@TRANCOUNT > 0)
        rollback transaction
    RETURN -1
END
else
begin   
    if (@@TRANCOUNT > 0)
        commit transaction;

    if @RowCountVar = 0
    begin
        SELECT @message = 'ReadCode ' + @code + ' - no actualizado';
        EXEC master..xp_logevent 60000, @message, informational;

        return -1;
    end
    else
    begin
        set @commFlag = (Select CommissioningFlag from Code where CodeId = @codeId);

        SELECT @message = 'ReadCode ' + @code + ' - flag ' + CAST(@commFlag as varchar(2)) + ' - return 0';
        EXEC master..xp_logevent 60000, @message, informational;

        return 0;
    end
end

There isn't any transaction on the stored procedure. Maybe the table is locked by an insert or another process.

After stored procedure execution I do a select for that row and it has the previous value on CommissioningFlag column.

I'm sure this stored procedure works because it updates nearly 98% of the rows. But there is a 2% that it isn't updated.

XACT_ABORT is not set.

Do you why an Update doesn't update a row but it doesn't return an Error?

I've been investigating what it is happening and I have seen something very interesting:

Row number 5 hasn't been updated. This is the error. My program first update row 1, then row 2, etc. Each update triggers 0.15s. Update between row 4 and row 6 gets 0.30s (more or less). It seems that row 5 is updated in 0.15s without any delay but it isn't stored the change in database.

why an update doesn't update a row if the where clause exists?

0 Answers0