Distributed SQL Transaction Coordination Through an Access Service
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In distributed computing environments, ensuring atomicity of database transactions across multiple compute instances and connections is challenging due to the inability of the database service to group and track operations as a single atomic transaction.
Innovation Solution
An access service is positioned between container groups and a SQL database service, creating an entry in a staging database to track SQL transactions. Multiple connections from container groups to the access service allow caching of SQL operation details in the staging database. Upon committing the transaction, a single connection is established between the access service and the SQL database service to commit the transaction as an atomic unit.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If multiple connections are established from container groups to the SQL database service, then the system can handle distributed transactions across multiple compute instances, but the database service cannot group and track operations as a single atomic transaction
Solution Approach 1:
The access service acts as an intermediary layer between container groups and the SQL database service. It receives transaction operations from multiple container groups via multiple connections, groups them by transaction ID, and manages their atomic execution. This intermediary resolves the contradiction by enabling distributed transaction handling while maintaining atomicity guarantees through centralized transaction coordination.
Solution Approach 2:
The system segments the transaction handling into two distinct layers: the access service layer that manages transaction grouping and coordination, and the database service layer that executes individual SQL operations. This segmentation allows the database service to focus on reliable atomic execution of individual operations while the access service handles the complexity of distributed transaction coordination across multiple connections.
2Reliability
If a single connection is used between access service and SQL database service, then atomic transactions can be committed reliably, but multiple container groups cannot simultaneously send transaction operations
Solution Approach 1:
The access service serves as a mediator that accepts concurrent connections from multiple container groups and buffers their transaction operations. It then serializes these operations and commits them through a single connection to the database service as atomic transactions. This resolves the contradiction by decoupling the concurrent access interface from the atomic commit mechanism.
Solution Approach 2:
The access service performs preliminary actions by receiving, validating, and buffering transaction operations from multiple container groups before committing them to the database. It groups operations by transaction ID and prepares them for atomic execution, ensuring that all operations are ready to be committed together through the single connection to the database service.
Data Source
AI summary
An access service is positioned between a set of container groups and a SQL database service. Upon receipt, at the access service, of a REST request from any container group indicating that a SQL transaction should be started, an entry is created in a staging database, separate from the SQL database service, to track the SQL transaction. Multiple connections are allowed to be established from the container groups to the access service over which other REST requests from any container group are sent. The other REST requests include details of SQL operations to be performed as part of the SQL transaction. The details are cached in the staging database. Upon receiving a REST request from any container group indicating that the SQL transaction should be committed, a single connection is established between the access service and SQL database service to commit the transaction, including the details, as an atomic transaction.