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

1 vote
0 answers
995 views
                          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.
                        
Asked by VansFannel (1873 rep)
Nov 24, 2016, 11:14 AM
Last activity: Nov 25, 2016, 03:13 PM
why an update doesn't update a row if the where clause exists?

Related Questions
