Return to notify2.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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