SQLite

View Ticket
Login
2011-06-01
11:26
Toward version 3.7.5.1. This check-in updates the version number and also patches the pager so that it never spills cache while in WAL mode. The latter patch fixes ticket [2d1a5c67dfc23] and also prevents excessive WAL file sizes if the pager cache starts thrashing. A few test cases fail. (Leaf check-in: 2e5a912a user: drh tags: branch-3.7.5)
2011-05-19
07:53
Add another test for [2d1a5c67df]. (check-in: 97fcd9e8 user: dan tags: trunk)
01:46
Backport the [67bf1c9a88] fix for vanishing WAL transactions due to small cache spillage. Ticket [2d1a5c67dfc236]. Also bump the version number to 3.7.6.3 in preparation for patch release. (check-in: e4d0f7ac user: drh tags: branch-3.7.6)
01:28 Fixed ticket [2d1a5c67]: Data loss on COMMIT in WAL mode plus 4 other changes (artifact: 98dc909f user: drh)
01:21
When committing a WAL transaction, make sure at least one page is written to the WAL file so that the WAL subsystem will have a page on which to set the commit flag. Ticket [2d1a5c67dfc236]. (check-in: 67bf1c9a user: drh tags: trunk)
2011-05-18
22:43 New ticket [2d1a5c67] Data loss on COMMIT in WAL mode. (artifact: e84732d1 user: drh)

Ticket Hash: 2d1a5c67dfc2363e44f29d9bbd57f7331851390a
Title: Data loss on COMMIT in WAL mode
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Pager Resolution: Fixed
Last Modified: 2011-05-19 01:28:33
Version Found In: 3.7.6
Description:
The following SQL demonstrates the problem:
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.