test/thread005.test - external/github.com/sqlite/sqlite.git - Git at Google

 # 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
	# 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