locked
How to do Concurrency Checking using RowVersion RRS feed

  • Question

  • User1493428334 posted

    I am doing concurrency checking by adding a RowVersion column in the DB. 

    I thought that when the RowVersion of the record I am saving to the DB does not match the RowVersion of the same record currently in the DB, the DB will automatically reject the Update operation, thus enforcing the concurrency checking. But the DB does not reject the Update.

    So my question is, how do I do concurrency checking using RowVersion?

    I am using SQL DB with Enterprise framework.

    Thanks,

    Golden

    Friday, October 25, 2019 1:39 AM

Answers

  • User765422875 posted

    I'm not sure you are executing the test correctly.

    As documented: "You can use the rowversion column of a row to easily determine whether the row has had an update statement ran against it since the last time it was read. If an update statement is ran against the row, the rowversion value is updated. If no update statements are ran against the row, the rowversion value is the same as when it was previously read. "

    Simulated Test Code from the Microsoft Docs:

    For example, assume that you create a table named MyTest. You populate some data in the table by running the following Transact-SQL statements

    CREATE TABLE MyTest (myKey int PRIMARY KEY  
        ,myValue int, RV rowversion);  
    GO   
    INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);  
    GO   
    INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);  
    GO  

    You can then use the following sample Transact-SQL statements to implement optimistic concurrency control on the MyTest table during the update.

    DECLARE @t TABLE (myKey int);  
    UPDATE MyTest  
    SET myValue = 2  
        OUTPUT inserted.myKey INTO @t(myKey)   
    WHERE myKey = 1   
        AND RV = myRv;  
    IF (SELECT COUNT(*) FROM @t) = 0  
        BEGIN  
            RAISERROR ('error changing row with myKey = %d'  
                ,16 -- Severity.  
                ,1 -- State   
                ,1) -- myKey that was changed   
        END;  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 28, 2019 1:42 PM
  • User-17257777 posted

    Hi GoldenMicrosoft,

    Optimistic concurrency means allowing concurrency conflicts to happen, and then reacting appropriately if they do. The demo in the link checks the rowversion to determine whether a concurrency conflict happens. If it does, an expection will raise, then the rowversion will be updated, you can customize the error message to the user to inform him that a conflict happens. If the user continues (edit or delete..), since the rowversion was updated and can be find in the database, then the operation will be executed.

    Best Regards,

    Jiadong Meng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 30, 2019 9:40 AM
  • User753101303 posted

    And the rowversion column shows up fine in your EDMX ?

    EF should use this information to generate the where clause (you'll need also to keep the value around, not sure if testing from a UI or programmatically). If all is fine it should use  a "WHERE pk=@pk AND rowversion=@versionWhenReading" and so if someone changed the db in between it doesn't match any more and it is handled as a conccurrency issue.

    Start maybe by tracing the SQL statements send to the server? Or do the simplest change you can (for example a console app with a keypress in between).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 30, 2019 9:54 AM

All replies

  • User753101303 posted

    Hi,

    You mean Entity Framework? You tried https://www.entityframeworktutorial.net/code-first/TimeStamp-dataannotations-attribute-in-code-first.aspx

    Or are you really using the old "Enterprise Application Library" ?

    Friday, October 25, 2019 4:01 PM
  • User1493428334 posted

    Hi Patrice,

    Sorry to reply to you late.

    By Entity Framework I meant by creating *****.edmx in my Visual Studio, and it will automatically create the classes based on the tables definition in the DB.

    The Entity Framework itself works fine. 

    But when I artificially change the value of RowVersion for testing purpose, e.g., change RowVersion[7] from 6 to 3 in my copy of the record, when I save the record on the DB, it does not reject it.

    I am expecting it to be rejected because by changing the RowVersion, Concurrency Checking should fail.

    Thanks,

    Golden

    Saturday, October 26, 2019 3:02 AM
  • User765422875 posted

    To simulate this, there <g class="gr_ gr_84 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="84" data-gr-id="84">has</g> to be two users executing the code on the same entity. User1 modifies the entity and saves it before User2. So, User1 successfully saves, whereas User2 will get "Concurrency Exception Occurred."

    Concurrency in Entity Framework

    Sunday, October 27, 2019 3:45 PM
  • User1493428334 posted

    I simulate it in another way: in the software, before saving the record on the DB, artificially change the RowVersion (so that it is different from the DB one).

    I was expecting the update be rejected, but no, the update was executed successfully by the DB. Thus it proves the DB does not check the RowVersion for concurrency.

    Thanks,

    Golden

    Monday, October 28, 2019 3:10 AM
  • User-17257777 posted

    Hi GoldenMicrosoft,

    I suggest you use Optimistic concurrency, it assumes the likelihood of a conflict is low, allowing concurrent access to data while minimizing blocking and deadlocks.

    What you said is Pessimistic Concurrency. Managing locks has disadvantages. It can be complex to program. It requires significant database management resources, and it can cause performance problems as the number of users of an application increases. For these reasons, not all database management systems support pessimistic concurrency. 

    For more details, refer to

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application

    Best Regards,

    Jiadong Meng

    Monday, October 28, 2019 10:42 AM
  • User765422875 posted

    I'm not sure you are executing the test correctly.

    As documented: "You can use the rowversion column of a row to easily determine whether the row has had an update statement ran against it since the last time it was read. If an update statement is ran against the row, the rowversion value is updated. If no update statements are ran against the row, the rowversion value is the same as when it was previously read. "

    Simulated Test Code from the Microsoft Docs:

    For example, assume that you create a table named MyTest. You populate some data in the table by running the following Transact-SQL statements

    CREATE TABLE MyTest (myKey int PRIMARY KEY  
        ,myValue int, RV rowversion);  
    GO   
    INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);  
    GO   
    INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);  
    GO  

    You can then use the following sample Transact-SQL statements to implement optimistic concurrency control on the MyTest table during the update.

    DECLARE @t TABLE (myKey int);  
    UPDATE MyTest  
    SET myValue = 2  
        OUTPUT inserted.myKey INTO @t(myKey)   
    WHERE myKey = 1   
        AND RV = myRv;  
    IF (SELECT COUNT(*) FROM @t) = 0  
        BEGIN  
            RAISERROR ('error changing row with myKey = %d'  
                ,16 -- Severity.  
                ,1 -- State   
                ,1) -- myKey that was changed   
        END;  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 28, 2019 1:42 PM
  • User1493428334 posted

    Hi Jiadong,

    Thanks for your explanation.

    I need to use Pessimistic Concurrency since the data I am updating is very critical.

    One question: does Optimistic Coccurancy provide any concurrancy checking at all? If yes, what kind of checkings does it do?

    Golden

    Tuesday, October 29, 2019 5:33 PM
  • User1493428334 posted

    Hi Deepalgorithm,

    Thanks for your detailed explanation.

    Based on your input, to do concurrency checking I need to provide code to check it, either in the SQL or ASP.NET?

    Golden

    Tuesday, October 29, 2019 5:43 PM
  • User-17257777 posted

    Hi GoldenMicrosoft,

    Optimistic concurrency means allowing concurrency conflicts to happen, and then reacting appropriately if they do. The demo in the link checks the rowversion to determine whether a concurrency conflict happens. If it does, an expection will raise, then the rowversion will be updated, you can customize the error message to the user to inform him that a conflict happens. If the user continues (edit or delete..), since the rowversion was updated and can be find in the database, then the operation will be executed.

    Best Regards,

    Jiadong Meng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 30, 2019 9:40 AM
  • User753101303 posted

    And the rowversion column shows up fine in your EDMX ?

    EF should use this information to generate the where clause (you'll need also to keep the value around, not sure if testing from a UI or programmatically). If all is fine it should use  a "WHERE pk=@pk AND rowversion=@versionWhenReading" and so if someone changed the db in between it doesn't match any more and it is handled as a conccurrency issue.

    Start maybe by tracing the SQL statements send to the server? Or do the simplest change you can (for example a console app with a keypress in between).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 30, 2019 9:54 AM
  • User1493428334 posted

    Hi Jiadong,

    Thanks for the explanation.

    Based on your explanation, I think I am using Optimistic concurrency. I use System.Threading.Monitor for the locking mechanism in my code-behind when checking the RowVersion.

    I need to ask you one more question, because its answer will affect the conflict resolution when it happens:

    When updating a record in the DB, we need a copy of the record to which we apply the changes, before saving the record in the DB. My question is, when the copy of the record should be made: (1) at the time when the user presses the Edit button, or (2) at the time when the user presses the Update button?

    The advantage of (1) is that the user sees the base copy, but with more conflicts to happen due to the duration of time between Edit and Update.

    The advantage of (2) is the opposite of the above.

    If we use (2), then when conflict happens as you explain above, my code can just update the base copy (by re-querying the DB) and re-update it.

    Thanks again,

    Golden

    Friday, November 1, 2019 12:10 AM
  • User1493428334 posted

    Hi Patrice,

    Thanks for  your reply.

    RowVersion shows up fine in my EDMX everyday. However, based on the conversation of this thread, I understand that the DB will only increment the RowVersion upon updating but does not do RowVersion checking by itself unless it is instructed in the DB Update command.

    But with EF, I do not do DB command anymore. I use Repository:

    In the code-behind (*.aspx.cs) update method, I do the following:

    Repository myRepo = new Repository();
    Product myProduct = myRepo.Products.Where(p => p.ProductID == productID).FirstOrDefault();

    Then I apply the changes to myProduct (my base copy) above.

    Then I save (update) the changes in the DB by: 

    myRepo.SaveProduct(myProduct);

    Below is the complete snippet of the related Repository code:

    public class Repository {

    private MyDBEntities context = new MyDBEntities();  // MyDBEntities be the connectionString to the EF

    public IEnumerable<Product> Products {
    get { return context.Products; }
    }

    public void SaveProduct(Product product) {
           if (product.ProductID == 0) {
                  //product = context.Products.Add(product);
                 context.Products.Add(product);
            }   // Update case do nothing since the caller of this method (see above) accesses the context thru the Repository instance
            context.SaveChanges();
    }

        }

    In the above, anyway I can instruct the DB to do concurrency checking?

    (BTW, don't know why when I inserted the above code here (by cut and paste) it bracketed them with so many unrelated HTML elements and showed them here until it was not readable. I had to manually type it)

    Thanks in advance,

    Golden

    Friday, November 1, 2019 1:11 AM
  • User-17257777 posted

    Hi GoldenMicrosoft,

    I am a little confused, Is there any difference between "Edit" and "Update". And it is not a copy of record, just the rowversion is updated when user click the button which will update the database. I suggest you go through the demo I think will be easier to understand than I said.

    Best Regards,

    Jiadong Meng

    Friday, November 1, 2019 9:44 AM
  • User1493428334 posted

    Hi Jiadong,

    Yes, I will study the demo.

    BTW, roughly speaking, Edit means bringing up a screen which the user can change the values; Update means applying the values to the stored record.

    Thanks again,

    Golden

    Saturday, November 2, 2019 2:19 AM