xref: /sqlite-3.40.0/test/notify2.test (revision 8ab4b9e9) 
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16if {[run_thread_tests]==0} { finish_test ; return }
17ifcapable !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
44db close
45set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
46
47# Number of threads to run simultaneously.
48#
49set nThread 6
50set nSecond 5
51
52# The Tcl script executed by each of the $nThread threads used by this test.
53#
54set 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
62set 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  set nOp 0
105  set nAttempt 0
106  while { ([clock_seconds]-$iStart) < $nSecond } {
107
108    # Each transaction does 3 operations. Each operation is either a read
109    # or write of a randomly selected table (t1, t2 or t3). Set the variables
110    # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement
111    # each operation.
112    #
113    for {set ii 1} {$ii <= 3} {incr ii} {
114      foreach {tbl database} [select_one {t1 main} {t2 aux2} {t3 aux3}] {}
115
116      set SQL($ii) [string map [list xxx $tbl yyy $database] [select_one {
117            SELECT
118              (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a)
119              FROM xxx WHERE a!=(SELECT max(a) FROM xxx);
120      } {
121            DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx);
122            INSERT INTO xxx SELECT NULL, total(a) FROM xxx;
123      } {
124            CREATE INDEX IF NOT EXISTS yyy.xxx_i ON xxx(b);
125      } {
126            DROP INDEX IF EXISTS yyy.xxx_i;
127      }
128      ]]
129    }
130
131    # Execute the SQL transaction.
132    #
133    incr nAttempt
134    set rc [catch { execsql_blocking $::DB "
135        BEGIN;
136          $SQL(1);
137          $SQL(2);
138          $SQL(3);
139        COMMIT;
140      "
141    } msg]
142
143    if {$rc && [string match "SQLITE_LOCKED*" $msg]
144            || [string match "SQLITE_SCHEMA*" $msg]
145    } {
146      # Hit an SQLITE_LOCKED error. Rollback the current transaction.
147      set rc [catch { execsql_blocking $::DB ROLLBACK } msg]
148      if {$rc && [string match "SQLITE_LOCKED*" $msg]} {
149        sqlite3_close $::DB
150        opendb
151      }
152    } elseif {$rc} {
153      # Hit some other kind of error. This is a malfunction.
154      error $msg
155    } else {
156      # No error occurred. Check that any SELECT statements in the transaction
157      # returned "1". Otherwise, the invariant was false, indicating that
158      # some malfunction has occurred.
159      foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }
160      incr nOp
161    }
162  }
163
164  # Close the database connection and return 0.
165  #
166  sqlite3_close $::DB
167  list $nOp $nAttempt
168}
169
170foreach {iTest xStep xPrepare} {
171  1 sqlite3_blocking_step sqlite3_blocking_prepare_v2
172  2 sqlite3_step          sqlite3_nonblocking_prepare_v2
173} {
174  forcedelete test.db test2.db test3.db
175
176  set ThreadSetup "set xStep $xStep;set xPrepare $xPrepare;set nSecond $nSecond"
177
178  # Set up the database schema used by this test. Each thread opens file
179  # test.db as the main database, then attaches files test2.db and test3.db
180  # as auxillary databases. Each file contains a single table (t1, t2 and t3, in
181  # files test.db, test2.db and test3.db, respectively).
182  #
183  do_test notify2-$iTest.1.1 {
184    sqlite3 db test.db
185    execsql {
186      ATTACH 'test2.db' AS aux2;
187      ATTACH 'test3.db' AS aux3;
188      CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b);
189      CREATE TABLE aux2.t2(a INTEGER PRIMARY KEY, b);
190      CREATE TABLE aux3.t3(a INTEGER PRIMARY KEY, b);
191      INSERT INTO t1 SELECT NULL, 0;
192      INSERT INTO t2 SELECT NULL, 0;
193      INSERT INTO t3 SELECT NULL, 0;
194    }
195  } {}
196  do_test notify2-$iTest.1.2 {
197    db close
198  } {}
199
200
201  # Launch $nThread threads. Then wait for them to finish.
202  #
203  puts "Running $xStep test for $nSecond seconds"
204  unset -nocomplain finished
205  for {set ii 0} {$ii < $nThread} {incr ii} {
206    thread_spawn finished($ii) $ThreadSetup $ThreadProgram
207  }
208  for {set ii 0} {$ii < $nThread} {incr ii} {
209    do_test notify2-$iTest.2.$ii {
210      if {![info exists finished($ii)]} { vwait finished($ii) }
211      incr anSuccess($xStep) [lindex $finished($ii) 0]
212      incr anAttempt($xStep) [lindex $finished($ii) 1]
213      expr 0
214    } {0}
215  }
216
217  # Count the total number of succesful writes.
218  do_test notify2-$iTest.3.1 {
219    sqlite3 db test.db
220    execsql {
221      ATTACH 'test2.db' AS aux2;
222      ATTACH 'test3.db' AS aux3;
223    }
224    set anWrite($xStep) [execsql {
225      SELECT (SELECT max(a) FROM t1)
226           + (SELECT max(a) FROM t2)
227           + (SELECT max(a) FROM t3)
228    }]
229    db close
230  } {}
231}
232
233# The following tests checks to make sure sqlite3_blocking_step() is
234# faster than sqlite3_step(). "Faster" in this case means uses fewer
235# CPU cycles. This is not always the same as faster in wall-clock time
236# for this type of test. The number of CPU cycles per transaction is
237# roughly proportional to the number of attempts made (i.e. one plus the
238# number of SQLITE_BUSY or SQLITE_LOCKED errors that require the transaction
239# to be retried). So this test just measures that a greater percentage of
240# transactions attempted using blocking_step() succeed.
241#
242# The blocking_step() function is almost always faster on multi-core and is
243# usually faster on single-core.  But sometimes, by chance, step() will be
244# faster on a single core, in which case the
245# following test will fail.
246#
247puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()"
248puts "interface helps multi-core systems to run more efficiently.  This test"
249puts "sometimes fails on single-core machines."
250puts [array get anWrite]
251do_test notify2-3 {
252  set blocking [expr {
253    double($anSuccess(sqlite3_blocking_step)) /
254    double($anAttempt(sqlite3_blocking_step))
255  }]
256  set non [expr {
257    double($anSuccess(sqlite3_step)) /
258    double($anAttempt(sqlite3_step))
259  }]
260  puts -nonewline [format " blocking: %.1f%% non-blocking %.1f%% ..." \
261    [expr $blocking*100.0] [expr $non*100.0]]
262
263  expr {$blocking > $non}
264} {1}
265
266sqlite3_enable_shared_cache $::enable_shared_cache
267finish_test
268