SQLite: Why can't I detach the database when the main database has an open prepared statement?

Question

Why the following code does not work:

#include <stdio.h> #include <sqlite3.h> int main(void) { sqlite3 *pDb; sqlite3_stmt *stmt; char *errmsg; sqlite3_open(":memory:", &pDb); sqlite3_exec(pDb, "CREATE TABLE Test(a INTEGER)", NULL, NULL, NULL); sqlite3_exec(pDb, "INSERT INTO Test(a) VALUES(1)", NULL, NULL, NULL); sqlite3_prepare_v2(pDb, "SELECT * FROM Test", -1, &stmt, NULL); sqlite3_step(stmt); sqlite3_exec(pDb, "ATTACH 'Test.db' as Other;", NULL, NULL, NULL); sqlite3_exec(pDb, "DETACH Other;", NULL, NULL, &errmsg); printf("error: %s\n", errmsg); return 0; } 

Output:

 error: database Other is locked 

If I execute sqlite3_reset(stmt) after sqlite3_step(stmt) , it works.

Why is an open statement in an unrelated database blocking a "different" database? I could not find an explanation in the documents.

Answer 1

Transaction documentation says:

An implicit transaction (a transaction that starts automatically, rather than a transaction initiated by BEGIN) is committed automatically when the last active statement ends. A statement ends when its prepared statement is reset or finalized .

Operations always affect all connected databases , so an open transaction will block all databases.