Monday, June 23, 2014

How to find running statements in sqlite3

Quite often, sqlite3 users get SQLITE_LOCKED error code when trying to execute some query. Sometimes it is related to concurrent access and is well documented here. However, in case of DROP TABLE operation it is practically useless. I have used the following method to detect which statements are not finished yet. To use it, you need some debugger that can attach to a running process. Then you need to set a breakpoint just near sqlite3_exec or whatever other function that causes SQLITE_LOCKED. Then, you need to step inside that function and print the database object (and you need sqlite3 debug symbols + source code to do these checks):

(lldb) p *db

nVdbeActive = 1
nVdbeRead = 1

So you have a single unfinished query. But to find it we need to apply some sort of black magic. First of all, sqlite stores statements that are not finalized (e.g. prepared statements) inside db object in a linked list of Vdbe* structures. Each Vdbe structure contains a single statement (actually, sqlite3_stmt is an opaque name for Vdbe structure). But how could we find, which query is finished and which is active. Here is the code from sqlite3 itself:

SQLITE_API int sqlite3_stmt_busy(sqlite3_stmt *pStmt){
  Vdbe *v = (Vdbe*)pStmt;
  return v!=0 && v->pc>0 && v->magic==VDBE_MAGIC_RUN;

Hence, we need to check 2 fields of Vdbe structure: pc (program count) and magic. In decimal form magic for active statements is 3186757027. The subsequent operations are quite simple: iterate over linked list (by using pNext field or by writing some macro to simplify this procedure) and find queries, that have pc > 0 and magic equal to 3186757027:

(lldb) p *db->pVdbe->pNext->pNext->pNext->pNext->pNext->pNext->pNext
zSql = 0x0000000801067c08 "SELECT version FROM packages WHERE origin=?1"
magic = 3186757027
pc = 12

Finally, examine and fix your code to reset that statement prior to calling for DROP TABLE/INDEX.