Cannot insert two one-to-one entities using two EF repositories in one transaction (method hangs)

Question

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. If I use OnCompleted event to insert a CreateTenantDto record - the method does not enter OnCompleted before returning newTenantDto and the latter is returned as a null (the records are inserted finally, but I want to return the inserted entity if it's inserted successfully). If I don't use OnCompleted at all - the method hangs (looks like DB lock). If I use two nested UnitOfWork objects - the method hangs as well. If I use the scope for working with two repositories -

    using (var scope = ServiceProvider.CreateScope())
    {
            var unitOfWorkManager = scope.ServiceProvider.GetRequiredService<IUnitOfWorkManager>();
            using (var tenantUow = unitOfWorkManager.Begin(new AbpUnitOfWorkOptions { IsTransactional = true }))
            { ... }
    }

it hangs also... 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?

    //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;
        }
    }

Mechanic

You're fighting an architecture that's needlessly complex. – Photogenic

@GertArnold I was told to reuse third party repository for working with nested entity, so I cannot just use a normal parent-child entity within single repository. Probably this is the main evil.. – Mechanic

This is one of those cases, where you will need to provide an MRE. When deadlocks are at play, the devil is in the details. Easiest way today is usually to upload it to GitHub and post the link here. In many cases, when trimming the project down to the minimal thing that still doesn't work, you will find the underlying issue yourself. If not, people will take a look at it, if it can be executed easily without much hassle. – Foothill

@Foothill Unfortunately - since it's a commercial project and DB is also not public either - cannot provide a test solution. I commented the navigation property (AbpTenant) and tried the same code. And it does not work either - as long as a foreign key (AbpId) is there. So I gave up for the moment due to lack of time and made temporary solution with two separate commits one under another and deleting first entry if the second insert fails. Hate this, but can't find another way so far... – Mechanic

The sample solution does not have to include any of the code or data of the commercial project. It only needs to reproduce the issue. – Foothill

@Foothill I appreciate all the assistance and even feedback I receive here. But unfortunately, I am terribly behind the schedule: I've killed several days trying to resolve this issue and now I need to move on - I am not given more time to try to build a reproduceable scenario (which I am sure would take additional day or more, taking into consideration Oracle and I have a lot more things to implement where I will stuck on for sure as well) :( Sorry, hope you understand. – Mechanic

@Mechanic No problem, I absolutely agree that making it work should always have priority over making it "right". – Foothill

Answer 1

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:

            //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:

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

Mechanic