Annotation of embedaddon/sqlite3/test/notify2.test, revision 1.1
1.1 ! misho 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.7 2009/03/30 11:59:31 drh Exp $
! 13:
! 14: set testdir [file dirname $argv0]
! 15: source $testdir/tester.tcl
! 16: if {[run_thread_tests]==0} { finish_test ; return }
! 17: ifcapable !unlock_notify||!shared_cache { finish_test ; return }
! 18:
! 19: # The tests in this file test the sqlite3_blocking_step() function in
! 20: # test_thread.c. sqlite3_blocking_step() is not an SQLite API function,
! 21: # it is just a demonstration of how the sqlite3_unlock_notify() function
! 22: # can be used to synchronize multi-threaded access to SQLite databases
! 23: # in shared-cache mode.
! 24: #
! 25: # Since the implementation of sqlite3_blocking_step() is included on the
! 26: # 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 database
! 31: # and attaches two other databases. Each database contains a single table.
! 32: #
! 33: # Each thread repeats transactions over and over for 20 seconds. Each
! 34: # transaction consists of 3 operations. Each operation is either a read
! 35: # or a write of one of the tables. The read operations verify an invariant
! 36: # to make sure that things are working as expected. If an SQLITE_LOCKED
! 37: # error is returned the current transaction is rolled back immediately.
! 38: #
! 39: # This exercise is repeated twice, once using sqlite3_step(), and the
! 40: # other using sqlite3_blocking_step(). The results are compared to ensure
! 41: # that sqlite3_blocking_step() resulted in higher transaction throughput.
! 42: #
! 43:
! 44: db close
! 45: set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
! 46:
! 47: # Number of threads to run simultaneously.
! 48: #
! 49: set nThread 6
! 50: set nSecond 5
! 51:
! 52: # The Tcl script executed by each of the $nThread threads used by this test.
! 53: #
! 54: set 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_OK
! 61:
! 62: set sql $zSql
! 63:
! 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 $lRes
! 76: }
! 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 } msg
! 82: }
! 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: opendb
! 98:
! 99: #after 2000
! 100:
! 101: # This loop runs for ~20 seconds.
! 102: #
! 103: set iStart [clock_seconds]
! 104: while { ([clock_seconds]-$iStart) < $nSecond } {
! 105:
! 106: # Each transaction does 3 operations. Each operation is either a read
! 107: # or write of a randomly selected table (t1, t2 or t3). Set the variables
! 108: # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement
! 109: # each operation.
! 110: #
! 111: for {set ii 1} {$ii <= 3} {incr ii} {
! 112: foreach {tbl database} [select_one {t1 main} {t2 aux2} {t3 aux3}] {}
! 113:
! 114: set SQL($ii) [string map [list xxx $tbl yyy $database] [select_one {
! 115: SELECT
! 116: (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a)
! 117: FROM xxx WHERE a!=(SELECT max(a) FROM xxx);
! 118: } {
! 119: DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx);
! 120: INSERT INTO xxx SELECT NULL, total(a) FROM xxx;
! 121: } {
! 122: CREATE INDEX IF NOT EXISTS yyy.xxx_i ON xxx(b);
! 123: } {
! 124: DROP INDEX IF EXISTS yyy.xxx_i;
! 125: }
! 126: ]]
! 127: }
! 128:
! 129: # Execute the SQL transaction.
! 130: #
! 131: set rc [catch { execsql_blocking $::DB "
! 132: BEGIN;
! 133: $SQL(1);
! 134: $SQL(2);
! 135: $SQL(3);
! 136: COMMIT;
! 137: "
! 138: } msg]
! 139:
! 140: if {$rc && [string match "SQLITE_LOCKED*" $msg]
! 141: || [string match "SQLITE_SCHEMA*" $msg]
! 142: } {
! 143: # Hit an SQLITE_LOCKED error. Rollback the current transaction.
! 144: set rc [catch { execsql_blocking $::DB ROLLBACK } msg]
! 145: if {$rc && [string match "SQLITE_LOCKED*" $msg]} {
! 146: sqlite3_close $::DB
! 147: opendb
! 148: }
! 149: } elseif {$rc} {
! 150: # Hit some other kind of error. This is a malfunction.
! 151: error $msg
! 152: } else {
! 153: # No error occured. Check that any SELECT statements in the transaction
! 154: # returned "1". Otherwise, the invariant was false, indicating that
! 155: # some malfunction has occured.
! 156: foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }
! 157: }
! 158: }
! 159:
! 160: # Close the database connection and return 0.
! 161: #
! 162: sqlite3_close $::DB
! 163: expr 0
! 164: }
! 165:
! 166: foreach {iTest xStep xPrepare} {
! 167: 1 sqlite3_blocking_step sqlite3_blocking_prepare_v2
! 168: 2 sqlite3_step sqlite3_nonblocking_prepare_v2
! 169: } {
! 170: forcedelete test.db test2.db test3.db
! 171:
! 172: set ThreadSetup "set xStep $xStep;set xPrepare $xPrepare;set nSecond $nSecond"
! 173:
! 174: # Set up the database schema used by this test. Each thread opens file
! 175: # test.db as the main database, then attaches files test2.db and test3.db
! 176: # as auxillary databases. Each file contains a single table (t1, t2 and t3, in
! 177: # files test.db, test2.db and test3.db, respectively).
! 178: #
! 179: do_test notify2-$iTest.1.1 {
! 180: sqlite3 db test.db
! 181: execsql {
! 182: ATTACH 'test2.db' AS aux2;
! 183: ATTACH 'test3.db' AS aux3;
! 184: CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b);
! 185: CREATE TABLE aux2.t2(a INTEGER PRIMARY KEY, b);
! 186: CREATE TABLE aux3.t3(a INTEGER PRIMARY KEY, b);
! 187: INSERT INTO t1 SELECT NULL, 0;
! 188: INSERT INTO t2 SELECT NULL, 0;
! 189: INSERT INTO t3 SELECT NULL, 0;
! 190: }
! 191: } {}
! 192: do_test notify2-$iTest.1.2 {
! 193: db close
! 194: } {}
! 195:
! 196:
! 197: # Launch $nThread threads. Then wait for them to finish.
! 198: #
! 199: puts "Running $xStep test for $nSecond seconds"
! 200: unset -nocomplain finished
! 201: for {set ii 0} {$ii < $nThread} {incr ii} {
! 202: thread_spawn finished($ii) $ThreadSetup $ThreadProgram
! 203: }
! 204: for {set ii 0} {$ii < $nThread} {incr ii} {
! 205: do_test notify2-$iTest.2.$ii {
! 206: if {![info exists finished($ii)]} { vwait finished($ii) }
! 207: set finished($ii)
! 208: } {0}
! 209: }
! 210:
! 211: # Count the total number of succesful writes.
! 212: do_test notify2-$iTest.3.1 {
! 213: sqlite3 db test.db
! 214: execsql {
! 215: ATTACH 'test2.db' AS aux2;
! 216: ATTACH 'test3.db' AS aux3;
! 217: }
! 218: set anWrite($xStep) [execsql {
! 219: SELECT (SELECT max(a) FROM t1)
! 220: + (SELECT max(a) FROM t2)
! 221: + (SELECT max(a) FROM t3)
! 222: }]
! 223: db close
! 224: } {}
! 225: }
! 226:
! 227: # The following tests checks to make sure sqlite3_blocking_step() is
! 228: # faster than sqlite3_step(). blocking_step() is always faster on
! 229: # multi-core and is usually faster on single-core. But sometimes, by
! 230: # chance, step() will be faster on a single core, in which case the
! 231: # following test will fail.
! 232: #
! 233: puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()"
! 234: puts "interface helps multi-core systems to run faster. This test sometimes"
! 235: puts "fails on single-core machines."
! 236: puts [array get anWrite]
! 237: do_test notify2-3 {
! 238: expr {$anWrite(sqlite3_blocking_step) > $anWrite(sqlite3_step)}
! 239: } {1}
! 240:
! 241: sqlite3_enable_shared_cache $::enable_shared_cache
! 242: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to notify2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test