Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync — oracle-tech

    Forum Stats

  • 3,715,657 Users
  • 2,242,821 Discussions
  • 7,845,481 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException on many changes before SaveChangesAsync

SergeyKotelnikov
SergeyKotelnikov Member Posts: 6
edited December 2019 in ODP.NET

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.

SergeyKotelnikov

Best Answer

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited April 2019

    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
    SergeyKotelnikov Member Posts: 6
    edited April 2019

    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
    Alex Keh-Oracle Posts: 2,751 Employee
    edited April 2019

    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
  • SergeyKotelnikov
    SergeyKotelnikov Member Posts: 6
    edited June 2019

    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
    Alex Keh-Oracle Posts: 2,751 Employee
    edited June 2019 Accepted Answer

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

    SergeyKotelnikov
  • user8132251
    user8132251 Member Posts: 1 Red Ribbon
    edited October 2019

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

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited October 2019

    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
    1967695 Member Posts: 2
    edited October 2019

    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
    Alex Keh-Oracle Posts: 2,751 Employee
    edited October 2019

    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
    Alex Keh-Oracle Posts: 2,751 Employee
    edited October 2019

    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
    1967695 Member Posts: 2
    edited October 2019

    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
    Alex Keh-Oracle Posts: 2,751 Employee
    edited December 2019

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

Sign In or Register to comment.