xref: /sqlite-3.40.0/test/notify2.test (revision 404ca075) 
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 2009/03/16 13:19:36 danielk1977 Exp $
13
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16
17# The tests in this file test the sqlite3_blocking_step() function in
18# test_thread.c. sqlite3_blocking_step() is not an SQLite API function,
19# it is just a demonstration of how the sqlite3_unlock_notify() function
20# can be used to synchronize multi-threaded access to SQLite databases
21# in shared-cache mode.
22#
23# Since the implementation of sqlite3_blocking_step() is included on the
24# website as example code, it is important to test that it works.
25#
26# notify2-1.*:
27#
28#   This test uses $nThread threads. Each thread opens the main database
29#   and attaches two other databases. Each database contains a single table.
30#
31#   Each thread repeats transactions over and over for 20 seconds. Each
32#   transaction consists of 3 operations. Each operation is either a read
33#   or a write of one of the tables. The read operations verify an invariant
34#   to make sure that things are working as expected. If an SQLITE_LOCKED
35#   error is returned the current transaction is rolled back immediately.
36#
37#   This exercise is repeated twice, once using sqlite3_step(), and the
38#   other using sqlite3_blocking_step(). The results are compared to ensure
39#   that sqlite3_blocking_step() resulted in higher transaction throughput.
40#
41
42if {[info commands sqlite3_blocking_step] eq ""} {
43  finish_test
44  return
45}
46db close
47set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
48source $testdir/thread_common.tcl
49
50# Number of threads to run simultaneously.
51#
52set nThread 3
53set nSecond 5
54
55# The Tcl script executed by each of the $nThread threads used by this test.
56#
57set ThreadProgram {
58
59  # Proc used by threads to execute SQL.
60  #
61  proc execsql_blocking {db zSql} {
62    set lRes [list]
63    set rc SQLITE_OK
64
65    while {$rc=="SQLITE_OK" && $zSql ne ""} {
66      set STMT [sqlite3_prepare_v2 $db $zSql -1 zSql]
67      while {[set rc [$::xStep $STMT]] eq "SQLITE_ROW"} {
68        for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
69          lappend lRes [sqlite3_column_text $STMT 0]
70        }
71      }
72      set rc [sqlite3_finalize $STMT]
73    }
74
75    if {$rc != "SQLITE_OK"} { error "$rc [sqlite3_errmsg $db]" }
76    return $lRes
77  }
78
79  proc select_one {args} {
80    set n [llength $args]
81    lindex $args [expr int($n*rand())]
82  }
83
84  # Open a database connection. Attach the two auxillary databases.
85  set ::DB [sqlite3_open test.db]
86  execsql_blocking $::DB {
87    ATTACH 'test2.db' AS aux2;
88    ATTACH 'test3.db' AS aux3;
89  }
90
91  # This loop runs for ~20 seconds.
92  #
93  set iStart [clock_seconds]
94  while { ([clock_seconds]-$iStart) < $nSecond } {
95
96    # Each transaction does 3 operations. Each operation is either a read
97    # or write of a randomly selected table (t1, t2 or t3). Set the variables
98    # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement
99    # each operation.
100    #
101    for {set ii 1} {$ii <= 3} {incr ii} {
102      set SQL($ii) [string map [list xxx [select_one t1 t2 t3]] [select_one {
103            SELECT
104              (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a)
105              FROM xxx WHERE a!=(SELECT max(a) FROM xxx);
106      } {
107            DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx);
108            INSERT INTO xxx SELECT NULL, total(a) FROM xxx;
109      }]]
110    }
111
112    # Execute the SQL transaction.
113    #
114    set rc [catch { execsql_blocking $::DB "
115        BEGIN;
116          $SQL(1);
117          $SQL(2);
118          $SQL(3);
119        COMMIT;
120      "
121    } msg]
122
123    if {$rc && [string match "SQLITE_LOCKED*" $msg]} {
124      # Hit an SQLITE_LOCKED error. Rollback the current transaction.
125      execsql_blocking $::DB ROLLBACK
126    } elseif {$rc} {
127      # Hit some other kind of error. This is a malfunction.
128      error $msg
129    } else {
130      # No error occured. Check that any SELECT statements in the transaction
131      # returned "1". Otherwise, the invariant was false, indicating that
132      # some malfunction has occured.
133      foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }
134    }
135  }
136
137  # Close the database connection and return 0.
138  #
139  sqlite3_close $::DB
140  expr 0
141}
142
143foreach {iTest xStep} {1 sqlite3_blocking_step 2 sqlite3_step} {
144  file delete -force test.db test2.db test3.db
145
146  set ThreadSetup "set xStep $xStep ; set nSecond $nSecond"
147
148  # Set up the database schema used by this test. Each thread opens file
149  # test.db as the main database, then attaches files test2.db and test3.db
150  # as auxillary databases. Each file contains a single table (t1, t2 and t3, in
151  # files test.db, test2.db and test3.db, respectively).
152  #
153  do_test notify2-$iTest.1.1 {
154    sqlite3 db test.db
155    execsql {
156      ATTACH 'test2.db' AS aux2;
157      ATTACH 'test3.db' AS aux3;
158      CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b);
159      CREATE TABLE aux2.t2(a INTEGER PRIMARY KEY, b);
160      CREATE TABLE aux3.t3(a INTEGER PRIMARY KEY, b);
161      INSERT INTO t1 SELECT NULL, 0;
162      INSERT INTO t2 SELECT NULL, 0;
163      INSERT INTO t3 SELECT NULL, 0;
164    }
165  } {}
166  do_test notify2-$iTest.1.2 {
167    db close
168  } {}
169
170
171  # Launch $nThread threads. Then wait for them to finish.
172  #
173  puts "Running $xStep test for $nSecond seconds"
174  unset -nocomplain finished
175  for {set ii 0} {$ii < $nThread} {incr ii} {
176    thread_spawn finished($ii) $ThreadSetup $ThreadProgram
177  }
178  for {set ii 0} {$ii < $nThread} {incr ii} {
179    do_test notify2-$iTest.2.$ii {
180      if {![info exists finished($ii)]} { vwait finished($ii) }
181      set finished($ii)
182    } {0}
183  }
184
185  # Count the total number of succesful writes.
186  do_test notify2-$iTest.3.1 {
187    sqlite3 db test.db
188    execsql {
189      ATTACH 'test2.db' AS aux2;
190      ATTACH 'test3.db' AS aux3;
191    }
192    set anWrite($xStep) [execsql {
193      SELECT (SELECT max(a) FROM t1)
194           + (SELECT max(a) FROM t2)
195           + (SELECT max(a) FROM t3)
196    }]
197    db close
198  } {}
199}
200
201do_test notify2-3 {
202  expr {$anWrite(sqlite3_blocking_step) > $anWrite(sqlite3_step)}
203} {1}
204
205sqlite3_enable_shared_cache $::enable_shared_cache
206finish_test
207
208