xref: /sqlite-3.40.0/test/thread005.test (revision 1f4969a1) 
1# 2009 March 11
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# Test a race-condition that shows up in shared-cache mode.
13#
14# $Id: thread005.test,v 1.2 2009/03/16 17:07:57 drh Exp $
15
16set testdir [file dirname $argv0]
17
18source $testdir/tester.tcl
19ifcapable !mutex||!shared_cache {
20  return
21}
22source $testdir/thread_common.tcl
23if {[info commands sqlthread] eq ""} {
24  return
25}
26
27#-------------------------------------------------------------------------
28# This test tries to exercise a race-condition that existed in shared-cache
29# mode at one point. The test uses two threads; each has a database connection
30# open on the same shared cache. The schema of the database is:
31#
32#    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
33#
34# One thread is a reader and the other thread a reader and a writer. The
35# writer thread repeats the following transaction as fast as possible:
36#
37#      BEGIN;
38#        DELETE FROM t1 WHERE a = (SELECT max(a) FROM t1);
39#        INSERT INTO t1 VALUES(NULL, NULL);
40#        UPDATE t1 SET b = a WHERE a = (SELECT max(a) FROM t1);
41#        SELECT count(*) FROM t1 WHERE b IS NULL;
42#      COMMIT;
43#
44# The reader thread does the following over and over as fast as possible:
45#
46#      BEGIN;
47#        SELECT count(*) FROM t1 WHERE b IS NULL;
48#      COMMIT;
49#
50# The test runs for 20 seconds or until one of the "SELECT count(*)"
51# statements returns a non-zero value. If an SQLITE_LOCKED error occurs,
52# the connection issues a ROLLBACK immediately to abandon the current
53# transaction.
54#
55# If everything is working correctly, the "SELECT count(*)" statements
56# should never return a value other than 0. The "INSERT" statement
57# executed by the writer adds a row with "b IS NULL" to the table, but
58# the subsequent UPDATE statement sets its "b" value to an integer
59# immediately afterwards.
60#
61# However, before the race-condition was fixed, if the reader's SELECT
62# statement hit an error (say an SQLITE_LOCKED) at the same time as the
63# writer was executing the UPDATE statement, then it could incorrectly
64# rollback the statement-transaction belonging to the UPDATE statement.
65# The UPDATE statement would still be reported as successful to the user,
66# but it would have no effect on the database contents.
67#
68# Note that it has so far only proved possible to hit this race-condition
69# when using an ATTACHed database. There doesn't seem to be any reason
70# for this, other than that operating on an ATTACHed database means there
71# are a few more mutex grabs and releases during the window of time open
72# for the race-condition. Maybe this encourages the scheduler to context
73# switch or something...
74#
75
76# Use shared-cache mode for this test.
77#
78db close
79set ::enable_shared_cache [sqlite3_enable_shared_cache]
80sqlite3_enable_shared_cache 1
81
82file delete -force test.db test2.db
83unset -nocomplain finished
84
85do_test thread005-1.1 {
86  sqlite3 db test.db
87  execsql { ATTACH 'test2.db' AS aux }
88  execsql {
89    CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b UNIQUE);
90    INSERT INTO t1 VALUES(1, 1);
91    INSERT INTO t1 VALUES(2, 2);
92  }
93  db close
94} {}
95
96set ThreadProgram {
97  proc execsql {zSql {db {}}} {
98    if {$db eq ""} {set db $::DB}
99
100    set lRes [list]
101    set rc SQLITE_OK
102
103    while {$rc=="SQLITE_OK" && $zSql ne ""} {
104      set STMT [sqlite3_prepare_v2 $db $zSql -1 zSql]
105      while {[set rc [sqlite3_step $STMT]] eq "SQLITE_ROW"} {
106        for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
107          lappend lRes [sqlite3_column_text $STMT 0]
108        }
109      }
110      set rc [sqlite3_finalize $STMT]
111    }
112
113    if {$rc != "SQLITE_OK"} { error "$rc [sqlite3_errmsg $db]" }
114    return $lRes
115  }
116
117  if {$isWriter} {
118    set Sql {
119      BEGIN;
120        DELETE FROM t1 WHERE a = (SELECT max(a) FROM t1);
121        INSERT INTO t1 VALUES(NULL, NULL);
122        UPDATE t1 SET b = a WHERE a = (SELECT max(a) FROM t1);
123        SELECT count(*) FROM t1 WHERE b IS NULL;
124      COMMIT;
125    }
126  } else {
127    set Sql {
128      BEGIN;
129      SELECT count(*) FROM t1 WHERE b IS NULL;
130      COMMIT;
131    }
132  }
133
134  set ::DB [sqlite3_open test.db]
135
136  execsql { ATTACH 'test2.db' AS aux }
137
138  set result "ok"
139  set finish [expr [clock_seconds]+5]
140  while {$result eq "ok" && [clock_seconds] < $finish} {
141    set rc [catch {execsql $Sql} msg]
142    if {$rc} {
143      if {[string match "SQLITE_LOCKED*" $msg]} {
144        catch { execsql ROLLBACK }
145      } else {
146        error $msg
147      }
148    } elseif {$msg ne "0"} {
149      set result "failed"
150    }
151  }
152
153  sqlite3_close $::DB
154  set result
155}
156
157puts "Running thread-tests for ~20 seconds"
158thread_spawn finished(0) {set isWriter 0} $ThreadProgram
159thread_spawn finished(1) {set isWriter 1} $ThreadProgram
160if {![info exists finished(0)]} { vwait finished(0) }
161if {![info exists finished(1)]} { vwait finished(1) }
162
163do_test thread005-1.2 {
164  list $finished(0) $finished(1)
165} {ok ok}
166
167do_test thread005-1.3 {
168  sqlite3 db test.db
169  execsql { ATTACH 'test2.db' AS aux }
170  execsql { SELECT count(*) FROM t1 WHERE b IS NULL }
171} {0}
172
173sqlite3_enable_shared_cache $::enable_shared_cache
174finish_test
175