SQLite

PRAGMA cache_size=4;
PRAGMA journal_mode=WAL;
CREATE TABLE t1(a,b);
CREATE INDEX t1b ON t1(b);
CREATE TABLE t2(x,y UNIQUE);
INSERT INTO t2 VALUES(3,4);
BEGIN;
INSERT INTO t1(a,b) VALUES(1,2);
SELECT 'A', * FROM t2 WHERE y=4;
SELECT 'B', * FROM t1;
COMMIT;
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.

drh added on 2011-05-19 01:28:33:
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.