Cross-Store Row Locking for Lost Update Protection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems struggle to optimize performance when handling complex queries across diverse database table types, particularly in scenarios involving row-locking mechanisms that can only target a single storage type within a query, failing to protect against lost updates and concurrent access issues.
Innovation Solution
Implementing a database system that creates rowlock accessors for multiple storage layers, inserts a rowlock relation into the query execution plan, and uses a rowlock operator to lock rows identified by a unique identifier, enabling row-locking across different database table types such as column-store and row-store through a join or cross product.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If row-locking mechanism targets only a single storage type within a query, then the locking mechanism is simple to implement, but it fails to protect against lost updates and concurrent access issues across diverse table types
Solution Approach 1:
The row-locking mechanism is designed to work across multiple storage types (column-store and row-store tables) through a unified interface. The system creates storage-type-specific row-lock accessors that are managed through a common rowlock operator, enabling the same locking logic to protect rows in diverse table types without requiring separate locking mechanisms for each storage type.
2Reliability
If row-locking is implemented across multiple storage layers, then protection against concurrent access issues is improved, but the complexity of creating and managing rowlock accessors increases
Solution Approach 1:
A rowlock operator is introduced as an intermediary component that manages multiple storage-type-specific row-lock accessors. This operator handles the creation, coordination, and execution of row locking operations across different storage layers, shielding users from the complexity of managing multiple accessors directly while ensuring reliable concurrent access protection.
Solution Approach 2:
The row-locking system is segmented into storage-type-specific row-lock accessors (for column-store and row-store tables) that are managed through a common rowlock operator. This segmentation allows each accessor to be optimized for its specific storage type while the operator provides unified management, reducing the overall complexity of handling diverse storage layers.
3Productivity
If the database system creates multiple rowlock accessors for different storage layers, then query execution performance is improved through efficient row locking, but the overhead of creating and inserting rowlock relations into query execution plans increases
Solution Approach 1:
The system creates storage-type-specific row-lock accessors in advance during the query plan generation phase, before query execution begins. These accessors are prepared and associated with the appropriate storage layers, so that during query execution, the rowlock operator can directly utilize the pre-prepared accessors without incurring additional creation overhead, thus improving query execution performance while minimizing preparation time.
Data Source
AI summary
A database system receives a query targeting a plurality of different database table types, where the query includes a rowlock request. The database system creates a plurality of rowlock accessors for a plurality of storage layers in response to receiving the query with the rowlock request. Next, the database system inserts a rowlock relation into a query execution plan corresponding to the query. Then, a rowlock operator is created for the query execution plan. Next, the plurality of rowlock accessors are set to enable locking of a plurality of targeted rows. Then, during execution of the query execution plan, a given rowlock accessor is accessed via the rowlock operator to lock at least one row.