Distributed SQL Transaction Coordination to Reduce 2PC Blocking

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

The two-phase commit (2PC) protocol in distributed databases leads to blocking and reduced transaction processing capability due to the lack of mutual communication between slave nodes, causing them to wait for a transaction coordinator recovery, which results in locked resources not being released.

Innovation Solution

A method and system that decomposes SQL requests into sub-SQL requests, allowing slave nodes to process and transmit execution status information independently, enabling them to make decisions and reduce blocking by implementing a decentralized transaction management system.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If the two-phase commit protocol is used with a centralized transaction coordinator, then transaction consistency is maintained, but system blocking increases and transaction processing capability decreases due to slave nodes waiting for coordinator recovery

Engineering Contradiction:

Improvetransaction consistencyVSAvoidtransaction processing capability

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The patent segments the centralized transaction coordinator into multiple distributed coordinators. Each slave node can communicate with multiple coordinators, and transaction coordination functionality is distributed across the network rather than concentrated in a single point. This segmentation eliminates the single point of failure and reduces blocking when a coordinator is unavailable.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent introduces intermediary mechanisms where slave nodes can exchange information directly with each other and with multiple potential coordinators. When one coordinator is unavailable, slave nodes can use alternative coordinators as intermediaries to complete transaction coordination, preventing system-wide blocking.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Reliability

If slave nodes wait for transaction coordinator recovery in the 2PC protocol, then transaction consistency is ensured, but locked resources cannot be released and system responsiveness decreases

Engineering Contradiction:

Improvetransaction consistencyVSAvoidresource locking duration

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The patent implements preliminary actions where slave nodes proactively seek alternative coordinators and prepare for coordinator failure before it occurs. When coordinator unavailability is detected, slave nodes have pre-established alternative communication paths and can immediately switch to backup coordinators, reducing the time resources remain locked.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent makes the coordinator selection dynamic rather than static. Slave nodes can dynamically switch between different coordinators based on availability, and the system adapts coordinator assignments in real-time. This dynamic behavior allows the system to respond quickly to coordinator failures and release resources faster.

Inventive Principle:

Principle #15Dynamics

3Device complexity

If a centralized transaction coordinator manages all slave nodes, then transaction coordination is simplified, but system complexity increases and single point of failure risk increases

Engineering Contradiction:

Improvetransaction coordination structureVSAvoidsystem availability

Core Design Contradiction:

Device complexityVSReliability

Solution Approach 1:

The patent divides the centralized coordinator into multiple distributed coordinator instances. Each coordinator handles a subset of slave nodes or specific transaction types, distributing the coordination load and eliminating the single point of failure. The segmented architecture maintains coordination simplicity through modular design while improving reliability.

Inventive Principle:

Principle #1Segmentation

4Device complexity

If slave nodes lack mutual communication capability in 2PC, then protocol implementation is simpler, but blocking increases when coordinator is unavailable

Engineering Contradiction:

Improveprotocol implementationVSAvoidtransaction processing capability

Core Design Contradiction:

Device complexityVSProductivity

Solution Approach 1:

The patent introduces direct communication channels between slave nodes as intermediaries. When the primary coordinator is unavailable, slave nodes can communicate directly with each other to exchange transaction status information and coordinate commits or rollbacks, reducing blocking without significantly complicating the protocol implementation.

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS12386819B2Method and system for transaction management, network device, and readable storage medium

Publication Date: 2025.08.12 ZTE CORP

AI summary

Disclosed are a method and system for transaction management, a network device, and a readable storage medium. The method includes the following. A coordinating node receives a structured query language (SQL) request externally initiated, where the SQL request is forwarded by a master node. The coordinating node decomposes the SQL request into sub-SQL (SubSQL) requests, and transmits each of the SubSQL requests to a corresponding slave node. Each slave node receives and processes a corresponding SubSQL request, transmits execution status information of the corresponding SubSQL request to the coordinating node and/or other slave nodes, and receives execution status information in processing respective SubSQL requests by the other slave nodes. Each particular slave node of one or more of the slave nodes feeds back a current local transaction status set of the particular slave node according to status query information received from the master node or the coordinating node.