# 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 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" } } } } # 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 } { 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) } 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