Consider a stored procedure which implements a Compare-and-Swap (CAS) operation for a Key-Value mapping table.

When calling the procedure you specify three things:

• A Key, which identifies a row in the table;
• The new Value that you would like to associate with that Key; and
• What you think the current Value associated with that Key is.

The procedure will swap in your new Value for that Key, but only if the current Value is still what you thought it was.

This allows you to read the value (e.g. 42), transform it in some way (e.g. increment it to 43), then store the new value, making sure that nobody else has changed the value while you were busy transforming it.

For example, if someone else changed the value to 123 while you were incrementing to 43, it would not be valid for you to store 43 as the incremented value because then you would be overwriting the change to 123, as if it never happened. What you would need to do is start over again, with 123, increment it to 124, and store that (so long as nobody has changed the 123 to yet another value in the mean time).

Here’s a naïve stored procedure that implements this logic:

-- KeyValuePairs table definition
create table dbo.KeyValuePairs (
    [Key] nvarchar(50) not null primary key,
    Value int not null)
GO

/* Procedure up_CompareAndSwap

 Compares the value stored for p_key in the KeyValuePairs table
 against the p_comparand value.
   * If they match, the new p_value is stored in KeyValuePairs.
   * If they do not match, KeyValuePairs is not updated.

 Returns: 0 if KeyValuePairs was updated;
          1 if KeyValuePairs was not updated;
          2 if the specified p_key was not found.
*/
create procedure up_CompareAndSwap 
    @p_key nvarchar(50),
    @p_value int,
    @p_comparand int
as
begin
    begin transaction

    -- Read current value
    declare @currentValue int
    select @currentValue = Value
    from KeyValuePairs
    where [Key] = @p_key
    
    if (@@ROWCOUNT != 1)
    begin
        raiserror('Key <%s> was not found.', 16, 0, @p_key)
        commit transaction
        return 2
    end

    -- Compare to the expected value (and return if they don't match)
    if (@currentValue != @p_comparand)
    begin
        print 'Comparand does not match. Value was not updated.'
        commit transaction
        return 1
    end

    -- Update to the new value
    update KeyValuePairs
    set [Value] = @p_value
    where [Key] = @p_key

    print 'Value was updated successfully.'
    commit transaction
    return 0
end
GO

So, what’s wrong with this stored procedure?

Specifically, what shortfall does it have when comparing it to the characteristics of CAS operations such as .NET’s Interlocked.CompareExchange()?

Read on in Compare-and-Swap in SQL: Part 2.