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