I did the experiment. I used two Terminal windows accessing the same database. It's not quite the right experiment because I can't figure out how to make the shell tool stop in the middle of a SELECT. So instead I used a transaction to keep the database locked between two SELECT commands.
Here's the setup: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(c INT); sqlite> INSERT INTO t (c) VALUES (1),(2),(3),(4),(5); sqlite> SELECT * FROM t; 1 2 3 4 5 sqlite> First, an attempt in default journaling mode: In window A: sqlite> PRAGMA journal_mode; delete sqlite> BEGIN; sqlite> SELECT * FROM t; 1 2 3 4 5 In window B: sqlite> INSERT INTO t (c) VALUES (6); Error: database is locked sqlite> ALTER TABLE t ADD COLUMN y TEXT; Error: database is locked So it won't let you do INSERT or ADD COLUMN because the database is locked, as expected. Now try it in WAL mode. (To my surprise I had to .quit and reopen in window B for it to register that the journal mode had changed. Can anyone tell me why ?) In window A: sqlite> END; sqlite> PRAGMA journal_mode = WAL; wal sqlite> PRAGMA journal_mode; wal sqlite> BEGIN; sqlite> SELECT * FROM t; 1 2 3 4 5 In window B (after .quit and reopen): sqlite> PRAGMA journal_mode; wal sqlite> INSERT INTO t (c) VALUES (6); sqlite> ALTER TABLE t ADD COLUMN y TEXT DEFAULT 'unchanged'; sqlite> SELECT * FROM t; 1|unchanged 2|unchanged 3|unchanged 4|unchanged 5|unchanged 6|unchanged Back to window A: sqlite> SELECT * FROM t; 1 2 3 4 5 sqlite> END; sqlite> SELECT * FROM t; 1|unchanged 2|unchanged 3|unchanged 4|unchanged 5|unchanged 6|unchanged As expected, ALTER TABLE acts like INSERT: it's a change which requires an exclusive lock. So just as the documentation says, in a normal journal mode you can't make a change while the database is locked (which it is during a SELECT), and in WAL mode you can make the change but the connection doing the SELECT won't see it until it finishes its transaction. Which gives the grand conclusion I posted earlier: OP does not have to worry about this issue. It can never happen. Your schema cannot be changed on you while you're inside a transaction if you don't intentionally defeat SQLite's locking mechanism. Which is what I'd thought and hoped, since in any scenario where schema can change while a SELECT is running (e.g. anywhere a graphical front end to SQLite was in use) every programmer would have had to write horrible extra code to handle the possibility. Simon.
