SQLite "Database table is locked" errors in PDO
While working on various PHPUnit tests, I was running some database queries. During the tear-down for my tests, I repeatedly received the following error:
General error: 6 database table is locked
The line of code generating this error (from my unit test's tearDown()) was this:
$db->exec('DROP TABLE qpdb_test');
Here, $db was a PDO object connected to a SQLite database. This line of code simply attempts to drop the table that the unit test has been working with.
Initially I could not find the root of the problem. A Google search just about convinced me that the problem was actually with the SQLite driver. However, I managed to temporarily work around the error by skipping the table drop and running a DELETE FROM qpdb_test instead. This worked as expected. After deleting, I discovered the problem.
One of my unit tests was failing. Because PHPUnit (correctly) traps all errors and catches all exceptions, the failed unit test did not stop the execution of the program. Instead, it began the tear-down procedure. However, the premature failure did prevent the PDOStatement's cursor from being closed. With the cursor left open, the DROP TABLE could not successfully be run. And when the DROP failed, the program prematurely exited (as this was in tear-down, not in a test).
A Google search turned up all sorts of theories about why the database tables were locked. (Apparently, others have had this same problem in the context of unit testing.) In the end, though, it was simply a matter of PDO performing as documented, PHPUnit performing as documented, and my code not performing as documented. <!--break-->
