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 levels, and lead to severe consequences, e.g., incorrect query results and database states. Existing isolation checkers can only work on simple 𝑘𝑒𝑦-𝑣𝑎𝑙𝑢𝑒-like data models and the associated 𝑟𝑒𝑎𝑑 (𝑘𝑒𝑦) and 𝑤𝑟𝑖𝑡𝑒 (𝑘𝑒𝑦, 𝑣𝑎𝑙𝑢𝑒) 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.

CCS Concepts: • Software and its engineering → Software testing and debugging.