gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/notify2.test (about) 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 set nOp 0 105 set nAttempt 0 106 while { ([clock_seconds]-$iStart) < $nSecond } { 107 108 # Each transaction does 3 operations. Each operation is either a read 109 # or write of a randomly selected table (t1, t2 or t3). Set the variables 110 # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement 111 # each operation. 112 # 113 for {set ii 1} {$ii <= 3} {incr ii} { 114 foreach {tbl database} [select_one {t1 main} {t2 aux2} {t3 aux3}] {} 115 116 set SQL($ii) [string map [list xxx $tbl yyy $database] [select_one { 117 SELECT 118 (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a) 119 FROM xxx WHERE a!=(SELECT max(a) FROM xxx); 120 } { 121 DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx); 122 INSERT INTO xxx SELECT NULL, total(a) FROM xxx; 123 } { 124 CREATE INDEX IF NOT EXISTS yyy.xxx_i ON xxx(b); 125 } { 126 DROP INDEX IF EXISTS yyy.xxx_i; 127 } 128 ]] 129 } 130 131 # Execute the SQL transaction. 132 # 133 incr nAttempt 134 set rc [catch { execsql_blocking $::DB " 135 BEGIN; 136 $SQL(1); 137 $SQL(2); 138 $SQL(3); 139 COMMIT; 140 " 141 } msg] 142 143 if {$rc && [string match "SQLITE_LOCKED*" $msg] 144 || [string match "SQLITE_SCHEMA*" $msg] 145 } { 146 # Hit an SQLITE_LOCKED error. Rollback the current transaction. 147 set rc [catch { execsql_blocking $::DB ROLLBACK } msg] 148 if {$rc && [string match "SQLITE_LOCKED*" $msg]} { 149 sqlite3_close $::DB 150 opendb 151 } 152 } elseif {$rc} { 153 # Hit some other kind of error. This is a malfunction. 154 error $msg 155 } else { 156 # No error occurred. Check that any SELECT statements in the transaction 157 # returned "1". Otherwise, the invariant was false, indicating that 158 # some malfunction has occurred. 159 foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } } 160 incr nOp 161 } 162 } 163 164 # Close the database connection and return 0. 165 # 166 sqlite3_close $::DB 167 list $nOp $nAttempt 168 } 169 170 foreach {iTest xStep xPrepare} { 171 1 sqlite3_blocking_step sqlite3_blocking_prepare_v2 172 2 sqlite3_step sqlite3_nonblocking_prepare_v2 173 } { 174 forcedelete test.db test2.db test3.db 175 176 set ThreadSetup "set xStep $xStep;set xPrepare $xPrepare;set nSecond $nSecond" 177 178 # Set up the database schema used by this test. Each thread opens file 179 # test.db as the main database, then attaches files test2.db and test3.db 180 # as auxillary databases. Each file contains a single table (t1, t2 and t3, in 181 # files test.db, test2.db and test3.db, respectively). 182 # 183 do_test notify2-$iTest.1.1 { 184 sqlite3 db test.db 185 execsql { 186 ATTACH 'test2.db' AS aux2; 187 ATTACH 'test3.db' AS aux3; 188 CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); 189 CREATE TABLE aux2.t2(a INTEGER PRIMARY KEY, b); 190 CREATE TABLE aux3.t3(a INTEGER PRIMARY KEY, b); 191 INSERT INTO t1 SELECT NULL, 0; 192 INSERT INTO t2 SELECT NULL, 0; 193 INSERT INTO t3 SELECT NULL, 0; 194 } 195 } {} 196 do_test notify2-$iTest.1.2 { 197 db close 198 } {} 199 200 201 # Launch $nThread threads. Then wait for them to finish. 202 # 203 puts "Running $xStep test for $nSecond seconds" 204 unset -nocomplain finished 205 for {set ii 0} {$ii < $nThread} {incr ii} { 206 thread_spawn finished($ii) $ThreadSetup $ThreadProgram 207 } 208 for {set ii 0} {$ii < $nThread} {incr ii} { 209 do_test notify2-$iTest.2.$ii { 210 if {![info exists finished($ii)]} { vwait finished($ii) } 211 incr anSuccess($xStep) [lindex $finished($ii) 0] 212 incr anAttempt($xStep) [lindex $finished($ii) 1] 213 expr 0 214 } {0} 215 } 216 217 # Count the total number of succesful writes. 218 do_test notify2-$iTest.3.1 { 219 sqlite3 db test.db 220 execsql { 221 ATTACH 'test2.db' AS aux2; 222 ATTACH 'test3.db' AS aux3; 223 } 224 set anWrite($xStep) [execsql { 225 SELECT (SELECT max(a) FROM t1) 226 + (SELECT max(a) FROM t2) 227 + (SELECT max(a) FROM t3) 228 }] 229 db close 230 } {} 231 } 232 233 # The following tests checks to make sure sqlite3_blocking_step() is 234 # faster than sqlite3_step(). "Faster" in this case means uses fewer 235 # CPU cycles. This is not always the same as faster in wall-clock time 236 # for this type of test. The number of CPU cycles per transaction is 237 # roughly proportional to the number of attempts made (i.e. one plus the 238 # number of SQLITE_BUSY or SQLITE_LOCKED errors that require the transaction 239 # to be retried). So this test just measures that a greater percentage of 240 # transactions attempted using blocking_step() succeed. 241 # 242 # The blocking_step() function is almost always faster on multi-core and is 243 # usually faster on single-core. But sometimes, by chance, step() will be 244 # faster on a single core, in which case the 245 # following test will fail. 246 # 247 puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()" 248 puts "interface helps multi-core systems to run more efficiently. This test" 249 puts "sometimes fails on single-core machines." 250 puts [array get anWrite] 251 do_test notify2-3 { 252 set blocking [expr { 253 double($anSuccess(sqlite3_blocking_step)) / 254 double($anAttempt(sqlite3_blocking_step)) 255 }] 256 set non [expr { 257 double($anSuccess(sqlite3_step)) / 258 double($anAttempt(sqlite3_step)) 259 }] 260 puts -nonewline [format " blocking: %.1f%% non-blocking %.1f%% ..." \ 261 [expr $blocking*100.0] [expr $non*100.0]] 262 263 expr {$blocking > $non} 264 } {1} 265 266 sqlite3_enable_shared_cache $::enable_shared_cache 267 finish_test