On the Complexity of Checking Mixed Isolation Levels for SQL Transactions

TL;DR

This paper investigates the computational complexity of verifying whether database transaction executions adhere to specified SQL isolation levels, revealing NP-completeness in many cases and proposing an efficient practical algorithm.

Contribution

It formalizes the problem of testing SQL transaction isolation levels, proves NP-completeness for many restrictions, and introduces an efficient algorithm for practical use.

Findings

Many restrictions are NP-complete

Proposed algorithm is exponential in worst-case but efficient in practice

Algorithm is polynomial-time in relevant cases

Abstract

Concurrent accesses to databases are typically grouped in transactions which define units of work that should be isolated from other concurrent computations and resilient to failures. Modern databases provide different levels of isolation for transactions that correspond to different trade-offs between consistency and throughput. Quite often, an application can use transactions with different isolation levels at the same time. In this work, we investigate the problem of testing isolation level implementations in databases, i.e., checking whether a given execution composed of multiple transactions adheres to the prescribed isolation level semantics. We particularly focus on transactions formed of SQL queries and the use of multiple isolation levels at the same time. We show that many restrictions of this problem are NP-complete and provide an algorithm which is exponential-time in the worst-case, polynomial-time in relevant cases, and practically efficient.