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 243
