Detecting Isolation Anomalies in Relational DBMSs

Relational Database Management Systems (DBMSs) utilize transactions to ensure data consistency and integrity, while providing multiple isolation levels to strike a balance between consistency and performance. However, isolation anomalies in relational DBMSs can undermine their claimed isolation leve...

Full description

Saved in:
Bibliographic Details
Published in:Proceedings of the ACM on software engineering Vol. 2; no. ISSTA; pp. 1725 - 1747
Main Authors: Yang, Rui, Cui, Ziyu, Dou, Wensheng, Gao, Yu, Song, Jiansen, Xie, Xudong, Wei, Jun
Format: Journal Article
Language:English
Published: New York, NY, USA ACM 22.06.2025
Subjects:
Software and its engineering
Software testing and debugging
isolation level
Database transaction
isolation anomaly
ISSN:2994-970X, 2994-970X
Online Access:Get full text
Tags: Add Tag
No Tags, Be the first to tag this record!
Description
Summary:Relational Database Management Systems (DBMSs) utilize transactions to ensure data consistency and integrity, while providing multiple isolation levels to strike a balance between consistency and performance. However, isolation anomalies in relational DBMSs can undermine their claimed isolation levels, and lead to severe consequences, e.g., incorrect query results and database states. Existing isolation checkers can only work on simple key-value-like data models and the associated read(key) and write(key,value) operations. Therefore, they cannot be directly applied to relational DBMSs that support relational data models and complex SQL operations. In this paper, we propose a novel black-box Isolation checker for Relational DBMSs, IsoRel, which can support relational data models and complex SQL operations. To infer dependencies among transactions in relational DBMSs, we first design an isolation-agnostic SQL statement instrumentation approach to record the data rows accessed by each SQL statement by utilizing two auxiliary columns in each database table. We then utilize the recorded data rows of each SQL statement to construct a transaction dependency graph for relational transactions, and identify isolation anomalies based on anomaly patterns. We evaluate IsoRel on five widely-used relational DBMSs, i.e., MySQL, PostgreSQL, MariaDB, CockroachDB, and TiDB, and all their supported isolation levels. Our evaluation reveals a total of 48 unique isolation anomalies that violate the isolation levels defined by Adya.
ISSN:2994-970X
2994-970X
DOI:10.1145/3728953