/trunk/test/notify2.test |
@@ -0,0 +1,242 @@ |
# 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] |
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. |
# |
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 occured. Check that any SELECT statements in the transaction |
# returned "1". Otherwise, the invariant was false, indicating that |
# some malfunction has occured. |
foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } } |
} |
} |
|
# Close the database connection and return 0. |
# |
sqlite3_close $::DB |
expr 0 |
} |
|
foreach {iTest xStep xPrepare} { |
1 sqlite3_blocking_step sqlite3_blocking_prepare_v2 |
2 sqlite3_step sqlite3_nonblocking_prepare_v2 |
} { |
file delete -force 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) } |
set finished($ii) |
} {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(). blocking_step() is 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 faster. This test sometimes" |
puts "fails on single-core machines." |
puts [array get anWrite] |
do_test notify2-3 { |
expr {$anWrite(sqlite3_blocking_step) > $anWrite(sqlite3_step)} |
} {1} |
|
sqlite3_enable_shared_cache $::enable_shared_cache |
finish_test |