AryaWu/sqlite
0
1# 2009 March 042#3# The author disclaims copyright to this source code. In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16if {[run_thread_tests]==0} { finish_test ; return }17ifcapable !unlock_notify||!shared_cache { finish_test ; return }18 19# The tests in this file test the sqlite3_blocking_step() function in20# test_thread.c. sqlite3_blocking_step() is not an SQLite API function,21# it is just a demonstration of how the sqlite3_unlock_notify() function22# can be used to synchronize multi-threaded access to SQLite databases23# in shared-cache mode.24#25# Since the implementation of sqlite3_blocking_step() is included on the26# 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 database31# and attaches two other databases. Each database contains a single table.32#33# Each thread repeats transactions over and over for 20 seconds. Each34# transaction consists of 3 operations. Each operation is either a read35# or a write of one of the tables. The read operations verify an invariant36# to make sure that things are working as expected. If an SQLITE_LOCKED37# error is returned the current transaction is rolled back immediately.38#39# This exercise is repeated twice, once using sqlite3_step(), and the40# other using sqlite3_blocking_step(). The results are compared to ensure41# that sqlite3_blocking_step() resulted in higher transaction throughput.42#43 44db close45set ::enable_shared_cache [sqlite3_enable_shared_cache 1]46 47# Number of threads to run simultaneously.48#49set nThread 650set nSecond 551 52# The Tcl script executed by each of the $nThread threads used by this test.53#54set 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_OK61 62set sql $zSql63 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 $lRes76 }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 } msg82 }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 opendb98 99 #after 2000100 101 # This loop runs for ~20 seconds.102 #103 set iStart [clock_seconds]104 set nOp 0105 set nAttempt 0106 while { ([clock_seconds]-$iStart) < $nSecond } {107 108 # Each transaction does 3 operations. Each operation is either a read109 # or write of a randomly selected table (t1, t2 or t3). Set the variables110 # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement111 # 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 nAttempt134 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 $::DB150 opendb151 } 152 } elseif {$rc} {153 # Hit some other kind of error. This is a malfunction.154 error $msg155 } else {156 # No error occurred. Check that any SELECT statements in the transaction157 # returned "1". Otherwise, the invariant was false, indicating that158 # some malfunction has occurred.159 foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }160 incr nOp161 }162 }163 164 # Close the database connection and return 0.165 #166 sqlite3_close $::DB167 list $nOp $nAttempt168}169 170foreach {iTest xStep xPrepare} {171 1 sqlite3_blocking_step sqlite3_blocking_prepare_v2172 2 sqlite3_step sqlite3_nonblocking_prepare_v2173} {174 forcedelete test.db test2.db test3.db175 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 file179 # test.db as the main database, then attaches files test2.db and test3.db180 # as auxillary databases. Each file contains a single table (t1, t2 and t3, in181 # files test.db, test2.db and test3.db, respectively). 182 #183 do_test notify2-$iTest.1.1 {184 sqlite3 db test.db185 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 close198 } {}199 200 201 # Launch $nThread threads. Then wait for them to finish.202 #203 puts "Running $xStep test for $nSecond seconds"204 unset -nocomplain finished205 for {set ii 0} {$ii < $nThread} {incr ii} {206 thread_spawn finished($ii) $ThreadSetup $ThreadProgram207 }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 0214 } {0}215 }216 217 # Count the total number of succesful writes.218 do_test notify2-$iTest.3.1 {219 sqlite3 db test.db220 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 close230 } {}231}232 233# The following tests checks to make sure sqlite3_blocking_step() is234# faster than sqlite3_step(). "Faster" in this case means uses fewer235# 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 of240# transactions attempted using blocking_step() succeed.241#242# The blocking_step() function is almost always faster on multi-core and is243# usually faster on single-core. But sometimes, by chance, step() will be244# faster on a single core, in which case the245# following test will fail.246#247puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()"248puts "interface helps multi-core systems to run more efficiently. This test"249puts "sometimes fails on single-core machines."250puts [array get anWrite]251do_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 266sqlite3_enable_shared_cache $::enable_shared_cache267finish_test268