research-article

Detecting Isolation Bugs via Transaction Oracle Construction

Authors:

Tao HuangAuthors Info & Claims

ICSE '23: Proceedings of the 45th International Conference on Software Engineering

Pages 1123 - 1135

https://doi.org/10.1109/ICSE48619.2023.00101

Published: 26 July 2023 Publication History

Abstract

Transactions are used to maintain the data integrity of databases, and have become an indispensable feature in modern Database Management Systems (DBMSs). Despite extensive efforts in testing DBMSs and verifying transaction processing mechanisms, isolation bugs still exist in widely-used DBMSs when these DBMSs violate their claimed transaction isolation levels. Isolation bugs can cause severe consequences, e.g., incorrect query results and database states.

In this paper, we propose a novel transaction testing approach, Transaction oracle construction (Troc), to automatically detect isolation bugs in DBMSs. The core idea of Troc is to decouple a transaction into independent statements, and execute them on their own database views, which are constructed under the guidance of the claimed transaction isolation level. Any divergence between the actual transaction execution and the independent statement execution indicates an isolation bug. We implement and evaluate Troc on three widely-used DBMSs, i.e., MySQL, MariaDB, and TiDB. We have detected 5 previously-unknown isolation bugs in the latest versions of these DBMSs.

References

[1]

(2022) MySQL. [Online]. Available: https://www.mysql.com

[2]

(2022) MariaDB. [Online]. Available: https://mariadb.org

[3]

(2022) PostgreSQL. [Online]. Available: https://www.postgresql.org

[4]

(2022) TiDB, PingCAP. [Online]. Available: https://pingcap.com

[5]

(2022) CockroachDB. [Online]. Available: https://www.cockroachlabs.com/

[6]

D. D. Chamberlin and R. F. Boyce, "SEQUEL: A structured english query language," in Proceedings of ACM SIGFIDET Workshop on Data Description, Access and Control (SIGFIDET), 1974, pp. 249--264.

[7]

P. A. Bernstein, V. Hadzilacos, and N. Goodman, Concurrency Control and Recovery in Database Systems. Addison-Wesley Longman Publishing Co., Inc., 1986.

Digital Library

[8]

P. M. Lewis, A. Bernstein, and M. Kifer, "Databases and transaction processing: An application-oriented approach," ACM SIGMOD Record, vol. 31, no. 1, pp. 74--75, 2002.

Digital Library

[9]

(2022) The ANSI isolation levels. [Online]. Available: http://www.adp-gmbh.ch/ora/misc/isolationlevel.html

[10]

H. Berenson, P. Bernstein, J. Gray, J. Melton, E. O'Neil, and P. O'Neil, "A critique of ANSI SQL isolation levels," in Proceedings of ACM SIGMOD International Conference on Management of Data (SIGMOD), 1995, pp. 1--10.

[11]

P. Bailis, A. Davidson, A. Fekete, A. Ghodsi, J. M. Hellerstein, and I. Stoica, "Highly available transactions: Virtues and limitations," Proceedings of the VLDB Endowment (VLDB), vol. 7, no. 3, pp. 181--192, 2013.

Digital Library

[12]

D. Potier and P. Leblanc, "Analysis of locking policies in database management systems," Communications of the ACM, vol. 23, no. 10, pp. 584--593, 1980.

Digital Library

[13]

S. Sippu and E. Soisalon-Soininen, Lock-Based Concurrency Control, 2014, pp. 125--158.

[14]

D. P. Reed, "Naming and synchronization in a decentralized computer system," Tech. Rep., 1978.

[15]

P. A. Bernstein and N. Goodman, "Multiversion concurrency control---theory and algorithms," ACM Transactions on Database Systems (TODS), vol. 8, no. 4, pp. 465--483, 1983.

Digital Library

[16]

M. J. Carey, "Improving the performance of an optimistic concurrency control algorithm through timestamps and versions," IEEE Transactions on Software Engineering (TSE), vol. SE-13, no. 6, pp. 746--751, 1987.

Digital Library

[17]

X. Song and J. W.-S. Liu, "Performance of multiversion concurrency control algorithms in maintaining temporal consistency," in Proceedings of Annual International Computer Software and Applications Conference (COMPSAC), 1990, pp. 132--139.

[18]

H.-T. Kung and J. T. Robinson, "On optimistic methods for concurrency control," ACM Transactions on Database Systems (TODS), vol. 6, no. 2, pp. 213--226, 1981.

Digital Library

[19]

