Tenant Group Materialized Tables for Multi-Tenant Query Locking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-tenant application systems, materialized tables are frequently locked during data synchronization, limiting their availability for query operations due to high update rates across shared basic-tables, which affects query efficiency and business operations.
Innovation Solution
Analyzing data access history to group tenants with similar update patterns and constructing tenant group materialized tables, allowing for dynamic enabling and disabling based on update activities to optimize synchronization and availability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If materialized tables are used to improve query efficiency, then query speed is improved, but the frequency of table locking increases due to high update rates
Solution Approach 1:
The patent segments the single shared materialized table into multiple tenant-specific materialized tables. Each tenant group gets its own materialized table instance, so that update operations on one tenant's basic table only lock that specific tenant's materialized table, not all materialized tables. This segmentation isolates locking effects and improves overall system availability.
Solution Approach 2:
The patent implements dynamic selection of materialized tables based on tenant identification. The system dynamically determines which materialized table to use for each query based on the tenant's update patterns and current state, allowing flexible switching between synchronized and unsynchronized tables to optimize both query performance and availability.
2Manufacturing precision
If frequent synchronization is performed to maintain data consistency, then data accuracy is improved, but query efficiency deteriorates due to increased locking
Solution Approach 1:
The patent applies local quality by performing synchronization selectively for specific tenant groups rather than globally. Each tenant group's materialized table is synchronized based on that group's specific update patterns, allowing other tenant groups to continue querying their own materialized tables without interference, thus maintaining data consistency where needed while preserving query efficiency elsewhere.
Solution Approach 2:
The patent performs preliminary analysis of tenant update patterns to identify which tenant groups are likely to synchronize frequently. Based on this analysis, the system proactively manages synchronization timing and selection, preventing unnecessary synchronization operations that would lock materialized tables and degrade query performance for other tenants.
3Device complexity
If a single materialized table is shared by all tenants, then device complexity is reduced, but the impact of locking on business operations increases
Solution Approach 1:
The patent segments the shared materialized table into multiple tenant-specific instances, reducing the scope of locking to individual tenant groups rather than affecting all tenants. This segmentation isolates the impact of synchronization operations, allowing business operations to continue uninterrupted for tenants not undergoing synchronization.
Solution Approach 2:
The patent changes the parameter of materialized table sharing from a binary state (shared or not) to a granular state where different tenant groups can have different levels of sharing based on their update patterns. This allows the system to optimize for both reduced complexity and reduced locking impact by dynamically adjusting the sharing parameter.
Data Source
AI summary
A method, system and computer program for processing materialized tables in a multi-tenant application system, wherein in the multi-tenant application system, a plurality of tenants share one or more basic-tables. According to the data access history information of the plurality of tenants, an update pattern analyzer analyzes the similarity of the update patterns for one or more basic-tables by the plurality of tenants. Furthermore, according to the similarity analyzed by the update pattern analyzer, a tenant grouping means groups the plurality of tenants into a plurality of tenant groups. Additionally, according to the tenant groups grouped by the tenant grouping means, a materialized table constructor constructs the tenant group materialized tables from the one or more basic-tables.