gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/thread005.test

# 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