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.

Alex Keh-Oracle

The bug fix will be in ODP.NET Core production.

Alex Keh-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?

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

Alex Keh-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.

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.

Alex Keh-Oracle

The bug fix will be in ODP.NET Core production.

user8132251

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

Alex Keh-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.

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);

Alex Keh-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.

Alex Keh-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.

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.

Alex Keh-Oracle

Bug 30452644 has been fixed and is available in NuGet Gallery as part of ODP.NET EF Core 19.6.