Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Anomaly Pattern-guided Transaction Bug Testing in Relational Databases

Huicong Xu, Shuang Liu, Xianyu Zhu, Qiyu Zhuang, Wei Lu, Xiaoyong Du

TL;DR

This work tackles the challenge of testing relational database transactions under varying isolation levels by introducing APTrans, an anomaly-pattern-guided testing framework. APTrans uses high-level anomaly patterns to synthesize targeted, interdependent transactions and enforces a predefined schedule to trigger concurrency bugs, pairing this with a two-phase detection strategy (explicit and implicit) to identify both explicit failures and silent inconsistencies. The approach is realized in an extensible tool that extends SQLancer for join-based schemas and adds versioned data tracking via ROW ID and Version columns, plus triggers to log write results. Empirical evaluation across MySQL, MariaDB, and OceanBase uncovered 13 unique transaction bugs (11 confirmed, 10 first-time reports), outperforming state-of-the-art approaches (TxCheck and Troc) in bug-triggering efficacy and accuracy. The results underscore the value of anomaly-pattern-guided test case generation and highlight the practical impact for improving DBMS correctness, with APTrans available as an open-source resource for ongoing research and validation.

Abstract

Concurrent transaction processing is a fundamental capability of Relational Database Management Systems (RDBMSs), widely utilized in applications requiring high levels of parallel user interaction, such as banking systems, e-commerce platforms, and telecommunications infrastructure. Isolation levels offer a configurable mechanism to manage the interaction between concurrent transactions, enabling varying degrees of consistency and performance trade-offs. These isolation guarantees are supported by all major RDBMSs. However, testing transaction behavior under different isolation levels remains a significant challenge due to two primary reasons. First, automatically generating test transactions that can effectively expose bugs in transaction handling logic is non-trivial, as such bugs are typically triggered under specific transactional constraints. Second, detecting logic anomalies in transaction outcomes is difficult because the correct execution results are often unknown for randomly generated transactions. To address these challenges, we propose an anomaly pattern-guided testing approach for uncovering transaction bugs in RDBMSs. Our solution tackles the first challenge by introducing a test case generation technique guided by predefined anomaly patterns, which increases the likelihood of exposing transactional bugs. For the second challenge, we present a two-phase detection process, involving explicit error detection and implicit error detection, to identify bugs in transaction execution. We have implemented our approach in a tool, APTrans, and evaluated it on three widely-used RDBMSs: MySQL, MariaDB, and OceanBase. APTrans successfully identified 13 previously unknown transaction-related bugs, 11 of which have been confirmed by the respective development teams.

Anomaly Pattern-guided Transaction Bug Testing in Relational Databases

TL;DR

This work tackles the challenge of testing relational database transactions under varying isolation levels by introducing APTrans, an anomaly-pattern-guided testing framework. APTrans uses high-level anomaly patterns to synthesize targeted, interdependent transactions and enforces a predefined schedule to trigger concurrency bugs, pairing this with a two-phase detection strategy (explicit and implicit) to identify both explicit failures and silent inconsistencies. The approach is realized in an extensible tool that extends SQLancer for join-based schemas and adds versioned data tracking via ROW ID and Version columns, plus triggers to log write results. Empirical evaluation across MySQL, MariaDB, and OceanBase uncovered 13 unique transaction bugs (11 confirmed, 10 first-time reports), outperforming state-of-the-art approaches (TxCheck and Troc) in bug-triggering efficacy and accuracy. The results underscore the value of anomaly-pattern-guided test case generation and highlight the practical impact for improving DBMS correctness, with APTrans available as an open-source resource for ongoing research and validation.

Abstract

Concurrent transaction processing is a fundamental capability of Relational Database Management Systems (RDBMSs), widely utilized in applications requiring high levels of parallel user interaction, such as banking systems, e-commerce platforms, and telecommunications infrastructure. Isolation levels offer a configurable mechanism to manage the interaction between concurrent transactions, enabling varying degrees of consistency and performance trade-offs. These isolation guarantees are supported by all major RDBMSs. However, testing transaction behavior under different isolation levels remains a significant challenge due to two primary reasons. First, automatically generating test transactions that can effectively expose bugs in transaction handling logic is non-trivial, as such bugs are typically triggered under specific transactional constraints. Second, detecting logic anomalies in transaction outcomes is difficult because the correct execution results are often unknown for randomly generated transactions. To address these challenges, we propose an anomaly pattern-guided testing approach for uncovering transaction bugs in RDBMSs. Our solution tackles the first challenge by introducing a test case generation technique guided by predefined anomaly patterns, which increases the likelihood of exposing transactional bugs. For the second challenge, we present a two-phase detection process, involving explicit error detection and implicit error detection, to identify bugs in transaction execution. We have implemented our approach in a tool, APTrans, and evaluated it on three widely-used RDBMSs: MySQL, MariaDB, and OceanBase. APTrans successfully identified 13 previously unknown transaction-related bugs, 11 of which have been confirmed by the respective development teams.

Paper Structure

This paper contains 26 sections, 7 figures, 7 tables, 2 algorithms.

Table of Contents

  1. Introduction
  2. Preliminary
  3. Database Transactions and Isolation level
  4. Anomaly patterns
  5. Methodology
  6. Overview
  7. Database and SQL statement Generation
  8. Database Generation
  9. Statement Generation
  10. Pattern-guided Transaction Generation
  11. Constraint Extraction
  12. Transaction Generation.
  13. Execution
  14. Transaction Schedule
  15. Results Recording
  16. ...and 11 more sections

Figures (7)

  • Figure 1: Motivation example: a logic bug detected by APTrans in Mariadb (Bug#36330)
  • Figure 2: Overview of APTrans
  • Figure 3: A generated transaction guided by the lost update pattern ($R_1[x_0]W_2[x_1]C_2W_1[x_2]C_1$)
  • Figure 4: A MySQL trigger example of update statement
  • Figure 5: MySQL Bug#117835 reported at Read Committed
  • ...and 2 more figures

Theorems & Definitions (5)

  • definition 1: Anomaly Pattern
  • definition 2: Statement Type Constraint
  • definition 3: Data Access Constraint
  • definition 4: Schedule Order Constraint
  • definition 5: Dependency Graph