xref: /sqlite-3.40.0/test/walthread.test (revision b9bf16bf) 
1# 2010 April 13
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library.  The
12# focus of this file is testing the operation of the library in
13# "PRAGMA journal_mode=WAL" mode.
14#
15
16set testdir [file dirname $argv0]
17
18source $testdir/tester.tcl
19if {[run_thread_tests]==0} { finish_test ; return }
20
21do_test walthread-1.1 {
22  execsql {
23    PRAGMA journal_mode = WAL;
24    CREATE TABLE t1(x PRIMARY KEY);
25    INSERT INTO t1 VALUES(randomblob(100));
26    INSERT INTO t1 VALUES(randomblob(100));
27    INSERT INTO t1 SELECT md5sum(x) FROM t1;
28  }
29} {wal}
30do_test walthread-1.2 {
31  execsql {
32    SELECT (SELECT count(*) FROM t1), (
33      SELECT md5sum(x) FROM t1 WHERE oid != (SELECT max(oid) FROM t1)
34    ) == (
35      SELECT x FROM t1 WHERE oid = (SELECT max(oid) FROM t1)
36    )
37  }
38} {3 1}
39do_test walthread-1.3 {
40  execsql { PRAGMA integrity_check }
41} {ok}
42do_test walthread-1.4 {
43  execsql { PRAGMA lock_status }
44} {main unlocked temp unknown}
45
46#--------------------------------------------------------------------------
47# Start N threads. Each thread performs both read and write transactions.
48# Each read transaction consists of:
49#
50#   1) Reading the md5sum of all but the last table row,
51#   2) Running integrity check.
52#   3) Reading the value stored in the last table row,
53#   4) Check that the values read in steps 1 and 3 are the same, and that
54#      the md5sum of all but the last table row has not changed.
55#
56# Each write transaction consists of:
57#
58#   1) Modifying the contents of t1 (inserting, updating, deleting rows).
59#   2) Appending a new row to the table containing the md5sum() of all
60#      rows in the table.
61#
62# Each of the N threads runs N read transactions followed by a single write
63# transaction in a loop as fast as possible.
64#
65# Ther is also a single checkpointer thread. It runs the following loop:
66#
67#   1) Execute "CHECKPOINT main 32 -1 1"
68#   2) Sleep for 500 ms.
69#
70
71set thread_program {
72  proc rest {ms} {
73    set ::rest 0
74    after $ms {set ::rest 1}
75    vwait ::rest
76  }
77
78  proc dosql {DB sql} {
79    set res ""
80    set stmt [sqlite3_prepare_v2 $DB $sql -1 dummy_tail]
81    set rc [sqlite3_step $stmt]
82    if {$rc eq "SQLITE_ROW"} {
83      set res [sqlite3_column_text $stmt 0]
84    }
85    set rc [sqlite3_finalize $stmt]
86
87    if {$rc ne "SQLITE_OK"} {
88      error "$rc: [sqlite3_errmsg $DB]"
89    }
90    return $res
91  }
92
93  proc read_transaction {DB} {
94    dosql $DB BEGIN
95
96    set md5_1 [dosql $DB {
97      SELECT md5sum(x) FROM t1 WHERE rowid != (SELECT max(rowid) FROM t1)
98    }]
99    set check [dosql $DB { PRAGMA integrity_check }]
100    set md5_2 [dosql $DB {
101      SELECT x FROM t1 WHERE rowid = (SELECT max(rowid) FROM t1)
102    }]
103    set md5_3 [dosql $DB {
104      SELECT md5sum(x) FROM t1 WHERE rowid != (SELECT max(rowid) FROM t1)
105    }]
106
107    dosql $DB COMMIT
108
109    if {$check ne "ok"
110     || $md5_1 ne $md5_2
111     || $md5_2 ne $md5_3
112    } {
113      error "Failed read transaction $check $md5_1 $md5_2 $md5_3"
114    }
115  }
116
117  proc write_transaction {DB} {
118    dosql $DB BEGIN
119    dosql $DB "INSERT INTO t1 VALUES(randomblob(100))"
120    dosql $DB "INSERT INTO t1 VALUES(randomblob(100))"
121    dosql $DB "INSERT INTO t1 SELECT md5sum(x) FROM t1"
122    dosql $DB COMMIT
123  }
124
125  proc checkpointer {DB} {
126    while { !$::finished } {
127      dosql $DB "PRAGMA checkpoint"
128      rest 1000
129    }
130  }
131
132  proc worker {DB N} {
133    set j 0
134    while { !$::finished } {
135      for {set i 0} {$i < $N} {incr i} { read_transaction $DB }
136      write_transaction $DB
137      rest 1
138    }
139  }
140
141  set ::finished 0
142  after [expr $seconds*1000] {set ::finished 1}
143
144  set ::DB [sqlthread open test.db]
145  dosql $::DB { PRAGMA journal_mode = WAL }
146
147
148  set rc [catch {
149    if {$role eq "worker"} { worker $DB $N }
150    if {$role eq "checkpointer"} { checkpointer $DB }
151  } msg]
152
153  sqlite3_close $::DB
154
155  if {$rc==0} { set msg OK }
156  set msg
157}
158
159set NTHREAD 6
160set SECONDS 30
161
162#set prg "set N $NTHREAD ; set seconds $SECONDS"
163set prg "set N 1 ; set seconds $SECONDS"
164
165array unset finished
166for {set i 0} {$i < $NTHREAD} {incr i} {
167  thread_spawn finished($i) {set role worker} $prg $thread_program
168}
169thread_spawn finished(C) {set role checkpointer} $prg $thread_program
170#set finished(C) 1
171
172puts "... test runs for approximately $SECONDS seconds ..."
173for {set i 0} {$i < $::NTHREAD} {incr i} {
174  if {![info exists finished($i)]} {
175    vwait finished($i)
176  }
177  do_test walthread-2.$i {
178    set ::finished($i)
179  } OK
180}
181do_test walthread-2.C {
182  if {![info exists finished(C)]} { vwait finished(C) }
183  set ::finished(C)
184} OK
185
186set logsize 0
187
188set rows    [execsql { SELECT count(*) FROM t1 }]
189catch { set logsize [expr [file size test.db-wal] / 1024] }
190set dbsize  [expr [file size test.db] / 1024]
191
192puts "rows=$rows db=${dbsize}K log=${logsize}K"
193
194finish_test
195
196
197