AryaWu/sqlite

1# 2009 March 112#3# The author disclaims copyright to this source code.  In place of4# 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.tcl19if {[run_thread_tests]==0} { finish_test ; return }20ifcapable !shared_cache {21  finish_test22  return23}24 25db close26 27# Use shared-cache mode for these tests.28# 29set ::enable_shared_cache [sqlite3_enable_shared_cache]30sqlite3_enable_shared_cache 131 32#-------------------------------------------------------------------------33# This test attempts to hit the race condition fixed by commit [6363].34#35proc runsql {zSql {db {}}} {36  set rc SQLITE_OK37  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 $rc43}44do_test thread005-1.1 {45  sqlite3 db test.db46  db eval { CREATE TABLE t1(a, b) }47  db close48} {}49for {set ii 2} {$ii < 500} {incr ii} {50  unset -nocomplain finished51  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 statement62    # can be executed succesfully. However, if the race-condition is not63    # 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 will65    # 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-cache77# mode at one point. The test uses two threads; each has a database connection78# 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 current101# 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, but106# the subsequent UPDATE statement sets its "b" value to an integer107# immediately afterwards.108#109# However, before the race-condition was fixed, if the reader's SELECT110# statement hit an error (say an SQLITE_LOCKED) at the same time as the111# writer was executing the UPDATE statement, then it could incorrectly112# 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-condition117# when using an ATTACHed database. There doesn't seem to be any reason118# for this, other than that operating on an ATTACHed database means there119# are a few more mutex grabs and releases during the window of time open120# for the race-condition. Maybe this encourages the scheduler to context121# switch or something...122#123 124forcedelete test.db test2.db125unset -nocomplain finished126 127do_test thread005-2.1 {128  sqlite3 db test.db129  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 close136} {}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_OK145 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 $lRes158  }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 $::DB190        error $msg191      }192    } elseif {$msg ne "0"} {193      set result "failed"194    }195  }196 197  sqlite3_close $::DB198  set result199}200 201# There is a race-condition in btree.c that means that if two threads202# attempt to open the same database at roughly the same time, and there203# does not already exist a shared-cache corresponding to that database,204# then two shared-caches can be created instead of one. Things still more205# or less work, but the two database connections do not use the same206# shared-cache.207#208# If the threads run by this test hit this race-condition, the tests209# fail (because SQLITE_BUSY may be unexpectedly returned instead of210# SQLITE_LOCKED). To prevent this from happening, open a couple of211# connections to test.db and test2.db now to make sure that there are212# already shared-caches in memory for all databases opened by the213# test threads.214#215sqlite3 db test.db216sqlite3 db test2.db217 218puts "Running thread-tests for ~20 seconds"219thread_spawn finished(0) {set isWriter 0} $ThreadProgram220thread_spawn finished(1) {set isWriter 1} $ThreadProgram221if {![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.db233  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_cache238finish_test239