The following SQL demonstrates the problem:
CREATE TABLE t1(a,b);
CREATE INDEX t1b ON t1(b);
CREATE TABLE t2(x,y UNIQUE);
INSERT INTO t2 VALUES(3,4);
INSERT INTO t1(a,b) VALUES(1,2);
SELECT 'A', * FROM t2 WHERE y=4;
SELECT 'B', * FROM t1;
SELECT 'C', * FROM t1;
The row inserted into t1 is present at B, but at C after the commit
the t1 table is empty. Changing the cache_size from 4 to 5 causes
the problem to go away.
<hr><i>drh added on 2011-05-19 01:28:33:</i><br>
This problem has existed in all previous versions of SQLite that support WAL.
The problem occurs when a multi-statement write transaction ends with one
or more SELECT statements that cause a cache spill due to memory pressure.
If there are no subsequent write operations, then when the transaction goes
to commit, there might not be any dirty pages left to write out to the WAL
with the commit-flag set. And so the commit-flag does not get set and the
transaction either merges into the next transaction, or if there is not
next transaction, the transaction will silently roll back.
We believe that the cache size must be set very small (less than 10 and
probably less than 5) for this to happen.
The fix is to always write page 1 to the WAL when there is a commit of
a non-empty transaction, even if page 1 was not changed or was already
written to WAL.