locked
EF Core - problem with optimistic concurrency RRS feed

  • Question

  • User1013659773 posted

    Hi,

    I`ve got problem with optimistic concurrency while updating entity in the EF Core. As far as I know I properly added RowVersion to my model and during the UPDATE this property shows in the WHERE clause:

    UPDATE [ServiceItems] SET [CreatedAt] = @p0, [CreatedById] = @p1, [GrossValueAdded] = @p2, [IsArchived] = @p3, [IsBlocked] = @p4, [IsManual] = @p5, [IsSubNamePrinted] = @p6, [IsSuspended] = @p7, [IsValueVariable] = @p8, [Name] = @p9, [NetValue] = @p10, [Notes] = @p11, [Quantity] = @p12, [RemoteSystemServiceCode] = @p13, [ServiceCategoryType] = @p14, [ServiceItemCustomerSpecificTag] = @p15, [SpecificLocation] = @p16, [SubName] = @p17, [UpdatedAt] = @p18, [UpdatedById] = @p19, [VATRate] = @p20, [InstallationDate] = @p21, [IsInvoiced] = @p22, [ServiceItemsSetId] = @p23
    WHERE [Id] = @p24 AND [RowVersion] = @p25;

    However If I`m trying to update the same entity during two simultaneous sessions no exception is thrown.  Then I decided to to mess up with the RowVersion like below and still no excpetion is thrown during UPDATE.

            // PUT api/OneTimeServiceItems/5
            [HttpPut("{id}")]
            public async Task<IActionResult> Put(int id, [FromBody]OneTimeServiceItemDto updatedOneTimeServiceItemDto)
            {
                if (!ModelState.IsValid || updatedOneTimeServiceItemDto.Id != id)
                    return BadRequest();
    
                var oneTimeServiceItems = await _context.ServiceItems.OfType<OneTimeServiceItem>().Where(g => g.Id == id).ToListAsync();
    
                if (!oneTimeServiceItems.Any())
                    return NotFound();
    
                var updatedOneTimeServiceItem = oneTimeServiceItems.First();
    
                Mapper.Map(updatedOneTimeServiceItemDto, updatedOneTimeServiceItem);
    
                updatedOneTimeServiceItem.RowVersion[7]++; // HERE I WANT TO FORCE EF TO THROW EXCEPTION
    
                try
                {
                    _context.ServiceItems.Update(updatedOneTimeServiceItem);
                    await _context.SaveChangesAsync();
                }
                catch (Exception exception)
                {
                    BadRequest(exception);
                }
    
                return new NoContentResult();
            }

    Could someone tell what I`m doing wrong in that code?

    Saturday, August 11, 2018 5:31 AM

All replies

  • User475983607 posted

    Did you add data annotations to the model or configure a timestamp in the fluent API?

    https://docs.microsoft.com/en-us/ef/core/modeling/concurrency

    https://docs.microsoft.com/en-us/ef/core/saving/concurrency

    Saturday, August 11, 2018 1:03 PM
  • User1013659773 posted

    Thats what I`ve got in my model:

            [Timestamp]
            public byte[] RowVersion { get; set; }

    Saturday, August 11, 2018 1:54 PM
  • User753101303 posted

    Hi,

    EF could still use the original value. You need to force that programmatically or you just want to see what happens when needed ?

    My first thought would be to just run a SQL update to create a "true" concurrency error. Here you are trying to "simulate" a concurrency error by changing the client side value but it may or may not have the same effect.

    Edit: missed you were trying to investigate a previous error. You checked on the database side that this column is really defined as a "rowversion" ? Do a SQL UPDATE and see if this column value is updated as well.

    Saturday, August 11, 2018 3:05 PM
  • User1013659773 posted

    As far as I debugged it it looks like everything is working fine (during each UPDATE the RowVersion column is modified and in SQL UPDATE statement the RowVersion condition is added) except in case of modifying the same entity in two simultaneus sessions the second SaveChangesAsync doesn`t throw an excpetion.

    Saturday, August 11, 2018 4:09 PM
  • User753101303 posted

    My first test would be likely to put a breakpoint on await _context.SaveChangesAsync();, do a server side UPDATE there and check the row version changed and then see if SaveChangesAsync throws a concurrency exception.

    The rowversion is part of your DTO ?

    Saturday, August 11, 2018 5:59 PM
  • User1013659773 posted

    I`ve already done that and SaveChangesAsync dosen`t throw an exception.  Yes, RowVersion is part of my DTO. The funny thing is that it should throw a concurrency excpetion every time RowVersion missmatch but it doesn`t.

    Saturday, August 11, 2018 7:42 PM
  • User1120430333 posted

    I`ve already done that and SaveChangesAsync dosen`t throw an exception.  Yes, RowVersion is part of my DTO. The funny thing is that it should throw a concurrency excpetion every time RowVersion missmatch but it doesn`t.

    Well, you should figure out how to use pessimistic concurrency. 

    Monday, August 13, 2018 5:48 AM
  • User-1764593085 posted

    Hi baroo,

    If you have done what PatriceSc suggested, it will throw an concurrency exception. How do you check it?

    To have a test, you could refer below code to add a sql before Update:

                try
                {
                    _context.Database.ExecuteSqlCommand("Update Statement");
    //Modify a value in the table like .ExecuteSqlCommand("Update Customer set Address = 'xxx' where ID = {0}",id)
    _context.ServiceItems.Update(updatedOneTimeServiceItem); await _context.SaveChangesAsync(); } catch (Exception exception) { BadRequest(exception); }

    Best Regards,

    Xing

    Monday, August 13, 2018 3:20 PM
  • User1013659773 posted

    I`ve checked it by setting a breakpoint right here:

                catch (Exception exception)
                {
                    BadRequest(exception); // Breakpoint
                }

    No exception is thrown during test.

    Monday, August 13, 2018 6:26 PM
  • User1013659773 posted

    I`ve removed that line that increments RowVersion and tested one more time what exactly is passed during both update requests as RowVersion value. While in the controller both RowVersion values passed from fronetend are the same (which is OK) then the SQL statements produced by the EF states two different values:

    exec sp_executesql N'SET NOCOUNT ON;
    UPDATE [ServiceItems] SET [CreatedAt] = @p0, [CreatedById] = @p1, [GrossValueAdded] = @p2, [IsArchived] = @p3, [IsBlocked] = @p4, [IsManual] = @p5, [IsSubNamePrinted] = @p6, [IsSuspended] = @p7, [IsValueVariable] = @p8, [Name] = @p9, [NetValue] = @p10, [Notes] = @p11, [Quantity] = @p12, [RemoteSystemServiceCode] = @p13, [ServiceCategoryType] = @p14, [ServiceItemCustomerSpecificTag] = @p15, [SpecificLocation] = @p16, [SubName] = @p17, [UpdatedAt] = @p18, [UpdatedById] = @p19, [VATRate] = @p20, [InstallationDate] = @p21, [IsInvoiced] = @p22, [ServiceItemsSetId] = @p23
    WHERE [Id] = @p24 AND [RowVersion] = @p25;
    SELECT [RowVersion]
    FROM [ServiceItems]
    WHERE @@ROWCOUNT = 1 AND [Id] = @p24;
    
    ',N'@p24 int,@p0 datetime2(7),@p1 nvarchar(4000),@p2 decimal(17,15),@p3 bit,@p4 bit,@p5 bit,@p6 bit,@p7 bit,@p8 bit,@p9 nvarchar(4000),@p10 decimal(2,0),@p11 nvarchar(4000),@p12 int,@p13 nvarchar(4000),@p25 varbinary(8),@p14 int,@p15 nvarchar(4000),@p16 nvarchar(4000),@p17 nvarchar(4000),@p18 datetime2(7),@p19 nvarchar(4000),@p20 decimal(2,0),@p21 datetime2(7),@p22 bit,@p23 int',@p24=4023,@p0='2018-02-05 18:50:25.7062749',@p1=N'84dc9c2f-84a5-4319-861d-3cd93a4e9b57',@p2=60.269999999999996,@p3=0,@p4=0,@p5=0,@p6=1,@p7=0,@p8=0,@p9=N'Internet %DETALE% - instalacjaoi',@p10=49,@p11=N'',@p12=1,@p13=N'1201',@p25=0x000000000008F112,@p14=0,@p15=N'',@p16=N'al. Piłsudskiego 190, 35-001 Rzeszów',@p17=N'100/20 Mb/s',@p18='2018-08-13 20:29:55.3436177',@p19=N'84dc9c2f-84a5-4319-861d-3cd93a4e9b57',@p20=23,@p21='2018-02-05 00:00:00',@p22=1,@p23=1
    exec sp_executesql N'SET NOCOUNT ON;
    UPDATE [ServiceItems] SET [CreatedAt] = @p0, [CreatedById] = @p1, [GrossValueAdded] = @p2, [IsArchived] = @p3, [IsBlocked] = @p4, [IsManual] = @p5, [IsSubNamePrinted] = @p6, [IsSuspended] = @p7, [IsValueVariable] = @p8, [Name] = @p9, [NetValue] = @p10, [Notes] = @p11, [Quantity] = @p12, [RemoteSystemServiceCode] = @p13, [ServiceCategoryType] = @p14, [ServiceItemCustomerSpecificTag] = @p15, [SpecificLocation] = @p16, [SubName] = @p17, [UpdatedAt] = @p18, [UpdatedById] = @p19, [VATRate] = @p20, [InstallationDate] = @p21, [IsInvoiced] = @p22, [ServiceItemsSetId] = @p23
    WHERE [Id] = @p24 AND [RowVersion] = @p25;
    SELECT [RowVersion]
    FROM [ServiceItems]
    WHERE @@ROWCOUNT = 1 AND [Id] = @p24;
    
    ',N'@p24 int,@p0 datetime2(7),@p1 nvarchar(4000),@p2 decimal(17,15),@p3 bit,@p4 bit,@p5 bit,@p6 bit,@p7 bit,@p8 bit,@p9 nvarchar(4000),@p10 decimal(2,0),@p11 nvarchar(4000),@p12 int,@p13 nvarchar(4000),@p25 varbinary(8),@p14 int,@p15 nvarchar(4000),@p16 nvarchar(4000),@p17 nvarchar(4000),@p18 datetime2(7),@p19 nvarchar(4000),@p20 decimal(2,0),@p21 datetime2(7),@p22 bit,@p23 int',@p24=4023,@p0='2018-02-05 18:50:25.7062749',@p1=N'84dc9c2f-84a5-4319-861d-3cd93a4e9b57',@p2=60.269999999999996,@p3=0,@p4=0,@p5=0,@p6=1,@p7=0,@p8=0,@p9=N'Internet %DETALE% - instalacjaot',@p10=49,@p11=N'',@p12=1,@p13=N'1201',@p25=0x00000000000900B1,@p14=0,@p15=N'',@p16=N'al. Piłsudskiego 190, 35-001 Rzeszów',@p17=N'100/20 Mb/s',@p18='2018-08-13 20:30:05.1322026',@p19=N'84dc9c2f-84a5-4319-861d-3cd93a4e9b57',@p20=23,@p21='2018-02-05 00:00:00',@p22=1,@p23=1

    I assume this is the problem. However I still don`t know why this is happening.

    Monday, August 13, 2018 6:37 PM
  • User-1764593085 posted

    Hi baroo,

    I’m wondering how could you ensure the SQLs you provided above can be executed in a  parallel way? I mean  , when the two incoming requests manipulate the same entity, let’s say aRequest and bRequest , the ef actually does :

    1.       Task 1:

                a.       aRequest Reads the entity  (t1-1)

                b.       update the entity (t1-2)</div> <div>           

                c.       save the entity via sql on Database Server (t1-3)

    2.       Task 2:

                a.       bRequest Reads the entity (t2-1)

                b.       update the entity (t2-2)

                c.       save the entity via sql on Database Server(t2-3)

    If  the sequence of execution is: t1-1 , t1-2 , t1-3 , t2-1 , t2-2 ,t2-3 ,  there will be no exceptions at all .

    So , could you show us how do you ensure the execution sequence of the tasks above?

    Best Regards,

    Xing

    Tuesday, August 14, 2018 2:57 AM
  • User1013659773 posted

    Using two browsers I make it like this t1-1, t2-1, t1-2 , t1-3 , t2-2 ,t2-3

    Tuesday, August 14, 2018 5:04 AM
  • User-1764593085 posted

    Hi baroo,

    Did you forget to add a RowVersion filed in your View File ?

    <form asp-action="Put">
               
        <input type="hidden" asp-for="RowVersion" />
        ...
    </form>

    Then change you action parameters:

     public async Task<IActionResult> Put(int id, byte[] rowVersion,[FromBody]OneTimeServiceItemDto updatedOneTimeServiceItemDto)

    You could refer below link to create a demo to test concurrency in two browsers:

    https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/concurrency?view=aspnetcore-2.0#test-concurrency-conflicts-in-the-edit-page

    Best Regards,

    Xing

    Tuesday, August 14, 2018 7:26 AM
  • User1013659773 posted

    As I mentioned before the RowVersion value in the controller seems to be OK (it reflects RowVersion value from loading edit view time). In my case it is passed back and forth to Angualr2 app using DTO - updatedOneTimeServiceItemDto.

    Tuesday, August 14, 2018 3:51 PM
  • User-1764593085 posted

    Hi,

    The value of the concurrency token on the database is compared against the original value read by EF Core not current value.

    Current values are the values that the application was attempting to write to the database.

    Original values are the values that were originally retrieved from the database, before any edits were made.

    https://docs.microsoft.com/en-us/ef/core/saving/concurrency#how-concurrency-control-works-in-ef-core

     updatedOneTimeServiceItem.RowVersion[7]++; 

    When you get the RowVersion in Entity and do ++ operation, it changes the RowVersion in Entity not Original vlaue.

    However, when you save changes to database, it compares the original value which does not have been modified.

    No matter how many times you execute ++ opertions, it will not appear concurrency confilct.

    Best Regards,

    Xing

    Wednesday, August 15, 2018 6:40 AM
  • User1013659773 posted

    Ok, but I still don`t know what is the proper way to pass that orignal value of the RowVersion I previously get before editing to the EF Core in the controller in order to maintain concurrency checks. Could you please show me how to do that int the following code?

            // PUT api/OneTimeServiceItems/5
            [HttpPut("{id}")]
            public async Task<IActionResult> Put(int id, [FromBody]OneTimeServiceItemDto updatedOneTimeServiceItemDto)
            {
                if (!ModelState.IsValid || updatedOneTimeServiceItemDto.Id != id)
                    return BadRequest();
    
                var oneTimeServiceItems = await _context.ServiceItems.OfType<OneTimeServiceItem>().Where(g => g.Id == id).ToListAsync();
    
                if (!oneTimeServiceItems.Any())
                    return NotFound();
    
                var updatedOneTimeServiceItem = oneTimeServiceItems.First();
    
                Mapper.Map(updatedOneTimeServiceItemDto, updatedOneTimeServiceItem);
    
                try
                {
                    _context.ServiceItems.Update(updatedOneTimeServiceItem);
                    await _context.SaveChangesAsync();
                }
                catch(DbUpdateConcurrencyException exception)
                {
                    BadRequest(exception);
                }
                catch (Exception exception)
                {
                    BadRequest(exception);
                }
    
                return new NoContentResult();
            }

    Wednesday, August 15, 2018 9:00 AM
  • User-1764593085 posted

    Hi baroo,

    You could add below code to check concurrency conflict:

    try
    {
        var originalVlaue = _context.Entry(updatedOneTimeServiceItem).Property("RowVersion").OriginalValue;
    
        updatedOneTimeServiceItem.RowVersion[7]++;
    
        originalVlaue = updatedOneTimeServiceItem.RowVersion;                    
    
        _context.ServiceItems.Update(updatedOneTimeServiceItem);
        await _context.SaveChangesAsync();
    }

    You could refer below link to learn about PropertyEntry:

    https://docs.microsoft.com/en-us/ef/core/api/microsoft.entityframeworkcore.changetracking.propertyentry#properties

    Best Regards,

    Xing

    Thursday, August 16, 2018 2:10 AM
  • User1013659773 posted

    Now It works as I removed RowVersion mapping for Mapper.Map(updatedOneTimeServiceItemDto, updatedOneTimeServiceItem). I mean it throws excpetion while incrementing RowVersion[7]. However I still don`t know where to put the RowVerion I preserved in my DTO to make it works in real concurrency scenarios.

    Friday, August 17, 2018 4:22 PM