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