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