TXSQL: Lock Optimizations Towards High Contented Workloads (Extended Version)

2025-04-09

AI Summary

To address severe lock contention and waiting bottlenecks caused by strict Two-Phase Locking (2PL) under high-concurrency workloads, this paper proposes a hotspot-aware 2PL optimization framework. Our approach introduces two key innovations: (1) a novel hotspot-aware group locking mechanism that dynamically identifies hotspots and serializes conflicting transactions within groups, thereby eliminating hotspot lock contention entirely; and (2) a zero-copy active transaction list combined with fine-grained queued lock management to minimize lock operation overhead. Evaluated on Tencent’s production-grade high-contention workloads, our framework achieves up to 6.5× higher throughput than state-of-the-art 2PL optimizations and up to 22.3× improvement over mainstream database systems. It significantly reduces tail latency and substantially increases transactions per second (TPS), demonstrating both scalability and practical efficacy in real-world deployment scenarios.

Technology Category

• Search and Optimization: Distributed Search
• Planning, Routing, and Scheduling: Optimization of Spatio-temporal Systems
• Data Mining & Knowledge Management: Scalability, Parallel & Distributed Systems

Application Category

• Systems and Infrastructure for Web, Mobile and WoT: Data management and stream processing for Web, mobile and wireless applications
• Security and Privacy: Large-scale security measurements
• Search and Retrieval-Augmented AI: Web learning to rank, online learning, and counterfactual learning for ranking

Abstract

Two-phase locking (2PL) is a fundamental and widely used concurrency control protocol. It regulates concurrent access to database data by following a specific sequence of acquiring and releasing locks during transaction execution, thereby ensuring transaction isolation. However, in strict 2PL, transactions must wait for conflicting transactions to commit and release their locks, which reduces concurrency and system throughput. We have observed this issue is exacerbated in high-contented workloads at Tencent, where lock contention can severely degrade system performance. While existing optimizations demonstrate some effectiveness in high-contention scenarios, their performance remains insufficient, as they suffer from lock contention and waiting in hotspot access. This paper presents optimizations in lock management implemented in Tencent's database, TXSQL, with a particular focus on high-contention scenarios. First, we discuss our motivations and the journey toward general lock optimization, which includes lightweight lock management, a copy-free active transaction list, and queue locking mechanisms that effectively enhance concurrency. Second, we introduce a hotspot-aware approach that enables certain highly conflicting transactions to switch to a group locking method, which groups conflicting transactions at a specific hotspot, allowing them to execute serially in an uncommitted state within a conflict group without the need for locking, thereby reducing lock contention. Our evaluation shows that under high-contented workloads, TXSQL achieves performance improvements of up to 6.5x and up to 22.3x compared to state-of-the-art methods and systems, respectively.

Problem

• Optimizes lock management in high-contention database workloads
• Reduces lock contention via lightweight and group locking mechanisms
• Improves performance up to 22.3x in high-contented scenarios

Innovation

• Lightweight lock management enhances concurrency
• Copy-free active transaction list reduces overhead
• Hotspot-aware group locking minimizes lock contention