X. Yu, A. Pavlo, D. Sanchez, and S. Devadas, "TicToc: Time traveling optimistic concurrency control," in Proceedings of International Conference on Management of Data (SIGMOD), 2016, pp. 1629--1642.

[20]

(2022) Hermitage. [Online]. Available: https://github.com/ept/hermitage

[21]

R. Biswas and C. Enea, "On the complexity of checking transactional consistency," in Proceedings of ACM SIGPLAN Conference on Object-Oriented Programming Systems, Languages, and Applications (OOP-SLA), 2019, pp. 165:1--165:28.

[22]

C. Tan, C. Zhao, S. Mu, and M. Walfish, "Cobra: Making transactional key-value stores verifiably serializable," in Proceedings of USENIX Symposium on Operating Systems Design and Implementation (OSDI), 2020, pp. 63--80.

[23]

K. Kingsbury and P. Alvaro, "Elle: Inferring isolation anomalies from experimental observations," Proceedings of the VLDB Endowment (VLDB), vol. 14, no. 3, pp. 268--280, 2020.

Digital Library

[24]

(2022) SQLsmith. [Online]. Available: https://github.com/anse1/sqlsmith

[25]

D. R. Slutz, "Massive stochastic testing of SQL," in Proceedings of International Conference on Very Large Data Bases (VLDB), 1998, pp. 618--622.

[26]

M. Rigger and Z. Su, "Testing database engines via pivoted query synthesis," in Proceedings of USENIX Symposium on Operating Systems Design and Implementation (OSDI), 2020, pp. 667--682.

[27]

M. Rigger and Z. Su, "Finding bugs in database systems via query partitioning," Proceedings of the ACM on Programming Languages, vol. 4, no. OOPSLA, pp. 211:1--211:30, 2020.

[28]

M. Rigger and Z. Su, "Detecting optimization bugs in database engines via non-optimizing reference engine construction," in Proceedings of ACM Joint European Software Engineering Conference and Symposium on the Foundations of Software Engineering (ESEC/FSE), 2020, pp. 1140--1152.

[29]

(2022) Inconsistent behaviors of UPDATE under Read Uncommitted. [Online]. Available: https://bugs.mysql.com/bug.php?id=104833

[30]

E. F. Codd, "A relational model of data for large shared data banks," Communications of the ACM, vol. 13, no. 6, pp. 377--387, 1970.

Digital Library

[31]

(2022) DB-Engines. [Online]. Available: https://db-engines.com/en/ranking

[32]

(2022) GitHub. [Online]. Available: https://github.com/

[33]

A. Adya, "Weak consistency: A generalized theory and optimistic implementations for distributed transactions," Ph.D. dissertation, Massachusetts Institute of Technology, 1999.

Digital Library

[34]

A. Adya, B. Liskov, and P. O'Neil, "Generalized isolation level definitions," in Proceedings of International Conference on Data Engineering (ICDE), 2000, pp. 67--78.

[35]

A. Cerone, G. Bernardi, and A. Gotsman, "A framework for transactional consistency models with atomic visibility," in Proceedings of International Conference on Concurrency Theory (CONCUR), 2015, pp. 58--71.

[36]

P. Bailis, A. Fekete, A. Ghodsi, J. M. Hellerstein, and I. Stoica, "Scalable atomic visibility with RAMP transactions," ACM Transactions on Database Systems (TODS), vol. 41, no. 3, pp. 15:1--15:45, 2016.

Digital Library

[37]

L. Brutschy, D. Dimitrov, P. Müller, and M. Vechev, "Serializability for eventual consistency: Criterion, analysis, and applications," in Proceedings of ACM SIGPLAN Symposium on Principles of Programming Languages (POPL), 2017, pp. 458--472.

[38]

(2022) MySQL isolation. [Online]. Available: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

[39]

(2022) Isolation levels in MariaDB. [Online]. Available: https://mariadb.com/kb/en/set-transaction/

[40]

(2022) TiDB Isolation. [Online]. Available: https://docs.pingcap.com/tidb/v5.0/transaction-isolation-levels

[41]

C. Binnig, D. Kossmann, E. Lo, and M. T. Özsu, "QAGen: Generating query-aware test databases," in Proceedings of ACM SIGMOD International Conference on Management of Data (SIGMOD), 2007, pp. 341--352.

[42]

N. Bruno and S. Chaudhuri, "Flexible database generators," in Proceedings of International Conference on Very Large Data Bases (VLDB), 2005, pp. 1097--1107.

[43]

E. F. Codd, "Relational completeness of data base sublanguages," Research Report, 1972.

