wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2009 March 04 |
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 | # |
||
12 | # $Id: notify2.test,v 1.7 2009/03/30 11:59:31 drh Exp $ |
||
13 | |||
14 | set testdir [file dirname $argv0] |
||
15 | source $testdir/tester.tcl |
||
16 | if {[run_thread_tests]==0} { finish_test ; return } |
||
17 | ifcapable !unlock_notify||!shared_cache { finish_test ; return } |
||
18 | |||
19 | # The tests in this file test the sqlite3_blocking_step() function in |
||
20 | # test_thread.c. sqlite3_blocking_step() is not an SQLite API function, |
||
21 | # it is just a demonstration of how the sqlite3_unlock_notify() function |
||
22 | # can be used to synchronize multi-threaded access to SQLite databases |
||
23 | # in shared-cache mode. |
||
24 | # |
||
25 | # Since the implementation of sqlite3_blocking_step() is included on the |
||
26 | # website as example code, it is important to test that it works. |
||
27 | # |
||
28 | # notify2-1.*: |
||
29 | # |
||
30 | # This test uses $nThread threads. Each thread opens the main database |
||
31 | # and attaches two other databases. Each database contains a single table. |
||
32 | # |
||
33 | # Each thread repeats transactions over and over for 20 seconds. Each |
||
34 | # transaction consists of 3 operations. Each operation is either a read |
||
35 | # or a write of one of the tables. The read operations verify an invariant |
||
36 | # to make sure that things are working as expected. If an SQLITE_LOCKED |
||
37 | # error is returned the current transaction is rolled back immediately. |
||
38 | # |
||
39 | # This exercise is repeated twice, once using sqlite3_step(), and the |
||
40 | # other using sqlite3_blocking_step(). The results are compared to ensure |
||
41 | # that sqlite3_blocking_step() resulted in higher transaction throughput. |
||
42 | # |
||
43 | |||
44 | db close |
||
45 | set ::enable_shared_cache [sqlite3_enable_shared_cache 1] |
||
46 | |||
47 | # Number of threads to run simultaneously. |
||
48 | # |
||
49 | set nThread 6 |
||
50 | set nSecond 5 |
||
51 | |||
52 | # The Tcl script executed by each of the $nThread threads used by this test. |
||
53 | # |
||
54 | set ThreadProgram { |
||
55 | |||
56 | # Proc used by threads to execute SQL. |
||
57 | # |
||
58 | proc execsql_blocking {db zSql} { |
||
59 | set lRes [list] |
||
60 | set rc SQLITE_OK |
||
61 | |||
62 | set sql $zSql |
||
63 | |||
64 | while {$rc=="SQLITE_OK" && $zSql ne ""} { |
||
65 | set STMT [$::xPrepare $db $zSql -1 zSql] |
||
66 | while {[set rc [$::xStep $STMT]] eq "SQLITE_ROW"} { |
||
67 | for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { |
||
68 | lappend lRes [sqlite3_column_text $STMT 0] |
||
69 | } |
||
70 | } |
||
71 | set rc [sqlite3_finalize $STMT] |
||
72 | } |
||
73 | |||
74 | if {$rc != "SQLITE_OK"} { error "$rc $sql [sqlite3_errmsg $db]" } |
||
75 | return $lRes |
||
76 | } |
||
77 | |||
78 | proc execsql_retry {db sql} { |
||
79 | set msg "SQLITE_LOCKED blah..." |
||
80 | while { [string match SQLITE_LOCKED* $msg] } { |
||
81 | catch { execsql_blocking $db $sql } msg |
||
82 | } |
||
83 | } |
||
84 | |||
85 | proc select_one {args} { |
||
86 | set n [llength $args] |
||
87 | lindex $args [expr int($n*rand())] |
||
88 | } |
||
89 | |||
90 | proc opendb {} { |
||
91 | # Open a database connection. Attach the two auxillary databases. |
||
92 | set ::DB [sqlite3_open test.db] |
||
93 | execsql_retry $::DB { ATTACH 'test2.db' AS aux2; } |
||
94 | execsql_retry $::DB { ATTACH 'test3.db' AS aux3; } |
||
95 | } |
||
96 | |||
97 | opendb |
||
98 | |||
99 | #after 2000 |
||
100 | |||
101 | # This loop runs for ~20 seconds. |
||
102 | # |
||
103 | set iStart [clock_seconds] |
||
104 | while { ([clock_seconds]-$iStart) < $nSecond } { |
||
105 | |||
106 | # Each transaction does 3 operations. Each operation is either a read |
||
107 | # or write of a randomly selected table (t1, t2 or t3). Set the variables |
||
108 | # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement |
||
109 | # each operation. |
||
110 | # |
||
111 | for {set ii 1} {$ii <= 3} {incr ii} { |
||
112 | foreach {tbl database} [select_one {t1 main} {t2 aux2} {t3 aux3}] {} |
||
113 | |||
114 | set SQL($ii) [string map [list xxx $tbl yyy $database] [select_one { |
||
115 | SELECT |
||
116 | (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a) |
||
117 | FROM xxx WHERE a!=(SELECT max(a) FROM xxx); |
||
118 | } { |
||
119 | DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx); |
||
120 | INSERT INTO xxx SELECT NULL, total(a) FROM xxx; |
||
121 | } { |
||
122 | CREATE INDEX IF NOT EXISTS yyy.xxx_i ON xxx(b); |
||
123 | } { |
||
124 | DROP INDEX IF EXISTS yyy.xxx_i; |
||
125 | } |
||
126 | ]] |
||
127 | } |
||
128 | |||
129 | # Execute the SQL transaction. |
||
130 | # |
||
131 | set rc [catch { execsql_blocking $::DB " |
||
132 | BEGIN; |
||
133 | $SQL(1); |
||
134 | $SQL(2); |
||
135 | $SQL(3); |
||
136 | COMMIT; |
||
137 | " |
||
138 | } msg] |
||
139 | |||
140 | if {$rc && [string match "SQLITE_LOCKED*" $msg] |
||
141 | || [string match "SQLITE_SCHEMA*" $msg] |
||
142 | } { |
||
143 | # Hit an SQLITE_LOCKED error. Rollback the current transaction. |
||
144 | set rc [catch { execsql_blocking $::DB ROLLBACK } msg] |
||
145 | if {$rc && [string match "SQLITE_LOCKED*" $msg]} { |
||
146 | sqlite3_close $::DB |
||
147 | opendb |
||
148 | } |
||
149 | } elseif {$rc} { |
||
150 | # Hit some other kind of error. This is a malfunction. |
||
151 | error $msg |
||
152 | } else { |
||
153 | # No error occured. Check that any SELECT statements in the transaction |
||
154 | # returned "1". Otherwise, the invariant was false, indicating that |
||
155 | # some malfunction has occured. |
||
156 | foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } } |
||
157 | } |
||
158 | } |
||
159 | |||
160 | # Close the database connection and return 0. |
||
161 | # |
||
162 | sqlite3_close $::DB |
||
163 | expr 0 |
||
164 | } |
||
165 | |||
166 | foreach {iTest xStep xPrepare} { |
||
167 | 1 sqlite3_blocking_step sqlite3_blocking_prepare_v2 |
||
168 | 2 sqlite3_step sqlite3_nonblocking_prepare_v2 |
||
169 | } { |
||
170 | file delete -force test.db test2.db test3.db |
||
171 | |||
172 | set ThreadSetup "set xStep $xStep;set xPrepare $xPrepare;set nSecond $nSecond" |
||
173 | |||
174 | # Set up the database schema used by this test. Each thread opens file |
||
175 | # test.db as the main database, then attaches files test2.db and test3.db |
||
176 | # as auxillary databases. Each file contains a single table (t1, t2 and t3, in |
||
177 | # files test.db, test2.db and test3.db, respectively). |
||
178 | # |
||
179 | do_test notify2-$iTest.1.1 { |
||
180 | sqlite3 db test.db |
||
181 | execsql { |
||
182 | ATTACH 'test2.db' AS aux2; |
||
183 | ATTACH 'test3.db' AS aux3; |
||
184 | CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); |
||
185 | CREATE TABLE aux2.t2(a INTEGER PRIMARY KEY, b); |
||
186 | CREATE TABLE aux3.t3(a INTEGER PRIMARY KEY, b); |
||
187 | INSERT INTO t1 SELECT NULL, 0; |
||
188 | INSERT INTO t2 SELECT NULL, 0; |
||
189 | INSERT INTO t3 SELECT NULL, 0; |
||
190 | } |
||
191 | } {} |
||
192 | do_test notify2-$iTest.1.2 { |
||
193 | db close |
||
194 | } {} |
||
195 | |||
196 | |||
197 | # Launch $nThread threads. Then wait for them to finish. |
||
198 | # |
||
199 | puts "Running $xStep test for $nSecond seconds" |
||
200 | unset -nocomplain finished |
||
201 | for {set ii 0} {$ii < $nThread} {incr ii} { |
||
202 | thread_spawn finished($ii) $ThreadSetup $ThreadProgram |
||
203 | } |
||
204 | for {set ii 0} {$ii < $nThread} {incr ii} { |
||
205 | do_test notify2-$iTest.2.$ii { |
||
206 | if {![info exists finished($ii)]} { vwait finished($ii) } |
||
207 | set finished($ii) |
||
208 | } {0} |
||
209 | } |
||
210 | |||
211 | # Count the total number of succesful writes. |
||
212 | do_test notify2-$iTest.3.1 { |
||
213 | sqlite3 db test.db |
||
214 | execsql { |
||
215 | ATTACH 'test2.db' AS aux2; |
||
216 | ATTACH 'test3.db' AS aux3; |
||
217 | } |
||
218 | set anWrite($xStep) [execsql { |
||
219 | SELECT (SELECT max(a) FROM t1) |
||
220 | + (SELECT max(a) FROM t2) |
||
221 | + (SELECT max(a) FROM t3) |
||
222 | }] |
||
223 | db close |
||
224 | } {} |
||
225 | } |
||
226 | |||
227 | # The following tests checks to make sure sqlite3_blocking_step() is |
||
228 | # faster than sqlite3_step(). blocking_step() is always faster on |
||
229 | # multi-core and is usually faster on single-core. But sometimes, by |
||
230 | # chance, step() will be faster on a single core, in which case the |
||
231 | # following test will fail. |
||
232 | # |
||
233 | puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()" |
||
234 | puts "interface helps multi-core systems to run faster. This test sometimes" |
||
235 | puts "fails on single-core machines." |
||
236 | puts [array get anWrite] |
||
237 | do_test notify2-3 { |
||
238 | expr {$anWrite(sqlite3_blocking_step) > $anWrite(sqlite3_step)} |
||
239 | } {1} |
||
240 | |||
241 | sqlite3_enable_shared_cache $::enable_shared_cache |
||
242 | finish_test |