11 Replies Latest reply on Oct 22, 2019 8:10 PM by 1967695

    Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync

    SergeyKotelnikov

      Hello all! I faced the following. When I update DB with many changes before SaveChangesAsync I catch Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 15394 row(s). I fixed it inserting SaveChangesAsync after each small piece of changes. I am sure that problem in the provider (Oracle.EntityFrameworkCore 2.18.0-beta3) because after I chaned it to Devart provider it works correctly. Maybe someone faced the similar problem and will help me. Thanks.

        • 1. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
          Alex Keh - Product Manager-Oracle

          How many rows were you expecting to be updated? It's not clear from your post whether the problem is an incorrect number of rows were modified, the return value from SaveChangesAsync, or the error message.

           

          Do you have a simple test case showing the behavior?

          • 2. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
            SergeyKotelnikov

            I've made a simple test, it is similar to my real production case where I have the problem.

            My test tables with triggers and sequences for ID:

             

            CREATE TABLE TESTPARENT

            (

                ID     NUMBER(19, 0) NOT NULL,

                S1     NVARCHAR2(250) NULL,

                S2     NVARCHAR2(250) NULL,

                S3     NVARCHAR2(250) NULL,

                CONSTRAINT PARENT_PK PRIMARY KEY (ID)

            );

            CREATE SEQUENCE PARENT_ID_SEQ START WITH 1 INCREMENT BY 1 NOCACHE ORDER;

            CREATE OR REPLACE TRIGGER PARENT_ID_TRG BEFORE INSERT ON TESTPARENT

            FOR EACH ROW WHEN (NEW.ID IS NULL)

            BEGIN

                :new.id := PARENT_ID_SEQ.nextval;

            END;

            /

            CREATE TABLE TESTCHILD

            (

                ID     NUMBER(19, 0) NOT NULL,

                PID    NUMBER(19, 0) NOT NULL,

                S1     NVARCHAR2(250) NULL,

                S2     NVARCHAR2(250) NULL,

                S3     NVARCHAR2(250) NULL,

                CONSTRAINT CHILD_PK PRIMARY KEY (ID),

                CONSTRAINT PARENT_FK FOREIGN KEY (PID) REFERENCES TESTPARENT(ID)

            );

            CREATE SEQUENCE CHILD_ID_SEQ START WITH 1 INCREMENT BY 1 NOCACHE ORDER;

            CREATE OR REPLACE TRIGGER CHILD_ID_TRG BEFORE INSERT ON TESTCHILD

            FOR EACH ROW WHEN (NEW.ID IS NULL)

            BEGIN

                :new.id := CHILD_ID_SEQ.nextval;

            END;

            /

            COMMIT;

             

            My C# Test

             

            using Microsoft.EntityFrameworkCore;

            using Microsoft.Extensions.DependencyInjection;

            using NUnit.Framework;

            using System;

            using System.Collections.Generic;

            using System.ComponentModel.DataAnnotations;

            using System.ComponentModel.DataAnnotations.Schema;

            using System.Threading.Tasks;

             

            namespace MyTests

            {

                [TestFixture]

                internal class DbTests

                {

                    private IServiceProvider _provider;

                    [OneTimeSetUp]

                    public void Setup()

                    {

                        var services = new ServiceCollection()

                            .AddEntityFrameworkOracle()

                            .AddDbContext<TestDbContext>(dbConfig => dbConfig.UseOracle(

                                "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Tst-efr027)(PORT=1525)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=Jira)));User Id=clients_dev;Password=***"));

                        _provider = services.BuildServiceProvider();

                    }

                    [OneTimeTearDown]

                    public void Dispose() => (_provider as IDisposable).Dispose();

                    [Table("TESTPARENT")]

                    internal sealed class TestParent

                    {

                        [Key]

                        public long ID { get; set; }

                        public string S1 { get; set; }

                        public string S2 { get; set; }

                        public string S3 { get; set; }

                        public ICollection<TestChild> Childs { get; set; }

                    }

                    [Table("TESTCHILD")]

                    internal sealed class TestChild

                    {

                        [Key]

                        public long ID { get; set; }

                        public long PID { get; set; }

                        public string S1 { get; set; }

                        public string S2 { get; set; }

                        public string S3 { get; set; }

                        [ForeignKey(nameof(PID))]

                        public TestParent Parent { get; set; }

                    }

                    internal class TestDbContext : DbContext

                    {

                        public TestDbContext(DbContextOptions options): base(options) { }

                        public virtual DbSet<TestParent> Parents { get; set; }

                        public virtual DbSet<TestParent> Childs { get; set; }

                    }

             

                    [Test]

                    public async Task Test()

                    {

                        using (var db = _provider.GetRequiredService<TestDbContext>())

                        {

                            var parent = new TestParent

                            {

                                S1 = "sdfasfsda",

                                S2 = "dsadasdas",

                                S3 = "dsadadasd"

                            };

                            db.Parents.Add(parent);

                            await db.SaveChangesAsync();

                            parent.S1 = "dsadsdgsdfhsfhfshsfgh sh sgh sfgh fghhs";

                            parent.Childs = new List<TestChild>();

                            for (int i = 0; i < 5; i++)

                            {

                                parent.Childs.Add(new TestChild

                                {

                                    S1 = "f asd f"

                                });

                            }

                            await db.SaveChangesAsync();

                        }

                    }

                }

            }

             

            Please try set i < 2, i < 3, i < 5 etc.

            In my case i<2 works ok, but i < 3 not .
            Error in last SaveChangesAsync:

            Message: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException : Database operation expected to affect 1 row(s) but actually affected 5 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

            • 3. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
              Alex Keh - Product Manager-Oracle

              Thanks for providing the test case. I'll have my team take review it and debug it.

               

              I've filed Bug 29683096 to track your issue.

              • 4. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
                SergeyKotelnikov

                Hello! Could you say about the Bug status? I cant view it unfourtunately(

                In the last library version (2.19.0-beta4) this problem is not fixed.

                • 6. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
                  user8132251

                  Are there any other known bugs that have been fixed, because I am still having this error.

                  • 7. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
                    Alex Keh - Product Manager-Oracle

                    All public bugs that are fixed are listed in the ODP.NET Core or Oracle EF Core NuGet package README. Bugs found internally by an Oracle team are fixed, but not noted in the README. Most of these bugs are pre-production code. Thus, no customer would encounter them. However, some of the bugs found internally may exhibit their behavior in prior beta or production releases.

                    • 8. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
                      1967695

                      We are still experiencing this issue.

                      Our environment set up: oracle 12, .net core 2.2, oracle.EntityFrameworkCore 2.19.30, Oracle.ManagedDataAccess.Core 2.19.31.

                      The SaveChangesAsync() updates/inserts rows to multiple tables. The one seems to have issue is a table that has column "ID" as identity column. Is there still a bug with insert to identity column?

                       

                      Here's the table

                      CREATE TABLE FCST.FCST_AUDIT_LOG

                      (

                          ID NUMBER(10) GENERATED ALWAYS AS IDENTITY (START WITH 1) NOT NULL CONSTRAINT FCST_AUDIT_LOG_PK PRIMARY KEY,

                          LOCATION_ID NUMBER(10) NOT NULL,

                          FORECAST_WEEK DATE NOT NULL,

                          PEOPLESOFT_ID NUMBER(10) NOT NULL,

                          TIMESTAMP TIMESTAMP NOT NULL,

                          EVENT_ID NUMBER(10) NOT NULL,

                          CALLING_APP_ID NUMBER(10) NOT NULL,

                          OBJECT CLOB NULL

                      )

                       

                      Here's the code in the c# OnModelCreating()

                      modelBuilder.Entity<AuditLog>
                                  (
                                      entity =>
                                      {
                                          entity.ToTable("FCST_AUDIT_LOG", "FCST");

                                          entity.HasKey(e => e.Id)
                                                .HasName("FCST_AUDIT_LOG_PK");

                                          entity.HasIndex(e => e.Id)
                                               .HasName("FCST_AUDIT_LOG_PK")
                                               .IsUnique();

                                          entity.Property(e => e.Id)
                                                .HasColumnName("ID")
                                                .UseOracleIdentityColumn();

                                          entity.Property(e => e.CallingAppId).HasColumnName("CALLING_APP_ID");

                                          entity.Property(e => e.EventId).HasColumnName("EVENT_ID");

                                          entity.Property(e => e.ForecastWeek)
                                                .HasColumnName("FORECAST_WEEK")
                                                .HasColumnType("DATE");

                                          entity.Property(e => e.LocationId).HasColumnName("LOCATION_ID");

                                          entity.Property(e => e.Object)
                                                .HasColumnName("OBJECT")
                                                .HasColumnType("CLOB");

                                          entity.Property(e => e.PeoplesoftId).HasColumnName("PEOPLESOFT_ID");

                                          entity.Property(e => e.Timestamp)
                                                .HasColumnName("TIMESTAMP")
                                                .HasColumnType("TIMESTAMP(6)");
                                      }
                                  );

                      Here's the code to add AuditLog to the context:

                      var auditLog = new AuditLog
                                  {
                                      LocationId = locationId,
                                      ForecastWeek = forecastWeek,
                                      Timestamp = DateTime.Now,
                                      PeoplesoftId = user.EmployeeId,
                                      EventId = (int) eventType,
                                      CallingAppId = (int) callingApp,
                                      Object = json
                                  };

                       

                           forecastingContext.AuditLog.Add(auditLog);

                       

                      • 9. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
                        Alex Keh - Product Manager-Oracle

                        If you are using an identity column, be sure to use UseOracleIdentityColumn() extension method. For example:

                         

                        modelBuilder.Entity<Blog>().Property(p => p.Id).UseOracleIdentityColumn();

                         

                        By default, this is not enabled.

                         

                        The bug the OP reported has been fixed. If you are encountering a similar error, please provide a more complete test case. We'll try to reproduce the problem in house, then fix it in an upcoming release.

                        • 10. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
                          Alex Keh - Product Manager-Oracle

                          For reference, I filed a new bug (30452644) to track this possible new concurrency issue in ODP.NET Core 19.5. If someone can post a complete test case to reproduce the issue, my team can debug and fix the issue more quickly.

                          • 11. Re: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync
                            1967695

                            Here's my test case that will produce the issue. The first time the code is run it usually update and insert the data fine. If you continuously to run the code for second and third time and so forth the dbconcurrency error occurs.

                            Our environment set up: oracle 12, .net core 2.2, oracle.EntityFrameworkCore 2.19.30, Oracle.ManagedDataAccess.Core 2.19.31.

                            DB tables

                             

                            CREATE TABLE FCST_AUDIT_LOG

                            (

                                ID NUMBER(10) GENERATED ALWAYS AS IDENTITY (START WITH 1) NOT NULL CONSTRAINT FCST_AUDIT_LOG_PK PRIMARY KEY,

                                LOCATION_ID NUMBER(10) NOT NULL,

                                FORECAST_WEEK DATE NOT NULL,

                                PEOPLESOFT_ID NUMBER(10) NOT NULL,

                                TIMESTAMP TIMESTAMP NOT NULL,

                                EVENT_ID NUMBER(10) NOT NULL,

                                CALLING_APP_ID NUMBER(10) NOT NULL,

                                OBJECT CLOB NULL

                            );

                             

                            create table TEST_A

                            (

                              LOC_ID NUMBER(10) not null,

                              BC_ID  NUMBER(10) not null,

                              WEEK   DATE not null,

                              DOW    NUMBER(10) not null,

                              DP_ID  NUMBER(10) not null,

                              COUNT  NUMBER(10) default 0 not null

                            )

                            tablespace FCST_DATA

                              pctfree 10

                              initrans 1

                              maxtrans 255

                              storage

                              (

                                initial 256K

                                next 256K

                                minextents 1

                                maxextents unlimited

                                pctincrease 0

                              );

                            -- Create/Recreate primary, unique and foreign key constraints

                            alter table TEST_A

                              add constraint PK_TA primary key (LOC_ID, BC_ID, WEEK, DOW, DP_ID)

                              using index

                              tablespace FCST_INDX

                              pctfree 10

                              initrans 2

                              maxtrans 255

                              storage

                              (

                                initial 1M

                                next 1M

                                minextents 1

                                maxextents unlimited

                                pctincrease 0

                              );

                             

                            Insert some data to table Test_A

                            DECLARE

                               a number(2);

                            BEGIN

                               FOR a in 1 .. 200 LOOP

                                  insert into test_a(loc_id, bc_id, week, dow, dp_id, count)

                                  values (a, a, to_date('10/21/2019', 'mm/dd/yyyy'), a, a, 100);

                              END LOOP;

                            EN

                            commit;

                            /

                             

                            C# Code

                            OnModelCreating(ModelBuilder modelBuilder)

                             

                            modelBuilder.Entity<AuditLog>
                                        (
                                            entity =>
                                            {
                                                entity.ToTable("FCST_AUDIT_LOG", "FCST");

                                                entity.HasIndex(e => e.Id)
                                                      .HasName("FCST_AUDIT_LOG_PK")
                                                      .IsUnique();

                                                entity.Property(e => e.Id)
                                                      .HasColumnName("ID")
                                                      .ValueGeneratedOnAdd();

                                                entity.Property(e => e.CallingAppId).HasColumnName("CALLING_APP_ID");

                                                entity.Property(e => e.EventId).HasColumnName("EVENT_ID");

                                                entity.Property(e => e.ForecastWeek)
                                                      .HasColumnName("FORECAST_WEEK")
                                                      .HasColumnType("DATE");

                                                entity.Property(e => e.LocationId).HasColumnName("LOCATION_ID");

                                                entity.Property(e => e.Object)
                                                      .HasColumnName("OBJECT")
                                                      .HasColumnType("CLOB");

                                                entity.Property(e => e.PeoplesoftId).HasColumnName("PEOPLESOFT_ID");

                                                entity.Property(e => e.Timestamp)
                                                      .HasColumnName("TIMESTAMP")
                                                      .HasColumnType("TIMESTAMP(6)");
                                            }
                                        );

                            modelBuilder.Entity<TestA>(entity =>
                                        {
                                            entity.HasKey(e => new { e.LocId, e.BcId, e.Week, e.Dow, e.DpId })
                                                .HasName("PK_TA");

                                            entity.ToTable("TEST_A", "FCST");

                                            entity.HasIndex(e => new { e.LocId, e.BcId, e.Week, e.Dow, e.DpId })
                                                .HasName("PK_TA")
                                                .IsUnique();

                                            entity.Property(e => e.LocId).HasColumnName("LOC_ID");

                                            entity.Property(e => e.BcId)
                                                .HasColumnName("BC_ID");

                                            entity.Property(e => e.Week)
                                                .HasColumnName("WEEK")
                                                .HasColumnType("DATE");

                                            entity.Property(e => e.Dow).HasColumnName("DOW");

                                            entity.Property(e => e.DpId).HasColumnName("DP_ID");

                                            entity.Property(e => e.Count)
                                                .HasColumnName("COUNT");
                                        });

                             

                            Test Class

                            public class Test
                                {
                                    public async Task TestDBUpdate()
                                    {
                                        var options = new DbContextOptionsBuilder<ForecastingContext>()
                                               .UseOracle(connectionString)
                                               .Options;

                             

                                        using (var context = new TestContext(options))
                                        {
                                            var testAList = await context.TestA.ToListAsync();

                                            testAList.ForEach(t => t.Count = 200);

                                            string json = JsonConvert.SerializeObject(testAList.First());
                                            var auditLog = new AuditLog
                                            {
                                                LocationId = 1,
                                                ForecastWeek = DateTime.Today,
                                                Timestamp = DateTime.Now,
                                                PeoplesoftId = 123456789,
                                                EventId = 1,
                                                CallingAppId = 1,
                                                Object = json
                                            };

                                            context.AuditLog.Add(auditLog);

                                            await context.SaveChangesAsync();

                                        }
                                    }
                                }

                             

                            Here's the error:

                            Result StackTrace:

                            at Oracle.EntityFrameworkCore.Update.Internal.OracleModificationCommandBatch.ConsumeAsync(RelationalDataReader relationalReader, CancellationToken cancellationToken)

                               at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

                               at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)

                               at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)

                               at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)

                               at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

                               at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

                               at Test.Test.TestDBUpdate() in C:\Repos\Test\Test.Tests\Test.cs:line 44

                            --- End of stack trace from previous location where exception was thrown ---

                            Result Message: Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException : Database operation expected to affect 1 row(s) but actually affected 9 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.

                             

                            The affected rows in the error are always the total number of rows in the fcst_audit_log table. It seems it wants to update all rows instead of inserting one row. We worked around the issue by removing identity column in the table, and create composite keys instead.