[44]

J. Gray, P. Sundaresan, S. Englert, K. Baclawski, and P. J. Weinberger, "Quickly generating billion-record synthetic databases," in Proceedings of ACM SIGMOD International Conference on Management of Data (SIGMOD), 1994, pp. 243--252.

[45]

K. Houkjær, K. Torp, and R. Wind, "Simple and realistic data generation," in Proceedings of International Conference on Very Large Data Bases (VLDB), 2006, pp. 1243--1246.

[46]

S. A. Khalek, B. Elkarablieh, Y. O. Laleye, and S. Khurshid, "Query-aware test generation using a relational constraint solver," in Proceedings of IEEE/ACM International Conference on Automated Software Engineering (ASE), 2008, pp. 238--247.

[47]

(2022) go-randgen. [Online]. Available: https://github.com/pingcap/go-randgen

[48]

R. Zhong, Y. Chen, H. Hu, H. Zhang, W. Lee, and D. Wu, "SQUIRREL: Testing database management systems with language validity and coverage feedback," in Proceedings of ACM SIGSAC Conference on Computer and Communications Security (CCS), 2020, pp. 58--71.

[49]

(2022) SQLancer. [Online]. Available: https://www.manuelrigger.at/dbms-bugs/

[50]

(2022) InnoDB transaction model. [Online]. Available: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html

[51]

J. N. Gray, R. A. Lorie, and G. R. Putzolu, "Granularity of locks in a shared data base," in Proceedings of International Conference on Very Large Data Bases (VLDB), 1975, pp. 428--451.

[52]

(2022) Next-key locking in MySQL. [Online]. Available: https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

[53]

(2022) Execution plan information in MySQL. [Online]. Available: https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html

[54]

(2022) DELETE fails to delete record after blocking is released. [Online]. Available: https://jira.mariadb.org/browse/MDEV-27992

[55]

(2022) Weird SELECT view when a record is modified to a same value by two transactions. [Online]. Available: https://jira.mariadb.org/browse/MDEV-26642

[56]

(2022) Inconsistent behaviors of UPDATE under RU & RC isolation level. [Online]. Available: https://jira.mariadb.org/browse/MDEV-26643

[57]

(2022) Weird SELECT view when a record is modified to the same value by two transactions. [Online]. Available: https://github.com/pingcap/tidb/issues/28212

[58]

(2022) UPDATE has inconsistent behaviors in a transaction. [Online]. Available: https://github.com/pingcap/tidb/issues/28092

[59]

(2022) UPDATE with CAST has inconsistent behaviors in transaction. [Online]. Available: https://github.com/pingcap/tidb/issues/28095

[60]

(2022) Isolation levles - IBM documentation. [Online]. Available: https://www.ibm.com/docs/en/db2/10.5?topic=issues-isolation-levels

[61]

J. Jung, H. Hu, J. Arulraj, T. Kim, and W. Kang, "APOLLO: Automatic detection and diagnosis of performance regressions in database systems," Proceedings of the VLDB Endowment (VLDB), vol. 13, no. 1, pp. 57--70, 2019.

Digital Library

[62]

M. Wang, Z. Wu, X. Xu, J. Liang, C. Zhou, H. Zhang, and Y. Jiang, "Industry practice of coverage-guided enterprise-level DBMS fuzzing," in Proceedings of International Conference on Software Engineering: Software Engineering in Practice (ICSE SEIP), 2021, pp. 328--337.

[63]

X. Liu, Q. Zhou, J. Arulraj, and A. Orso, "Automatic detection of performance bugs in database systems using equivalent queries," in Proceedings of IEEE/ACM SIGSOFT International Conference on Software Engineering (ICSE), 2022, pp. 225--236.

[64]

Y. Zheng, W. Dou, Y. Wang, Z. Qin, L. Tang, Y. Gao, D. Wang, W. Wang, and J. Wei, "Finding bugs in Gremlin-based graph database systems via randomized differential testing," in Proceedings of ACM SIGSOFT International Symposium on Software Testing and Analysis (ISSTA), 2022, pp. 302--313.

[65]

Z. Cui, W. Dou, Q. Dai, J. Song, W. Wang, J. Wei, and D. Ye, "Differentially testing database transactions for fun and profit," in Proceedings of IEEE/ACM International Conference on Automated Software Engineering (ASE), 2022, pp. 35:1--35:12.

[66]

Y. Liang, S. Liu, and H. Hu, "Detecting logical bugs of DBMS with coverage-based guidance," in Proceedings of USENIX Security Symposium (USENIX Security), 2022.

