1

I have a system in production that I need to retrofit with concurrency checking due to increased use and some issues that have arisen.

Currently I'm using stored procedures that follow a basic upsert pattern:

Update X where key = @key 
if @@rowcount = 0
Insert into...

Into that mix I need to check for row changes. I plan on using a Rowversion/Timestamp.

I don't want to just add:

Update ...  Where key = @key AND Row_Version = @Row_Version

I want to fire a SQL error or something that can be caught by C# so that I can communicate the concurrency issue to the user, rather than just do nothing or a more "generic" error.

So, I think I'm going to have to ditch my Upsert and do things as follows:

-- Update Check
if exists (select @Current_Row_Version from table where key = @key)
    if @Current_Row_Version != @Row_Version 

        RAISEERROR('Concurrency Violation - TableName', 11,1);            
        Return
    else
        Update * set...  Where Key = @key

else
    -- Insert
    insert ...

So, does that look like I'm the right track? Are there better patterns or practices?

Found some interesting examples such as here but nothing that quite fits my need. I could try the insert first as here but I think I'm going to be doing more updating than inserting.

Merge will not work for me either.

This Update/Insert will be part of a transaction as well... if any part fails, I need to roll everything back. The rollback is handled in C# code based on errors caught.

pStan
  • 1,084
  • 3
  • 14
  • 36
  • Do you want to make sure that only one person is able to affect the row in some timeframe? What do you expect to happen in the end? If I try to change the row and someone else beats me to it, I get an error for how long? If I try again in 20 seconds and succeed, what has been accomplished? – Aaron Bertrand Feb 13 '18 at 19:39
  • If another user has updated the row, then the row id has changed.... which means the 2nd user has "cold" data. Retry will not work in this case, because this transaction involves multi part header and detail data. The user will need to cancel out and load fresh data. Users are pulling data to edit, then letting it sit... coming back to it some time later and choosing to save and close without thinking... SO... I don't mind putting up an error message and making them reload the screen. – pStan Feb 13 '18 at 20:03
  • Have you used SQL Server's MERGE before? What isolation level are you running at? – mjwills Feb 13 '18 at 20:24

1 Answers1

3

To ensure the that the row doesn't change between your if and your update, you could specify the locking hints updlock and holdlock:

begin transaction;
if exists (select @Current_Row_Version from table with (updlock, holdlock) where key=@key)
    begin
    if @Current_Row_Version != @Row_Version 
        begin
        rollback transaction;
        raiserror ('Concurrency Violation - TableName', 11,1);            
        return
        end
    else
        update * set...  Where Key = @key
    end
commit transaction;

Note that raiserror is written with one E.

A more common way to do optimistic locking is :

update   ...
set      ...
where    row_version = @target_row_version

And then check the number of updated rows client side. If it's 0, your optimistic update has failed.

For more information on SQL Server's concurrency support read the MSDN pages for set transaction isolation level.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • If I do code as in the 2nd block, I'm using @@rowcount to check for No record (need to insert) or changed record. I still have to figure out which one. ... I can't assume concurrency violation yet... I just may need to do an Insert. Right, or am I missing something. – pStan Feb 13 '18 at 20:10
  • 1
    I think you're missing something. Some time will pass between the `update` and the `insert`. In that time, another connection can do an `insert`. – Andomar Feb 13 '18 at 20:16