1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
|
# 2009 March 11
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test a race-condition that shows up in shared-cache mode.
#
# $Id: thread005.test,v 1.5 2009/03/26 14:48:07 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
if {[run_thread_tests]==0} { finish_test ; return }
ifcapable !shared_cache {
finish_test
return
}
db close
# Use shared-cache mode for these tests.
#
set ::enable_shared_cache [sqlite3_enable_shared_cache]
sqlite3_enable_shared_cache 1
#-------------------------------------------------------------------------
# This test attempts to hit the race condition fixed by commit [6363].
#
proc runsql {zSql {db {}}} {
set rc SQLITE_OK
while {$rc=="SQLITE_OK" && $zSql ne ""} {
set STMT [sqlite3_prepare_v2 $db $zSql -1 zSql]
while {[set rc [sqlite3_step $STMT]] eq "SQLITE_ROW"} { }
set rc [sqlite3_finalize $STMT]
}
return $rc
}
do_test thread005-1.1 {
sqlite3 db test.db
db eval { CREATE TABLE t1(a, b) }
db close
} {}
for {set ii 2} {$ii < 500} {incr ii} {
unset -nocomplain finished
thread_spawn finished(0) {sqlite3_open test.db}
thread_spawn finished(1) {sqlite3_open test.db}
if {![info exists finished(0)]} { vwait finished(0) }
if {![info exists finished(1)]} { vwait finished(1) }
do_test thread005-1.$ii {
runsql { BEGIN } $finished(0)
runsql { INSERT INTO t1 VALUES(1, 2) } $finished(0)
# If the race-condition was hit, then $finished(0 and $finished(1)
# will not use the same pager cache. In this case the next statement
# can be executed succesfully. However, if the race-condition is not
# hit, then $finished(1) will be blocked by the write-lock held by
# $finished(0) on the shared-cache table t1 and the statement will
# return SQLITE_LOCKED.
#
runsql { SELECT * FROM t1 } $finished(1)
} {SQLITE_LOCKED}
sqlite3_close $finished(0)
sqlite3_close $finished(1)
}
#-------------------------------------------------------------------------
# This test tries to exercise a race-condition that existed in shared-cache
# mode at one point. The test uses two threads; each has a database connection
# open on the same shared cache. The schema of the database is:
#
# CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
#
# One thread is a reader and the other thread a reader and a writer. The
# writer thread repeats the following transaction as fast as possible:
#
# BEGIN;
# DELETE FROM t1 WHERE a = (SELECT max(a) FROM t1);
# INSERT INTO t1 VALUES(NULL, NULL);
# UPDATE t1 SET b = a WHERE a = (SELECT max(a) FROM t1);
# SELECT count(*) FROM t1 WHERE b IS NULL;
# COMMIT;
#
# The reader thread does the following over and over as fast as possible:
#
# BEGIN;
# SELECT count(*) FROM t1 WHERE b IS NULL;
# COMMIT;
#
# The test runs for 20 seconds or until one of the "SELECT count(*)"
# statements returns a non-zero value. If an SQLITE_LOCKED error occurs,
# the connection issues a ROLLBACK immediately to abandon the current
# transaction.
#
# If everything is working correctly, the "SELECT count(*)" statements
# should never return a value other than 0. The "INSERT" statement
# executed by the writer adds a row with "b IS NULL" to the table, but
# the subsequent UPDATE statement sets its "b" value to an integer
# immediately afterwards.
#
# However, before the race-condition was fixed, if the reader's SELECT
# statement hit an error (say an SQLITE_LOCKED) at the same time as the
# writer was executing the UPDATE statement, then it could incorrectly
# rollback the statement-transaction belonging to the UPDATE statement.
# The UPDATE statement would still be reported as successful to the user,
# but it would have no effect on the database contents.
#
# Note that it has so far only proved possible to hit this race-condition
# when using an ATTACHed database. There doesn't seem to be any reason
# for this, other than that operating on an ATTACHed database means there
# are a few more mutex grabs and releases during the window of time open
# for the race-condition. Maybe this encourages the scheduler to context
# switch or something...
#
forcedelete test.db test2.db
unset -nocomplain finished
do_test thread005-2.1 {
sqlite3 db test.db
execsql { ATTACH 'test2.db' AS aux }
execsql {
CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b UNIQUE);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
}
db close
} {}
set ThreadProgram {
proc execsql {zSql {db {}}} {
if {$db eq ""} {set db $::DB}
set lRes [list]
set rc SQLITE_OK
while {$rc=="SQLITE_OK" && $zSql ne ""} {
set STMT [sqlite3_prepare_v2 $db $zSql -1 zSql]
while {[set rc [sqlite3_step $STMT]] eq "SQLITE_ROW"} {
for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
lappend lRes [sqlite3_column_text $STMT 0]
}
}
set rc [sqlite3_finalize $STMT]
}
if {$rc != "SQLITE_OK"} { error "$rc [sqlite3_errmsg $db]" }
return $lRes
}
if {$isWriter} {
set Sql {
BEGIN;
DELETE FROM t1 WHERE a = (SELECT max(a) FROM t1);
INSERT INTO t1 VALUES(NULL, NULL);
UPDATE t1 SET b = a WHERE a = (SELECT max(a) FROM t1);
SELECT count(*) FROM t1 WHERE b IS NULL;
COMMIT;
}
} else {
set Sql {
BEGIN;
SELECT count(*) FROM t1 WHERE b IS NULL;
COMMIT;
}
}
set ::DB [sqlite3_open test.db]
execsql { ATTACH 'test2.db' AS aux }
set result "ok"
set finish [expr [clock_seconds]+5]
while {$result eq "ok" && [clock_seconds] < $finish} {
set rc [catch {execsql $Sql} msg]
if {$rc} {
if {[string match "SQLITE_LOCKED*" $msg]} {
catch { execsql ROLLBACK }
} else {
sqlite3_close $::DB
error $msg
}
} elseif {$msg ne "0"} {
set result "failed"
}
}
sqlite3_close $::DB
set result
}
# There is a race-condition in btree.c that means that if two threads
# attempt to open the same database at roughly the same time, and there
# does not already exist a shared-cache corresponding to that database,
# then two shared-caches can be created instead of one. Things still more
# or less work, but the two database connections do not use the same
# shared-cache.
#
# If the threads run by this test hit this race-condition, the tests
# fail (because SQLITE_BUSY may be unexpectedly returned instead of
# SQLITE_LOCKED). To prevent this from happening, open a couple of
# connections to test.db and test2.db now to make sure that there are
# already shared-caches in memory for all databases opened by the
# test threads.
#
sqlite3 db test.db
sqlite3 db test2.db
puts "Running thread-tests for ~20 seconds"
thread_spawn finished(0) {set isWriter 0} $ThreadProgram
thread_spawn finished(1) {set isWriter 1} $ThreadProgram
if {![info exists finished(0)]} { vwait finished(0) }
if {![info exists finished(1)]} { vwait finished(1) }
catch { db close }
catch { db2 close }
do_test thread005-2.2 {
list $finished(0) $finished(1)
} {ok ok}
do_test thread005-2.3 {
sqlite3 db test.db
execsql { ATTACH 'test2.db' AS aux }
execsql { SELECT count(*) FROM t1 WHERE b IS NULL }
} {0}
sqlite3_enable_shared_cache $::enable_shared_cache
finish_test
|
