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 16 set testdir [file dirname $argv0] 17 18 source $testdir/tester.tcl 19 if {[run_thread_tests]==0} { finish_test ; return } 20 ifcapable !shared_cache { 21 finish_test 22 return 23 } 24 25 db close 26 27 # Use shared-cache mode for these tests. 28 # 29 set ::enable_shared_cache [sqlite3_enable_shared_cache] 30 sqlite3_enable_shared_cache 1 31 32 #------------------------------------------------------------------------- 33 # This test attempts to hit the race condition fixed by commit [6363]. 34 # 35 proc 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 } 44 do_test thread005-1.1 { 45 sqlite3 db test.db 46 db eval { CREATE TABLE t1(a, b) } 47 db close 48 } {} 49 for {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 124 file delete -force test.db test2.db 125 unset -nocomplain finished 126 127 do_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 139 set 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 # 215 sqlite3 db test.db 216 sqlite3 db test2.db 217 218 puts "Running thread-tests for ~20 seconds" 219 thread_spawn finished(0) {set isWriter 0} $ThreadProgram 220 thread_spawn finished(1) {set isWriter 1} $ThreadProgram 221 if {![info exists finished(0)]} { vwait finished(0) } 222 if {![info exists finished(1)]} { vwait finished(1) } 223 224 catch { db close } 225 catch { db2 close } 226 227 do_test thread005-2.2 { 228 list $finished(0) $finished(1) 229 } {ok ok} 230 231 do_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 237 sqlite3_enable_shared_cache $::enable_shared_cache 238 finish_test 239
