Sigmod 2025_TXSQL - Lock Optimizations Towards High Contented Workloads from Tencent_腾讯云.pdf

迹部景吾

14页

0次

2025-06-27

免费下载

TXSQL: Lock Optimizations Towards High Contented Workloads

Donghui Wang

Yuxing Chen*

Chengyao Jiang

Anqun Pan

Tencent Inc.

Shenzhen, China

{dorseywang,axingguchen,idavidjiang,

aaronpan}@tencent.com

Wei Jiang

Songli Wang

Hailin Lei

Chong Zhu

Lixiong Zheng

Tencent Inc.

Shenzhen, China

{vitalejiang,stanleewang,harlylei,

teddyzhu,paterzheng}@tencent.com

Wei Lu

Yunpeng Chai

Feng Zhang

Xiaoyong Du

Renmin University of China

Beijing, China

{lu-wei,ypchai,fengzhang,

duyong}@ruc.edu.cn

Abstract

Two-phase locking (2PL) is a fundamental and widely used con-

currency control protocol. It regulates concurrent access to data-

base data by following a specic sequence of acquiring and releas-

ing locks during transaction execution, thereby ensuring transac-

tion isolation. However, in strict 2PL, transactions must wait for

conicting 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 eectiveness in

high-contention scenarios, their performance remains insucient,

as they suer from lock contention and waiting in hotspot access.

This paper presents optimizations in lock management imple-

mented 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 light-

weight lock management, a copy-free active transaction list, and

queue locking mechanisms that eectively enhance concurrency.

Second, we introduce a hotspot-aware approach that enables cer-

tain highly conicting transactions to switch to a group locking

method, which groups conicting transactions at a specic hotspot,

allowing them to execute serially in an uncommitted state within a

conict group without the need for locking, thereby reducing lock

contention. Our evaluation shows that under high-contented work-

loads, TXSQL achieves performance improvements of up to 6.5x

and up to 22.3x compared to state-of-the-art methods and systems,

respectively.

CCS Concepts

• Information systems → Database transaction processing.

Keywords

Transaction; Two-phase locking; High-contented workloads

*Yuxing Chen is the corresponding author.

This work is licensed under a Creative Commons Attribution 4.0 International License.

SIGMOD-Companion ’25, Berlin, Germany

ACM ISBN 979-8-4007-1564-8/2025/06

https://doi.org/10.1145/3722212.3724457

ACM Reference Format:

Donghui Wang, Yuxing Chen, Chengyao Jiang, Anqun Pan, Wei Jiang, Songli

Wang, Hailin Lei, Chong Zhu, Lixiong Zheng, Wei Lu, Yunpeng Chai, Feng

Zhang, and Xiaoyong Du . 2025. TXSQL: Lock Optimizations Towards High

Contented Workloads. In Companion of the 2025 International Conference

on Management of Data (SIGMOD-Companion ’25), June 22–27, 2025, Berlin,

Germany. ACM, New York, NY, USA, 14 pages. https://doi.org/10.1145/

3722212.3724457

1 Introduction

In Tencent Cloud [

]’s Online Transaction Processing (OLTP) sce-

narios, databases typically experience low request loads most of the

time. During these periods, the volume of user requests is below the

system’s processing capacity. However, there are occasional brief

intervals characterized by sudden spikes in request trac. For in-

stance, in e-commerce, best-selling products or major promotional

events can lead to surges in user visits and transaction volumes.

These sudden spikes are often regarded as highly contented work-

loads, where the most frequently accessed data typically becomes

a hotspot. Although these workload transactions are typically short

[

], the system’s eorts to maintain transaction

isolation result in numerous lock requests and contention, which

negatively impacts performance [4, 33, 38].

Pessimistic concurrency control protocols, such as two-phase

locking (2PL) [

], are more eective for managing high-conict

transactions [

]. However, traditional 2PL protocols often strug-

gle to address scenarios involving hotspots (e.g., [

]). Conse-

quently, several enhancement strategies have been proposed to

mitigate these limitations. An intuitive approach is to reduce the

duration of lock holding. For instance, QURO [

] introduces a

commit-time-update mechanism that allows transactions to ac-

quire locks later, thereby minimizing lock holding time. Escrow

[

] is a locking mechanism that temporarily stores the access

rights to resources through a third-party intermediary to reduce

lock contentions. Similarly, Bamboo [

] proposes a violation of

the 2PL protocol by releasing locks earlier to achieve the same goal.

While these methods demonstrate some eectiveness in conicted

workloads, they cannot eliminate lock acquisitions and contention

during hotspot access. In contrast, deterministic protocols (e.g.,

[

]) can eectively eliminate locking by scheduling transac-

tions in a conict-free manner. However, they face limitations due

to scheduling overhead and the requirement for pre-acquisition of

read-write sets, which are generally considered impractical [21].

675

SIGMOD-Companion ’25, June 22–27, 2025, Berlin, Germany Donghui Wang et al.

Most academic implementations of these novel protocols are

found in in-memory database prototypes (e.g., DBX1000 [

]), while

industry practitioners tend to be cautious about modifying concur-

rency control protocols. For example, systems like MySQL and SQL

Server rely on traditional 2PL and multi-version concurrency con-

trol (MVCC) [

], or a combination of both. Also, rather than altering

the transaction layer to mitigate hotspot workloads, they often fo-

cus on modifying the application layer. For instance, most of our

Tencent Cloud database instances implement request restrictions at

