Two-Level Transaction Hierarchy for Database Concurrency Control
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in processing concurrent transactions in a distributed environment while ensuring data integrity and consistency, particularly in avoiding deadlocks and starvation, which are critical for online transactional processing (OLTP).
Innovation Solution
The implementation of a concurrency control mechanism combining multi-version concurrency control (MVCC) for read operations and locking for write operations, along with a two-level transaction hierarchy, where top-level transactions correspond to SQL transactions and nested transactions correspond to SQL statements within parent transactions, allowing for read and write operations, rollback, and restart capabilities before committing and releasing locks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If concurrency control mechanisms are implemented to ensure data integrity and consistency, then transaction safety is improved, but system complexity increases
Solution Approach 1:
The patent segments transactions into two distinct levels: top-level transactions (SQL transactions) and nested transactions (SQL statements). This segmentation allows different concurrency control strategies to be applied at each level - locking at the nested level for fine-grained control and versioning at the top level for transactional consistency, thereby managing complexity through structured division of control mechanisms.
Solution Approach 2:
The patent introduces version identifiers as an intermediary mechanism that mediates between concurrent transactions. Each data item carries version information that acts as a mediator to detect and resolve conflicts without requiring direct locking between transactions, reducing the complexity of inter-transaction coordination while maintaining consistency.
2Reliability
If locking mechanisms are used for write operations to prevent deadlocks, then data consistency is improved, but transaction execution time increases
Solution Approach 1:
The patent applies preliminary locking at the nested transaction level before write operations are executed. By acquiring locks on individual data items at the statement level before committing the top-level transaction, the system prevents deadlock conditions early in the execution process, allowing faster commit times while maintaining consistency through pre-established lock ordering.
Solution Approach 2:
The patent implements local locking at the nested transaction level for specific data items rather than global locking. This allows fine-grained control where locks are applied only to the specific rows or columns being modified, minimizing the scope of locking and reducing the time transactions need to wait for lock availability, thereby improving execution speed while maintaining data consistency.
3Productivity
If multi-version concurrency control is applied to read operations to improve concurrency, then transaction throughput is improved, but memory usage increases
Solution Approach 1:
The patent implements multi-version concurrency control by creating version copies of data items when writes occur. Each write operation generates a new version of the affected data with an incremented version identifier, allowing multiple readers to access different versions simultaneously without blocking writers. This copying mechanism enables high read throughput by providing readers with isolated version snapshots while managing memory through selective version retention.
Data Source
AI summary
The subject technology inserts, by a first transaction, a new version of an object, the first transaction including a first statement to perform an update operation to a row in a first table, the object corresponding to data in the row to be updated, the first statement including information comprising an object key associated with the object. The subject technology performs, by a second transaction, a range read, the range read including information indicating the object key. The subject technology receives a set of conflicting transactions from the range read. The subject technology determines that a conflict occurred between the first transaction and a third transaction from the set of conflicting transactions. The subject technology performs a restart of the first transaction in response to determining that the conflict occurred.