When a DROP TABLE fails due to a database table lock error it is not necessarily the table that is being dropped that is locked.

The background

Recently I wrote some code for searching a database from a web interface and I wanted to add an option to graph the data.  The first task was to take the search data and reformat it for pChart.  I settled on code that created two temporary tables and at the end I dropped the two tables, even though it’s not necessary since they will cease to exist once the connection is closed.  However when I started using it in anger (so to speak) I was getting an error complaining that table was locked.  Since it wasn’t doing any harm I continued with the rest of the coding.  Once I had finished the main code I couldn’t bring myself to leave the DROP TABLE problem unsolved.

The investigation

Even though I have been programming for over thirty years, I am still relatively inexperienced at SQL programming so I searched the Internet for clues and while one example came quite close, it didn’t contain enough context.  However it did indicate where I should be looking.  The first step was to demonstrate that there was no problem with the code which created and used the temporary tables, GraphExpenditure().  This could be done by closing and re-opening the database connection just before calling the function:

$ExpHandle = 0 ; // Close the database connection
if(($ExpHandle=ExpendConn($DBname))==FALSE) {
        printf("Cannot open database file %s\n",$DBname) ;
} 
else 
        GraphExpenditure($ExpHandle,$SQL_Common,$OutputType) ;

Once I did that the DROP TABLE error went away.  This proved that the problem lay with the code that existed before the graphing code was even introduced.

To debug where that problem was I changed from PDO class to PDOstatement class because that would allow me to explicitly close each connection (with closeCursor()).  There are three pieces of SQL code before graphing:

1. SELECT SUM() of the data which was searched for.
2. Much more elaborate SELECT SUM() of the data.
3. Search for the data itself and display it as a table.

I discovered that if either of the first two sets of code doesn’t have closeCursor() then the DROP TABLE later would fail.

The solution

The solution turned out to be relatively simple: just complete all the queries until they return FALSE.  In PHP (the normal checks have been removed to make the code easier to read), the important part is highlighted:

$CntDataQry = $DBhandle->query($CntDataSQL = "SELECT SUM(Counter) FROM SampleTable") ;
$row = $CntDataQry->fetch() ; // Fetch first (and only) row of the result
printf("SUM(Counter) = %d\n",$row[0]) ;
while($CntDataQry->fetch()!=FALSE) ; // This will fetch nothing

So the table that was locked was the system table.  In a previous job the DBAs regularly uttered oaths about Oracle’s system table, which if it locks, will quickly bring the whole system to a grinding halt.  Trying to compare my situation (SQLite) with Oracle is a bit like comparing a boat with an outboard motor to an ocean-going liner, but it’s interesting to see that they have some things in common.

Example code

I’ve written some sample code which will demonstrate a failure to DROP TABLE, followed by its resolution.  It’s intended to be run from the command line.