the application layer to prevent sudden spikes in hotspot requests

(for further details, see Section 4.6). Additionally, some databases

employ proactive hotspot identication and SQL rewriting [

] at

the application layer to address ad-hoc high-contented workloads

(e.g., [

]). However, these implementations may not necessarily

represent the most eective solutions for managing hotspot access

in commercial databases, and in certain dynamic scenarios, they

may not be applicable at all.

In certain high-contented workloads at Tencent, we have found

that request restrictions can eectively mitigate excessive lock

contention. However, this approach is less eective in dynamic

hotspot situations (see Section 2.3), where transactions concentrate

on updating one or a few data items. In these cases, executing trans-

actions serially may yield better performance by eliminating lock

contention and associated overhead. To oer a practical solution for

managing hotspots, this paper provides insights into lock optimiza-

tion within Tencent Database TXSQL [

]. First, we discuss our

motivations and the overarching lock optimization process, includ-

ing lightweight lock management, a lock-free active transaction

list, and a queue lock mechanism. These optimizations enhance

concurrency and improve performance in typical high-contention

scenarios. Secondly, we place particular emphasis on its optimiza-

tions for hotspot situations. Specically, TXSQL is designed to meet

the following two criteria for concurrency control when addressing

hotspots: (1) the protocol is workload-agnostic and does not inter-

fere with existing application logic; and (2) the protocol maintains

performance when concurrency increases.

Inspired by the benets of deterministic protocols in scheduling

high-conicted transactions, we propose a group locking mecha-

nism within the 2PL protocol framework for managing hotspots.

Unlike traditional 2PL, which treats all data uniformly, our ap-

proach distinguishes between hotspot and non-hotspot data. We

group transactions updating hotspot data, allowing them to proceed

without locking, as long as they adhere to a total order. When there

are no hotspot updates, TXSQL reverts to traditional 2PL, ensuring

minimal impact on overall throughput. Our main contributions are

as follows:

•

We provide motivation, optimizations, and insights in TXSQL

to address lock conict issues.

•

When involving hotspot data, we propose a group locking

mechanism that groups hotspot data accesses and executes

them serially without locking. Also, we describe its correct-

ness in terms of deadlock, rollback, and failure recovery.

•

Compared to state-of-the-art methods and systems, evalu-

ation shows that our approach achieves performance im-

provements of up to 6.5x and 22.3x, respectively.

Connection Pool

Parser Optimizer

Server Engine InnoDB-like Storage Engine

Trx Coordinate Log

Execution Engine

Trx Manager

Active trx list

trx_start

lock/unlock

trx_commit

trx_rollback

Lock Manager

Lock hash table

lock_rec_lock

unlock_rec_lock

Log BufferBuffer Pool

Binlog Cache

Redo Log FilesUndo TablespaceData TablespacesBinary Log Files

Disk Files & Logs

Figure 1: The core architecture of TXSQL.

2 Preliminary

This section introduces the basic 2PL design, as well as TXSQL’s

system architecture and its applications.

2.1 Two-Phase Locking (2PL)

The 2PL protocol, widely implemented in many database man-

agement systems, is designed to prevent data conicts between

concurrent transactions. Transaction execution is divided into two

distinct phases: the growing phase and the shrinking phase. In the

growing phase, a transaction can acquire locks and access data but

is prohibited from releasing any locks. In the shrinking phase, a

transaction may release locks but cannot acquire any new locks.

The 2PL ensures transactions do not conict with one another

during execution, thereby maintaining transaction isolation.

Mutual Exclusion (Mutex) is a synchronization mechanism used

in multithreaded programming, designed to prevent multiple threads

from simultaneously accessing shared resources, thereby avoiding

data races and inconsistencies. A mutex ensures that only one

thread can access a specic resource or code segment at any given

time. It is commonly utilized in 2PL lock management.

2.2 Transaction Execution Workow

TXSQL [

], an open-source MySQL branch maintained by Tencent

Cloud, is fully compatible with MySQL’s syntax and APIs. Figure 1

depicts the core system architecture of TXSQL. The primary opti-

mizations discussed in this paper focus on the transaction manager

and the lock manager at the Storage layer. The transaction man-

ager is responsible for the lifecycle of transactions, including their

initiation, execution, commit, and rollback. It ensures the atomicity

of transactions, meaning that they either complete successfully in

their entirety or do not execute at all. In contrast, the lock manager

implements a locking mechanism to regulate access to database

resources during transaction execution, such as rows and tables.

When a transaction initiates a row update in the transaction

manager, the specic row can be uniquely identied by locating the

record’s associated tablespace through the

𝑠𝑝𝑎𝑐𝑒_𝑖𝑑

, identifying the

page containing the record via the

𝑝𝑎𝑔𝑒_𝑛𝑜

, and pinpointing the

exact position of the record within a page using the

ℎ𝑒𝑎𝑝_𝑛𝑜

. There-

fore, the combination of <

𝑠𝑝𝑎𝑐𝑒_𝑖𝑑

𝑝𝑎𝑔𝑒_𝑛𝑜

ℎ𝑒𝑎𝑝_𝑛𝑜

> serves as a

unique identier for a row. Once the corresponding record is located,

676

of 14

免费下载

文档被以下合辑收录

数据库顶会 SIGMOD 2025 论文下载（共12篇）

数据库顶会 SIGMOD 2025 论文下载

关注

文档被以下合辑收录

评论