Cannot insert two one-to-one entities using two EF repositories in one transaction (row lock issue)

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
alexandis
Posts: 3
Joined: Wed 27 May 2020 12:26

Cannot insert two one-to-one entities using two EF repositories in one transaction (row lock issue)

Post by alexandis » Fri 24 Jul 2020 20:00

I have two entities bound as one-to-one via foreignkey: CreateTenantDto and SaasTenantCreateDto. I need to use TWO repositories (_abpTenantRepository is an instance of 3rd party repository from ABP Framework) to insert those entities into DB. I am trying to use ABP UnitOfWork implementation for this. After SaasTenantCreateDto entity is inserted, I am trying to insert CreateTenantDto entry which depends on it.

It hangs. It is definitely the lock and it has to do with accessing the id from the newly created newAbpTenant: I can see that in SQL Developer Sessions
enq: TX - row lock contention
and guilty session is another my HttpApi host session. Probably, the reason is as Oracle doc says: "
INSERT, UPDATE, and DELETE statements on the child table do not acquire any locks on the parent table, although INSERT and UPDATE statements wait for a row-lock on the index of the parent table to clear.
" - SaveChangesAsync causes new record row lock?

How to resolve this issue? Is there a bug in EntityFrameworkCore Devart driver or EntityFrameworkCore itself or I'm doing something wrong?

Code: Select all

   //OnModelCreatingBinding
    builder.Entity<Tenant>()
            .HasOne(x => x.AbpTenant)
            .WithOne()
            .HasPrincipalKey<Volo.Saas.Tenant>(x => x.Id)
            .HasForeignKey<Tenant>(x => x.AbpId);
    ...
    b.Property(x => x.AbpId).HasColumnName("C_ABP_TENANT").IsRequired();

    //Mapping ignoration to avoid problems with 'bound' entities, since using separate repositories for Insert / Update
    CreateMap<CreateTenantDto, Tenant>().ForMember(x => x.AbpTenant, opt => opt.Ignore());
    CreateMap<UpdateTenantDto, Tenant>().ForMember(x => x.AbpTenant, opt => opt.Ignore());

    public class CreateTenantDto
    {
        [Required]
        public int Id { get; set; }

        ...

        public Guid? AbpId { get; set; }

        public SaasTenantCreateDto AbpTenant { get; set; }
    }

    public async Task<TenantDto> CreateAsync(CreateTenantDto input)
    {
        try
        {
            TenantDto newTenantDto = null;
            using (var uow = _unitOfWorkManager.Begin(new AbpUnitOfWorkOptions { IsTransactional = true, IsolationLevel = System.Data.IsolationLevel.Serializable }))
            {
                var abpTenant = await _abpTenantManager.CreateAsync(input.AbpTenant.Name, input.AbpTenant.EditionId);
                input.AbpTenant.MapExtraPropertiesTo(abpTenant);
                var newAbpTenant = await _abpTenantRepository.InsertAsync(abpTenant);
                await uow.SaveChangesAsync();

                var tenant = ObjectMapper.Map<CreateTenantDto, Tenant>(input);
                tenant.AbpId = newAbpTenant.Id;
                var newTenant = await _tenantRepository.InsertAsync(tenant);
                newTenantDto = ObjectMapper.Map<Tenant, TenantDto>(newTenant);

                await uow.CompleteAsync();
            }

            return newTenantDto;
    }

    //Implementation by ABP Framework
    public virtual async Task CompleteAsync(CancellationToken cancellationToken = default)
    {
        if (_isRolledback)
        {
            return;
        }

        PreventMultipleComplete();

        try
        {
            _isCompleting = true;
            await SaveChangesAsync(cancellationToken);
            await CommitTransactionsAsync();
            IsCompleted = true;
            await OnCompletedAsync();
        }
        catch (Exception ex)
        {
            _exception = ex;
            throw;
        }
    }

Shalex
Site Admin
Posts: 8981
Joined: Thu 14 Aug 2008 12:44

Re: Cannot insert two one-to-one entities using two EF repositories in one transaction (row lock issue)

Post by Shalex » Mon 27 Jul 2020 14:25

alexandis wrote:
Fri 24 Jul 2020 20:00
How to resolve this issue? Is there a bug in EntityFrameworkCore Devart driver or EntityFrameworkCore itself or I'm doing something wrong?
Or with ABP.

We are not experts with ABP, but you can try:
1) remove await uow.SaveChangesAsync(); from your code
2) assign navigation property
tenant.AbpTenant = newAbpTenant;
instead of assigning ID
tenant.AbpId = newAbpTenant.Id;

alexandis
Posts: 3
Joined: Wed 27 May 2020 12:26

Re: Cannot insert two one-to-one entities using two EF repositories in one transaction (row lock issue)

Post by alexandis » Tue 28 Jul 2020 11:10

I cannot assign navigation property, since it needs to be taken care of in own repository (_abpTenantRepository) - so intentionally make it NULL in the mapping. So far, I had to use workaround and deal with each entity separately: committing after both changes. And if something is wrong with second entity - I delete the first one. I hate workarounds, but I am lack of time now :(

alexandis
Posts: 3
Joined: Wed 27 May 2020 12:26

Re: Cannot insert two one-to-one entities using two EF repositories in one transaction (row lock issue)

Post by alexandis » Tue 04 Aug 2020 10:06

I have finally resolved the problem using the following approach (but it is not using TWO repositories which seems to be impossible to implement, since we need to manipulate DbContext directly):

Application service layer:

Code: Select all

            //requiresNew: true - to be able to use TransactionScope
            //isTransactional: false, otherwise it won't be possible to use TransactionScope, since we would have active ambient transaction

            using var uow = _unitOfWorkManager.Begin(requiresNew: true);
            var abpTenant = await _abpTenantManager.CreateAsync(input.AbpTenant.Name, input.AbpTenant.EditionId);
            input.AbpTenant.MapExtraPropertiesTo(abpTenant);
            var tenant = ObjectMapper.Map<CreateTenantDto, Tenant>(input);
            var newTenant = await _tenantRepository.InsertAsync(tenant, abpTenant);
            await uow.CompleteAsync();
            return ObjectMapper.Map<Tenant, TenantDto>(newTenant);
Handmade InsertAsync method on Repository (EntityFrameworkCore) layer:

Code: Select all

        using (new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
        {
            var newAbpTenant = DbContext.AbpTenants.Add(abpTenant).Entity;
            tenant.AbpId = newAbpTenant.Id;
            var newTenant = DbContext.Tenants.Add(tenant).Entity;
            if (autoSave)
            {
                await DbContext.SaveChangesAsync(GetCancellationToken(cancellationToken));
            }
            return newTenant;
        }

Post Reply