[67]

J. Song, W. Dou, Z. Cui, Q. Dai, W. Wang, J. Wei, H. Zhong, and T. Huang, "Testing database systems via differential query execution," in Proceedings of IEEE/ACM International Conference on Software Engineering (ICSE), 2023.

[68]

R. Yang, Y. Zheng, L. Tang, W. Dou, W. Wang, and J. Wei, "Randomized differential testing of RDF stores," in Proceedings of IEEE/ACM International Conference on Software Engineering (ICSE Demo), 2023.

[69]

J. Ba and M. Rigger, "Testing database engines via query plan guidance," in Proceedings of IEEE/ACM International Conference on Software Engineering (ICSE), 2023.

[70]

M. Kamm, M. Rigger, C. Zhang, and Z. Su, "Testing graph database engines via query partitioning," in Proceedings of ACM SIGSOFT International Symposium on Software Testing and Analysis (ISSTA), 2023.

[71]

J. Liang, Y. Chen, Z. Wu, J. Fu, M. Wang, Y. Jiang, X. Huang, T. Chen, J. Wang, and J. Li, "Sequence-oriented DBMS fuzzing," in Proceedings of IEEE International Conference on Data Engineering (ICDE), 2023.

[72]

Z.-M. Jiang, J.-J. Bai, and Z. Su, "DynSQL: Stateful fuzzing for database management systems with complex and valid SQL query generation," in Proceedings of USENIX Security Symposium (USENIX Security), 2023.

[73]

Z. Hua, W. Lin, L. Ren, Z. Li, L. Zhang, W. Jiao, and T. Xie, "GDsmith: Detecting bugs in Cypher graph database engines," in Proceedings of ACM SIGSOFT International Symposium on Software Testing and Analysis (ISSTA), 2023.

[74]

W. Lin, Z. Hua, L. Zhang, and T. Xie, "GDiff: Automated differential performance testing for graph database systems," in Proceedings of IEEE/ACM International Conference on Software Engineering (ICSE), 2023.

[75]

Y. Deng, P. Frankl, and Z. Chen, "Testing database transaction concurrency," in Proceedings of IEEE International Conference on Automated Software Engineering (ASE), 2003, pp. 184--193.

[76]

H. Luo, M. Masud, and H. Ural, "Detecting offline transaction concurrency problems," Journal of Software, vol. 7, pp. 1855--1860, 2012.

[77]

L. Brutschy, D. Dimitrov, P. Müller, and M. Vechev, "Static serializability analysis for causal consistency," in Proceedings of SIGPLAN Conference on Programming Language Design and Implementation (PLDI), 2018, pp. 90--104.

[78]

K. Rahmani, K. Nagar, B. Delaware, and S. Jagannathan, "CLOTHO: Directed test generation for weakly consistent database systems," Proceedings of the ACM on Programming Languages, vol. 3, no. OOPSLA, pp. 117:1--117:28, 2019.

[79]

Y. Gan, X. Ren, D. Ripberger, S. Blanas, and Y. Wang, "IsoDiff: Debugging anomalies caused by weak isolation," Proceedings of the VLDB Endowment (VLDB), vol. 13, no. 12, pp. 2773--2786, 2020.

Digital Library

[80]

R. Biswas, D. Kakwani, J. Vedurada, C. Enea, and A. Lal, "MonkeyDB: Effectively testing correctness under weak isolation levels," Proceedings of the ACM on Programming Languages, vol. 5, no. OOPSLA, pp. 132:1--132:27, 2021.

[81]

C. Tang, Z. Wang, X. Zhang, Q. Yu, B. Zang, H. Guan, and H. Chen, "Ad hoc transactions in web applications: The good, the bad, and the ugly," in Proceedings of International Conference on Management of Data (SIGMOD), 2022, pp. 4--18.

Cited By

