Db2 (On Premises and Cloud)

Expand all | Collapse all

DbUpdateConcurrencyException when using SaveChangesAsync in .NET Core 3.1 EF Core with latest drivers

  • 1.  DbUpdateConcurrencyException when using SaveChangesAsync in .NET Core 3.1 EF Core with latest drivers

    Posted Mon August 03, 2020 06:29 AM
    Edited by Lars Mon August 03, 2020 07:24 AM
    Hi,

    using 
    .NET Core 3.1
    Microsoft.EntityFrameworkCore 3.1.0
    IBM.EntityFrameworkCore-osx 3.1.0.200

    If I create a DbContext and use Add/SaveChanges, everything works and the new row is added to the table as expected. If I use AddAsync and SaveChangesAsync, I get a DbUpdateConcurrencyException, and no row is added to the table. 

    (This code uses a table from IdentityServer4, but this also applies to ASP.NET Identity etc.)

    Unhandled exception. System.AggregateException: One or more errors occurred. (Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.)
     ---> Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
       at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
       at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithoutPropagationAsync(Int32 commandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
       at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
       at eftest.Program.Perform() in /Users/xxx/projects/eftest/Program.cs:line 35

    DDL for table:

    create table PERSISTEDGRANTS
    (
    	KEY VARCHAR(200) not null
    		constraint PK_PERSISTEDGRANTS
    			primary key,
    	TYPE VARCHAR(50) not null,
    	SUBJECTID VARCHAR(200),
    	SESSIONID VARCHAR(100),
    	CLIENTID VARCHAR(200) not null,
    	DESCRIPTION VARCHAR(200),
    	CREATIONTIME TIMESTAMP(6) not null,
    	EXPIRATION TIMESTAMP(6),
    	CONSUMEDTIME TIMESTAMP(6),
    	DATA VARCHAR(32672) not null
    );
    
    create index IX_PERSISTEDGRANTS_EXPIRATION
    	on PERSISTEDGRANTS (EXPIRATION);
    
    create index IX_PERSISTEDGRANTS_SUBJECTID_CLIENTID_TYPE
    	on PERSISTEDGRANTS (SUBJECTID, CLIENTID, TYPE);
    
    create index IX_PERSISTEDGRANTS_SUBJECTID_SESSIONID_TYPE
    	on PERSISTEDGRANTS (SUBJECTID, SESSIONID, TYPE);
    


    My DbContext:

    using System;
    using IBM.EntityFrameworkCore;
    using IBM.EntityFrameworkCore.Storage.Internal;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Logging;
    
    namespace eftest
    {
        public class PersistedGrantDbContext : DbContext
        {
            public DbSet<PersistedGrant> PersistedGrants { get; set; }
          
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                var connectionString = "REDACTED";
    
                optionsBuilder.UseDb2(connectionString, db2Builder =>
                {
                    db2Builder.SetServerInfo(IBMDBServerType.LUW, IBMDBServerVersion.LUW_11_01_2020);
                    db2Builder.UseRowNumberForPaging();
                    db2Builder.MaxBatchSize(1);
    
                });
            }
            
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
    
                modelBuilder.Entity<PersistedGrant>(grant =>
                {
                    grant.ToTable("PERSISTEDGRANTS");
                    grant.Property(x => x.Key).HasMaxLength(200).ValueGeneratedNever();
                    grant.Property(x => x.Type).HasMaxLength(50).IsRequired();
                    grant.Property(x => x.SubjectId).HasMaxLength(200);
                    grant.Property(x => x.SessionId).HasMaxLength(100);
                    grant.Property(x => x.ClientId).HasMaxLength(200).IsRequired();
                    grant.Property(x => x.Description).HasMaxLength(200);
                    grant.Property(x => x.CreationTime).IsRequired();
                    // 50000 chosen to be explicit to allow enough size to avoid truncation, yet stay beneath the MySql row size limit of ~65K
                    // apparently anything over 4K converts to nvarchar(max) on SqlServer
                    grant.Property(x => x.Data).HasMaxLength(50000).IsRequired();
                    grant.HasKey(x => x.Key);
                    grant.HasIndex(x => new {x.SubjectId, x.ClientId, x.Type});
                    grant.HasIndex(x => new {x.SubjectId, x.SessionId, x.Type});
                    grant.HasIndex(x => x.Expiration);
                   
                });
                
               
            }
        }
    
        public class PersistedGrant
        {
            public string Key { get; set; }
            public string Type { get; set; }
            public string SubjectId { get; set; }
            public string SessionId { get; set; }
            public string ClientId { get; set; }
            public string Description { get; set; }
            public DateTime CreationTime { get; set; }
            public DateTime? Expiration { get; set; }
            public DateTime? ConsumedTime { get; set; }
            public string Data { get; set; }
      
         }
    
     
    }
    
    
    


    My Program.cs:

    using System;
    using System.Threading.Tasks;
    
    namespace eftest
    {
        class Program
        {
            static void Main(string[] args)
            {
                Console.WriteLine("Hello World!");
                Perform().Wait();
            }
    
    
            private static async Task Perform()
            {
                using (var context = new PersistedGrantDbContext())
                {
    
                    var std = new PersistedGrant()
                    {
                        Data = "data",
                        Description = "descr",
                        Expiration = DateTime.Today,
                        Key = "Key",
                        Type = "type",
                        ClientId = "clientid",
                        ConsumedTime = DateTime.Today.AddDays(1),
                        CreationTime = DateTime.Now,
                        SessionId = "sesionid",
                        SubjectId = "subjectid"
                    };
    
                    await context.PersistedGrants.AddAsync(std);
                    await context.SaveChangesAsync(); //SaveChanges works, but not Async
                    
                }
            }
            
          
        }
    }
    

    Why is this?

    If I add a dummy column like this to the original table above:

    dummy timestamp not null generated always for each row on update as row change timestamp;

    and then specify it is the row version in the DbContext, it works with both async and sync. But this makes it hard/impossible to use in a lot of cases when we can't change the model or DDL of the table. 

    Please help :-)



    ------------------------------
    Best regards,

    Lars
    ------------------------------


  • 2.  RE: DbUpdateConcurrencyException when using SaveChangesAsync in .NET Core 3.1 EF Core with latest drivers

    Posted Tue August 04, 2020 04:38 AM
    Hi Lars,
     we are looking into this and we will get back to you on any possible workarounds.

    Thanks
    Vishwa

    ------------------------------
    Hosathota Vishwanatha
    ------------------------------



  • 3.  RE: DbUpdateConcurrencyException when using SaveChangesAsync in .NET Core 3.1 EF Core with latest drivers

    Posted Tue August 04, 2020 07:01 AM
    Thank you, much appreciated!

    ------------------------------
    Lars
    ------------------------------



  • 4.  RE: DbUpdateConcurrencyException when using SaveChangesAsync in .NET Core 3.1 EF Core with latest drivers

    Posted Tue April 06, 2021 04:33 PM
    Hi Vishwa,

    Any progress on this?  We're experiencing the same issue using the latest version

    ------------------------------
    Scott Sweezey
    ------------------------------



  • 5.  RE: DbUpdateConcurrencyException when using SaveChangesAsync in .NET Core 3.1 EF Core with latest drivers

    Posted Mon April 12, 2021 03:19 PM
    Hi Scott,
     true async operations cannot be supported since our lower level dependencies cannot support async. However, in the particular case, we want to explore further at EntityFramework layer. This item is prioritize now and we will keep you posted about the feasibility and then the timelines.

    Thanks
    Vishwa

    ------------------------------
    Hosathota Vishwanatha
    ------------------------------



  • 6.  RE: DbUpdateConcurrencyException when using SaveChangesAsync in .NET Core 3.1 EF Core with latest drivers

    Posted Fri August 06, 2021 02:30 PM
    Hi Scott,
      I wanted to point out the availability of this fix in Db2 .NET5 EF package.

    Thanks
    Vishwa

    ------------------------------
    Hosathota Vishwanatha
    ------------------------------