Issues with handling composite Tenant / User entity #332 | Support Center | ABP Commercial

alexander.nikonov created 3 years ago

ABP Framework version: v2.9
UI type: Angular
Tiered (MVC) or Identity Server Seperated (Angular): Identity Server Seperated

Hi. To extend the existing ABP tenants / users functionality in the system, we had to create corresponding tables / entities which relate 1:1 to ABP one:

 //Our Tenant class
 using AbpTenant = Volo.Saas.Tenant;

 namespace XXX.Tenants {
      public class Tenant : LogEntity {
      ...
      public Guid AbpId { get; set; }
      public AbpTenant AbpTenant { get; set; }`
      ...
      
 //OnModelCreating   
        ...
        builder.Entity<Tenant>()
            .HasOne(x => x.AbpTenant)
            .WithOne()
            .HasPrincipalKey<Volo.Saas.Tenant>(x => x.Id)
            .HasForeignKey<Tenant>(x => x.AbpId);
        ...
        builder.Entity<Tenant>(b => {
            b.ToTable("OUR_TENANT");
            b.ConfigureByConvention();
            b.HasKey(x => x.Id);
            b.Property(x => x.Id).HasColumnName("C_TENANT").IsRequired().ValueGeneratedNever();
            ...
            b.Property(x => x.AbpId).HasColumnName("C_ABP_TENANT").IsRequired();
            ...

We had to create own client-side infrastructure in Angular app as well, to process these composite entities:

//Angular Tenants model

export interface State {
    tenants: Response;
    tenantsLookup: Common.LookupResponse<number>;
}

export interface Response {
    items: Tenants.TenantWithNavigationProperties[];
    totalCount: number;
}

export interface TenantsQueryParams extends ABP.PageQueryParams {
    filterText?: string;
    idMin?: number;
    idMax?: number;
    shortName?: string;
    fullName?: string;
    companyId?: number;
    masterId?: number;
    abpId?: string;
    isMaster?: boolean;
}

export interface AbpTenant {
    id: string;
    name: string;
    editionId: string;
}

export interface AbpTenantCreateDto {
    name: string;
    editionId: string;
    adminEmailAddress: string;
    adminPassword: string;
}

export interface AbpTenantUpdateDto {
    name: string;
    editionId: string;
}

export interface TenantWithNavigationProperties {
    id: number;
    shortName: string;
    fullName: string;
    comment: string;
    companyId: number;
    masterId: number;
    abpId: string;
    isMaster: boolean;
    abpTenant: AbpTenant;
}

export interface TenantCreateDto {
    id: number;
    shortName: string;
    fullName: string;
    comment: string;
    companyId: number;
    masterId?: number;
    abpId?: string;
    isMaster: boolean;
    abpTenant: AbpTenantCreateDto;
}

export interface TenantUpdateDto {
    id: number;
    shortName: string;
    fullName: string;
    comment: string;
    companyId: number;
    masterId?: number;
    abpId: string;
    isMaster: boolean;
    abpTenant: AbpTenantUpdateDto;
}

To circumvent potential issues with compatibility, we have decided to handle CRUD operations using two repositories - ABP ITenantRepository and IIdentityUserRepository. Unfortunately, it raised a major transaction issue: row lock when trying to create (not tested thouroughly on other operations, but sure the issue exists there as well) a new tenant. We have tried different approaches (including using ITenantAppService directly instead of ITenantRepository) to resolve it, but none of them worked:

            //using ITenantAppService
            using var uow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: true);
            var abpTenantDto = await _abpTenantAppService.CreateAsync(input.AbpTenant);
            var tenant = ObjectMapper.Map<CreateTenantDto, Tenant>(input);
            tenant.AbpId = abpTenantDto.Id; //causes row lock SOMETIMES
            var newTenant = await _tenantRepository.InsertAsync(tenant);
            await uow.CompleteAsync(); //this operations hangs SOMETIMES because of row lock
            return ObjectMapper.Map<Tenant, TenantDto>(newTenant);
            
            //using ITenantRepository
            using var uow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: true);
            var abpTenant = await _abpTenantManager.CreateAsync(input.AbpTenant.Name, input.AbpTenant.EditionId);
            input.AbpTenant.MapExtraPropertiesTo(abpTenant);
            var newAbpTenant = await _abpTenantRepository.InsertAsync(abpTenant);
            var tenant = ObjectMapper.Map<CreateTenantDto, Tenant>(input);
            tenant.AbpId = abpTenant.Id; //causes row lock ALWAYS
            var newTenant = await _tenantRepository.InsertAsync(tenant);
            await uow.CompleteAsync(); //this operations hangs ALWAYS because of row lock
            return ObjectMapper.Map<Tenant, TenantDto>(newTenant);

Now we use two separate commits (ABP tenant, then - our tenant) as a workaround (deleting the first entry if first commit failed), which of course is not good at all and is just a temporary solution:

            #region ABP tenant commit
            using var abpTenantUow = _unitOfWorkManager.Begin(requiresNew: true);
            Tenant newTenant = null;
            var abpTenant = await _abpTenantManager.CreateAsync(input.AbpTenant.Name, input.AbpTenant.EditionId);
            input.AbpTenant.MapExtraPropertiesTo(abpTenant);
            var newAbpTenant = await _abpTenantRepository.InsertAsync(abpTenant);
            await abpTenantUow.CompleteAsync();
            #endregion ABP tenant commit

            #region Tenant commit
            using var tenantUow = _unitOfWorkManager.Begin(requiresNew: true);
            var tenant = ObjectMapper.Map<CreateTenantDto, Tenant>(input);
            tenant.AbpId = abpTenant.Id;
            newTenant = await _tenantRepository.InsertAsync(tenant);
            tenantUow.Failed += async (sender, args) =>
            {
                using var abpTenantDeleteUow = _unitOfWorkManager.Begin(requiresNew: true);
                await _abpTenantRepository.HardDeleteAsync(abpTenant);
                await abpTenantDeleteUow.CompleteAsync();
            };
            await tenantUow.CompleteAsync();
            #endregion Tenant commit

Here is the sessions screenshot displaying the row lock when trying to use one transaction:

Could you please help us to resolve transaction issue in the first place and also suggest how to handle two-tenant-approach in the most correct way on both back-end and front-end side?

19 Answer(s)

liangshiwei created 3 years ago

Support Team Fullstack Developer

Hi:

Try:

[Dependency(ReplaceServices = true)]
[RemoteService(isEnabled:false)]
public class QaTenantAppService : TenantAppService
{
    private readonly IRepository<QaTenant, Guid> _qaTenantRepository;

    public QaTenantAppService(ITenantRepository tenantRepository, ITenantManager tenantManager,
        IDataSeeder dataSeeder, IRepository<QaTenant, Guid> qaTenantRepository) : base(tenantRepository, tenantManager, dataSeeder)
    {
        _qaTenantRepository = qaTenantRepository;
    }

    public override async Task<TenantDto> CreateAsync(TenantCreateDto input)
    {
        var tenant = await base.CreateAsync(input);

       await _qaTenantRepository.InsertAsync(new QaTenant(GuidGenerator.Create(), input.Name, tenant.Id));

       return tenant;
    }
}

0

alexander.nikonov created 3 years ago

Thank you for the suggestion. I have tried it, but I bumped into the same issue as already described - using ABP TenantService directly, without implementing a custom one: first INSERT operation was pretty long, but succeeded. However, when I tried to insert another Tenant - it stuck with the same row lock notification (see the screenshot above). BTW, nested transaction (using two nested IOW) also does not resolve the problem. I understand that transaction commit when calling CompleteAsync MUST release all the locks, but for some reason it does not work as expected. P.S. This bug is present in ABP Framework 3.0.5 as well. P.S.2. BTW - I think Repository needs to be used, not AppService. When I use TenantAppService - the first insert does not rollback if second one fails.
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

Hi,

Sorry, I can't reproduce your problem. Can you provide steps to reproduce? Thanks.
0
alexander.nikonov created 3 years ago
Meanwhile I came up with the following simple solution:

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); tenant.AbpTenant = abpTenant; var newTenant = await _tenantRepository.InsertAsync(tenant); await uow.CompleteAsync(); return ObjectMapper.Map<Tenant, TenantDto>(newTenant);

Seems like it works - it is enough to use sole InsertAsync for our Tenant entity, reference AbpTenant entry is inserted automatically, since it's being tracked by uow IUnitOfWork instance, right?

I have also checked the scenario when something is wrong with inserting our Tenant entity - AbpTenant is not inserted either. But the question is - when do I need to use isTransactional: true here? Is it when I have several repository CRUD operations?? Please explain.

Another question is regarding your repositories. I have noticed you never 'play' with CurrentValues.SetValues in DbConext, but always use Update call, so ALL fields are always updated even if not changed. Any reason for that?

UPDATE: Summarizing, I can tell that everything looks fine as long as I use don't use isTransactional: true. For instance, this works in positive scenario:

using var tenantUow = _unitOfWorkManager.Begin(requiresNew: true); var abpTenant = tenant.AbpTenant; await _tenantRepository.DeleteAsync(tenant); await _abpTenantRepository.HardDeleteAsync(abpTenant); await tenantUow.CompleteAsync();

BUT if something is WRONG with second delete - first delete comes through (not rolled back). If I use isTransactional: true and intentionally fail second delete for test - the operation just hangs (DB lock):

using var tenantUow = _unitOfWorkManager.Begin(requiresNew: true, isTransactional: true); var abpTenant = tenant.AbpTenant; await _tenantRepository.DeleteAsync(tenant); await _abpTenantRepository.HardDeleteAsync(abpTenant); await tenantUow.CompleteAsync();

To workaround this, I had to create custom delete method in Tenant repository and made use of TransactionScope
0
liangshiwei created 3 years ago
Support Team Fullstack Developer
Hi:

The CompleteAsync method calls SaveChangesAsync internally. When you call the InsertAsync,the data will not be stored in the database immediately. It will be saved to the database when the method is executed or you manually call SaveChangesAsync,In fact, this is the feaures of EF Core.

Transaction guarantees the atomicity of operations. You can see the document

EF Core has entity object tracking,If the entity has not changed, it will not be updated.

I will try to reproduce the lock problem
0

alexander.nikonov created 3 years ago

Ok, thank you - I understand all this. But how to resolve the issue with row lock when using isTransactional: true on CompleteAsync? It does not look like a proper behavior, does it? CompleteAsync needs to commit all the changes within UOW area, but instead, we receive a row lock as shown on screenshots from SQL Manager above. I was able to workaround the issue in one case simply because I have moved all the logic into one repository. Though, I have a different case, where the logic is spread across multiple repositories using within the same UOW instance. And this is where row lock happens. Please provide me with Oracle test instance where I can connect and I will demostrate it.
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

Hi:

I make an example project: https://github.com/realLiangshiwei/AbpQa332/tree/master. I did not get the problem you described. You can see it,
0

alexander.nikonov created 3 years ago

Could you please try this example on Oracle DB (any version starting from 12c), since this is what we are using? It would be great to isolate DB and make sure it is not an Oracle issue.
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

Hi,

I have switched the database provider to oracle(18c) and it works fine. See https://github.com/realLiangshiwei/AbpQa332
0

alexander.nikonov created 3 years ago

Hi.

We are using commercial version of ABP framework - so it took me some time to change your project to using it instead of free version.

I have replaced TenantManagement projects to Saas projects. There are some other changes. I have also changed the view of QaTenant entity to match our entity.

And I was able to reproduce the lock issue. Please have a look at ABP Tenant transaction deadlock

Looking for your reply ASAP, since we really need transactions.
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

Hi,

I used the project you provided, it works fine for me. PS: I don't have devart license key ,so I use the official oracle driver and oracle version is 18c.

See https://github.com/realLiangshiwei/AbpQa332/tree/tenant

Will there be this problem if you switch to the official driver?

I can help you remotely , my email is shiwei.liang@volosoft.com
0
alexander.nikonov created 3 years ago
Hi, I was unable to run the solution without Devart driver. We have uppercased table names and column names. Devart driver setting below eliminates quotes around object names in the generated queries. As a result, queries are not case-sensitive. If Devart driver is not used - the queries contain quotes and tables / columns are not found - "ORA-00942: table or view does not exist":

public class qaEntityFrameworkCoreModule : AbpModule { ... public override void ConfigureServices(ServiceConfigurationContext context) { ... Configure<AbpDbContextOptions>(options => { var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance; config.Workarounds.DisableQuoting = true; ... } } }

Do you by chance know the setting how to achieve the same result without Devart? This setting works only if using Devart Oracle driver, not official driver.

Please be aware we have DB-first solution! Our local DB tables are already created - so we don't want our solution to depend on the code (e.g. make the code setting to generate all the tables in lower- or upper-case). We would like to ignore case instead.
0

liangshiwei created 3 years ago
Support Team Fullstack Developer

Hi,

Sorry, I don't know much about oracle, but if your table and column names are uppercase, you can use this lilbary: https://github.com/efcore/EFCore.NamingConventions

alexander.nikonov created 3 years ago

Already tried that, but for some reason it does not work - probably, it does not work with Oracle. Is it possible for you to try transaction thing witn Devart provider (while I am stuck on case mismatch issue) so I was sure the lock was really a Devart problem? Thank you. P.S. How to implement something kind of DB query inteceptor (where I can remove quotes, etc.) by means of ABP framework? This thing did not work:

        Configure&lt;AbpDbContextOptions&gt;(options =>
        {
            options.UseOracle(oracleDbContextOptionsBuilder =>
            {
                var dbContextOptionsBuilder = new DbContextOptionsBuilder();
                dbContextOptionsBuilder.AddInterceptors(new RemoveQuotesInterceptor());
                oracleDbContextOptionsBuilder = new Oracle.EntityFrameworkCore.Infrastructure.OracleDbContextOptionsBuilder(dbContextOptionsBuilder);
            });
        });

liangshiwei created 3 years ago

Support Team Fullstack Developer

Could you give me a devart license? I tried use trial license but not work. thanks.

If you want to remove the quotes in sql, you can do it:

public class RemoveQuotesInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        return base.ReaderExecuting(RemoveQuotes(command), eventData, result);
    }

    public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        return base.ReaderExecutingAsync(RemoveQuotes(command), eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        return base.ScalarExecuting(RemoveQuotes(command), eventData, result);
    }

    public override Task<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        return base.ScalarExecutingAsync(RemoveQuotes(command), eventData, result, cancellationToken);
    }

    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        return base.NonQueryExecuting(RemoveQuotes(command), eventData, result);
    }

    public override Task<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        return base.NonQueryExecutingAsync(RemoveQuotes(command), eventData, result, cancellationToken);
    }

    private DbCommand RemoveQuotes(DbCommand command)
    {
        command.CommandText = command.CommandText.Replace("\"", "");
        return command;
    }
}

0

alexander.nikonov created 3 years ago

liangshiwei, thank you very much for your invaluable help! Our test team will check transactions thouroughly with standard driver to make sure the problem went away. And I will be able to close the ticket, if so.
0

alexander.nikonov created 3 years ago

Everything seems to be working good. But one complaint: speed. For some reason, login - just like a tenant creation - takes very long, despite the fact we are in local network (well, DB is on Azure cloud actually). Is it possible to trace somehow to make sure it's DB problem or not?
1

liangshiwei created 3 years ago
Support Team Fullstack Developer

If you are using tiered template, Usually caused by not installing redis.
0

alexander.nikonov created 3 years ago

Thank you - will consider this!

Have an answer to this question? Log in and write your answer.