diff options
Diffstat (limited to '')
| -rw-r--r-- | test/notify2.test | 267 |
1 files changed, 267 insertions, 0 deletions
diff --git a/test/notify2.test b/test/notify2.test new file mode 100644 index 0000000..12c6a53 --- /dev/null +++ b/test/notify2.test @@ -0,0 +1,267 @@ +# 2009 March 04 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# $Id: notify2.test,v 1.7 2009/03/30 11:59:31 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +if {[run_thread_tests]==0} { finish_test ; return } +ifcapable !unlock_notify||!shared_cache { finish_test ; return } + +# The tests in this file test the sqlite3_blocking_step() function in +# test_thread.c. sqlite3_blocking_step() is not an SQLite API function, +# it is just a demonstration of how the sqlite3_unlock_notify() function +# can be used to synchronize multi-threaded access to SQLite databases +# in shared-cache mode. +# +# Since the implementation of sqlite3_blocking_step() is included on the +# website as example code, it is important to test that it works. +# +# notify2-1.*: +# +# This test uses $nThread threads. Each thread opens the main database +# and attaches two other databases. Each database contains a single table. +# +# Each thread repeats transactions over and over for 20 seconds. Each +# transaction consists of 3 operations. Each operation is either a read +# or a write of one of the tables. The read operations verify an invariant +# to make sure that things are working as expected. If an SQLITE_LOCKED +# error is returned the current transaction is rolled back immediately. +# +# This exercise is repeated twice, once using sqlite3_step(), and the +# other using sqlite3_blocking_step(). The results are compared to ensure +# that sqlite3_blocking_step() resulted in higher transaction throughput. +# + +db close +set ::enable_shared_cache [sqlite3_enable_shared_cache 1] + +# Number of threads to run simultaneously. +# +set nThread 6 +set nSecond 5 + +# The Tcl script executed by each of the $nThread threads used by this test. +# +set ThreadProgram { + + # Proc used by threads to execute SQL. + # + proc execsql_blocking {db zSql} { + set lRes [list] + set rc SQLITE_OK + +set sql $zSql + + while {$rc=="SQLITE_OK" && $zSql ne ""} { + set STMT [$::xPrepare $db $zSql -1 zSql] + while {[set rc [$::xStep $STMT]] eq "SQLITE_ROW"} { + for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { + lappend lRes [sqlite3_column_text $STMT 0] + } + } + set rc [sqlite3_finalize $STMT] + } + + if {$rc != "SQLITE_OK"} { error "$rc $sql [sqlite3_errmsg $db]" } + return $lRes + } + + proc execsql_retry {db sql} { + set msg "SQLITE_LOCKED blah..." + while { [string match SQLITE_LOCKED* $msg] } { + catch { execsql_blocking $db $sql } msg + } + } + + proc select_one {args} { + set n [llength $args] + lindex $args [expr int($n*rand())] + } + + proc opendb {} { + # Open a database connection. Attach the two auxillary databases. + set ::DB [sqlite3_open test.db] + execsql_retry $::DB { ATTACH 'test2.db' AS aux2; } + execsql_retry $::DB { ATTACH 'test3.db' AS aux3; } + } + + opendb + + #after 2000 + + # This loop runs for ~20 seconds. + # + set iStart [clock_seconds] + set nOp 0 + set nAttempt 0 + while { ([clock_seconds]-$iStart) < $nSecond } { + + # Each transaction does 3 operations. Each operation is either a read + # or write of a randomly selected table (t1, t2 or t3). Set the variables + # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement + # each operation. + # + for {set ii 1} {$ii <= 3} {incr ii} { + foreach {tbl database} [select_one {t1 main} {t2 aux2} {t3 aux3}] {} + + set SQL($ii) [string map [list xxx $tbl yyy $database] [select_one { + SELECT + (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a) + FROM xxx WHERE a!=(SELECT max(a) FROM xxx); + } { + DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx); + INSERT INTO xxx SELECT NULL, total(a) FROM xxx; + } { + CREATE INDEX IF NOT EXISTS yyy.xxx_i ON xxx(b); + } { + DROP INDEX IF EXISTS yyy.xxx_i; + } + ]] + } + + # Execute the SQL transaction. + # + incr nAttempt + set rc [catch { execsql_blocking $::DB " + BEGIN; + $SQL(1); + $SQL(2); + $SQL(3); + COMMIT; + " + } msg] + + if {$rc && [string match "SQLITE_LOCKED*" $msg] + || [string match "SQLITE_SCHEMA*" $msg] + } { + # Hit an SQLITE_LOCKED error. Rollback the current transaction. + set rc [catch { execsql_blocking $::DB ROLLBACK } msg] + if {$rc && [string match "SQLITE_LOCKED*" $msg]} { + sqlite3_close $::DB + opendb + } + } elseif {$rc} { + # Hit some other kind of error. This is a malfunction. + error $msg + } else { + # No error occurred. Check that any SELECT statements in the transaction + # returned "1". Otherwise, the invariant was false, indicating that + # some malfunction has occurred. + foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } } + incr nOp + } + } + + # Close the database connection and return 0. + # + sqlite3_close $::DB + list $nOp $nAttempt +} + +foreach {iTest xStep xPrepare} { + 1 sqlite3_blocking_step sqlite3_blocking_prepare_v2 + 2 sqlite3_step sqlite3_nonblocking_prepare_v2 +} { + forcedelete test.db test2.db test3.db + + set ThreadSetup "set xStep $xStep;set xPrepare $xPrepare;set nSecond $nSecond" + + # Set up the database schema used by this test. Each thread opens file + # test.db as the main database, then attaches files test2.db and test3.db + # as auxillary databases. Each file contains a single table (t1, t2 and t3, in + # files test.db, test2.db and test3.db, respectively). + # + do_test notify2-$iTest.1.1 { + sqlite3 db test.db + execsql { + ATTACH 'test2.db' AS aux2; + ATTACH 'test3.db' AS aux3; + CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); + CREATE TABLE aux2.t2(a INTEGER PRIMARY KEY, b); + CREATE TABLE aux3.t3(a INTEGER PRIMARY KEY, b); + INSERT INTO t1 SELECT NULL, 0; + INSERT INTO t2 SELECT NULL, 0; + INSERT INTO t3 SELECT NULL, 0; + } + } {} + do_test notify2-$iTest.1.2 { + db close + } {} + + + # Launch $nThread threads. Then wait for them to finish. + # + puts "Running $xStep test for $nSecond seconds" + unset -nocomplain finished + for {set ii 0} {$ii < $nThread} {incr ii} { + thread_spawn finished($ii) $ThreadSetup $ThreadProgram + } + for {set ii 0} {$ii < $nThread} {incr ii} { + do_test notify2-$iTest.2.$ii { + if {![info exists finished($ii)]} { vwait finished($ii) } + incr anSuccess($xStep) [lindex $finished($ii) 0] + incr anAttempt($xStep) [lindex $finished($ii) 1] + expr 0 + } {0} + } + + # Count the total number of succesful writes. + do_test notify2-$iTest.3.1 { + sqlite3 db test.db + execsql { + ATTACH 'test2.db' AS aux2; + ATTACH 'test3.db' AS aux3; + } + set anWrite($xStep) [execsql { + SELECT (SELECT max(a) FROM t1) + + (SELECT max(a) FROM t2) + + (SELECT max(a) FROM t3) + }] + db close + } {} +} + +# The following tests checks to make sure sqlite3_blocking_step() is +# faster than sqlite3_step(). "Faster" in this case means uses fewer +# CPU cycles. This is not always the same as faster in wall-clock time +# for this type of test. The number of CPU cycles per transaction is +# roughly proportional to the number of attempts made (i.e. one plus the +# number of SQLITE_BUSY or SQLITE_LOCKED errors that require the transaction +# to be retried). So this test just measures that a greater percentage of +# transactions attempted using blocking_step() succeed. +# +# The blocking_step() function is almost always faster on multi-core and is +# usually faster on single-core. But sometimes, by chance, step() will be +# faster on a single core, in which case the +# following test will fail. +# +puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()" +puts "interface helps multi-core systems to run more efficiently. This test" +puts "sometimes fails on single-core machines." +puts [array get anWrite] +do_test notify2-3 { + set blocking [expr { + double($anSuccess(sqlite3_blocking_step)) / + double($anAttempt(sqlite3_blocking_step)) + }] + set non [expr { + double($anSuccess(sqlite3_step)) / + double($anAttempt(sqlite3_step)) + }] + puts -nonewline [format " blocking: %.1f%% non-blocking %.1f%% ..." \ + [expr $blocking*100.0] [expr $non*100.0]] + + expr {$blocking > $non} +} {1} + +sqlite3_enable_shared_cache $::enable_shared_cache +finish_test |