Song JDou WGao YCui ZZheng YWang DWang WWei JHuang T(2024)Detecting Metadata-Related Logic Bugs in Database Systems via Raw Database ConstructionProceedings of the VLDB Endowment10.14778/3659437.365944517:8(1884-1897)Online publication date: 1-Apr-2024
https://dl.acm.org/doi/10.14778/3659437.3659445
Zheng YDou WTang LCui ZGao YSong JXu LZhu JWang WWei JZhong HHuang TChristakis MPradel M(2024)Testing Gremlin-Based Graph Database Systems via Query DisassemblingProceedings of the 33rd ACM SIGSOFT International Symposium on Software Testing and Analysis10.1145/3650212.3680392(1695-1707)Online publication date: 11-Sep-2024
https://dl.acm.org/doi/10.1145/3650212.3680392
Cui ZDou WGao YWang DSong JZheng YWang TYang RXu KHu YWei JHuang TRoychoudhury APaiva AAbreu RStorey M(2024)Understanding Transaction Bugs in Database SystemsProceedings of the IEEE/ACM 46th International Conference on Software Engineering10.1145/3597503.3639207(1-13)Online publication date: 20-May-2024
https://dl.acm.org/doi/10.1145/3597503.3639207

Recommendations

Understanding Transaction Bugs in Database Systems
ICSE '24: Proceedings of the IEEE/ACM 46th International Conference on Software Engineering

Transactions are used to guarantee data consistency and integrity in Database Management Systems (DBMSs), and have become an indispensable component in DBMSs. However, faulty designs and implementations of DBMSs' transaction processing mechanisms can ...
Transaction Isolation in Mixed-Level and Mixed-Scope Settings
Advances in Databases and Information Systems
Abstract
Modern database-management systems permit the isolation level to be set on a per-transaction basis. In such a mixed-level setting, it is important to understand how transactions running at different levels interact. More fundamentally however, ...
Towards a universal construction for transaction-based multiprocess programs

The aim of a Software Transactional Memory (STM) system is to discharge the programmer from the explicit management of synchronization issues. The programmer's job resides in the design of multiprocess programs in which processes are made up of ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

ICSE '23: Proceedings of the 45th International Conference on Software Engineering

May 2023

2713 pages

ISBN:9781665457019

General Chair:
John Grundy
Department of Software Systems and Cybersecurity, Faculty of IT, Monash University, Australia
,
Program Co-chairs:
Lori Pollock
University of Delaware, DE, USA
,
Massimiliano Di Penta
University of Sannio, Italy

Sponsors

SIGSOFT: ACM Special Interest Group on Software Engineering

In-Cooperation

IEEE CS

Publisher

IEEE Press

Publication History

Published: 26 July 2023

Check for updates

Badges

Author Tags

Qualifiers

Research-article

Conference

ICSE '23

Sponsor:

SIGSOFT

ICSE '23: 45th International Conference on Software Engineering

May 14 - 20, 2023

Victoria, Melbourne, Australia

Acceptance Rates

Overall Acceptance Rate 276 of 1,856 submissions, 15%

Upcoming Conference

ICSE 2025

2025 IEEE/ACM 46th International Conference on Software Engineering

April 26 - May 3, 2025

Ottawa , ON , Canada

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

3
Total Citations
View Citations
91
Total Downloads

Downloads (Last 12 months)60
Downloads (Last 6 weeks)5

Reflects downloads up to 01 Jan 2025

Other Metrics

View Author Metrics

Citations

Cited By

Song JDou WGao YCui ZZheng YWang DWang WWei JHuang T(2024)Detecting Metadata-Related Logic Bugs in Database Systems via Raw Database ConstructionProceedings of the VLDB Endowment10.14778/3659437.365944517:8(1884-1897)Online publication date: 1-Apr-2024
https://dl.acm.org/doi/10.14778/3659437.3659445
Zheng YDou WTang LCui ZGao YSong JXu LZhu JWang WWei JZhong HHuang TChristakis MPradel M(2024)Testing Gremlin-Based Graph Database Systems via Query DisassemblingProceedings of the 33rd ACM SIGSOFT International Symposium on Software Testing and Analysis10.1145/3650212.3680392(1695-1707)Online publication date: 11-Sep-2024
https://dl.acm.org/doi/10.1145/3650212.3680392
Cui ZDou WGao YWang DSong JZheng YWang TYang RXu KHu YWei JHuang TRoychoudhury APaiva AAbreu RStorey M(2024)Understanding Transaction Bugs in Database SystemsProceedings of the IEEE/ACM 46th International Conference on Software Engineering10.1145/3597503.3639207(1-13)Online publication date: 20-May-2024
https://dl.acm.org/doi/10.1145/3597503.3639207
Ba JRigger MRoychoudhury APaiva AAbreu RStorey M(2024)CERT: Finding Performance Issues in Database Systems Through the Lens of Cardinality EstimationProceedings of the IEEE/ACM 46th International Conference on Software Engineering10.1145/3597503.3639076(1-13)Online publication date: 20-May-2024
https://dl.acm.org/doi/10.1145/3597503.3639076

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents