xref: /sqlite-3.40.0/test/thread005.test (revision 20736d82) 
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.1 2009/03/12 14:43:28 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}
26
27#-------------------------------------------------------------------------
28# This test tries to exercise a race-condition that existed in shared-cache
29# mode at one point. The test uses two threads; each has a database connection
30# open on the same shared cache. The schema of the database is:
31#
32#    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
33#
34# One thread is a reader and the other thread a reader and a writer. The
35# writer thread repeats the following transaction as fast as possible:
36#
37#      BEGIN;
38#        DELETE FROM t1 WHERE a = (SELECT max(a) FROM t1);
39#        INSERT INTO t1 VALUES(NULL, NULL);
40#        UPDATE t1 SET b = a WHERE a = (SELECT max(a) FROM t1);
41#        SELECT count(*) FROM t1 WHERE b IS NULL;
42#      COMMIT;
43#
44# The reader thread does the following over and over as fast as possible:
45#
46#      BEGIN;
47#        SELECT count(*) FROM t1 WHERE b IS NULL;
48#      COMMIT;
49#
50# The test runs for 20 seconds or until one of the "SELECT count(*)"
51# statements returns a non-zero value. If an SQLITE_LOCKED error occurs,
52# the connection issues a ROLLBACK immediately to abandon the current
53# transaction.
54#
55# If everything is working correctly, the "SELECT count(*)" statements
56# should never return a value other than 0. The "INSERT" statement
57# executed by the writer adds a row with "b IS NULL" to the table, but
58# the subsequent UPDATE statement sets its "b" value to an integer
59# immediately afterwards.
60#
61# However, before the race-condition was fixed, if the reader's SELECT
62# statement hit an error (say an SQLITE_LOCKED) at the same time as the
63# writer was executing the UPDATE statement, then it could incorrectly
64# rollback the statement-transaction belonging to the UPDATE statement.
65# The UPDATE statement would still be reported as successful to the user,
66# but it would have no effect on the database contents.
67#
68# Note that it has so far only proved possible to hit this race-condition
69# when using an ATTACHed database. There doesn't seem to be any reason
70# for this, other than that operating on an ATTACHed database means there
71# are a few more mutex grabs and releases during the window of time open
72# for the race-condition. Maybe this encourages the scheduler to context
73# switch or something...
74#
75
76# Use shared-cache mode for this test.
77#
78db close
79set ::enable_shared_cache [sqlite3_enable_shared_cache]
80sqlite3_enable_shared_cache 1
81
82file delete -force test.db test2.db
83
84do_test thread005-1.1 {
85  sqlite3 db test.db
86  execsql { ATTACH 'test2.db' AS aux }
87  execsql {
88    CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b UNIQUE);
89    INSERT INTO t1 VALUES(1, 1);
90    INSERT INTO t1 VALUES(2, 2);
91  }
92  db close
93} {}
94
95set ThreadProgram {
96  proc execsql {zSql {db {}}} {
97    if {$db eq ""} {set db $::DB}
98
99    set lRes [list]
100    set rc SQLITE_OK
101
102    while {$rc=="SQLITE_OK" && $zSql ne ""} {
103      set STMT [sqlite3_prepare_v2 $db $zSql -1 zSql]
104      while {[set rc [sqlite3_step $STMT]] eq "SQLITE_ROW"} {
105        for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
106          lappend lRes [sqlite3_column_text $STMT 0]
107        }
108      }
109      set rc [sqlite3_finalize $STMT]
110    }
111
112    if {$rc != "SQLITE_OK"} { error "$rc [sqlite3_errmsg $db]" }
113    return $lRes
114  }
115
116  if {$isWriter} {
117    set Sql {
118      BEGIN;
119        DELETE FROM t1 WHERE a = (SELECT max(a) FROM t1);
120        INSERT INTO t1 VALUES(NULL, NULL);
121        UPDATE t1 SET b = a WHERE a = (SELECT max(a) FROM t1);
122        SELECT count(*) FROM t1 WHERE b IS NULL;
123      COMMIT;
124    }
125  } else {
126    set Sql {
127      BEGIN;
128      SELECT count(*) FROM t1 WHERE b IS NULL;
129      COMMIT;
130    }
131  }
132
133  set ::DB [sqlite3_open test.db]
134
135  execsql { ATTACH 'test2.db' AS aux }
136
137  set result "ok"
138  set finish [expr [clock_seconds]+5]
139  while {$result eq "ok" && [clock_seconds] < $finish} {
140    set rc [catch {execsql $Sql} msg]
141    if {$rc} {
142      if {[string match "SQLITE_LOCKED*" $msg]} {
143        catch { execsql ROLLBACK }
144      } else {
145        error $msg
146      }
147    } elseif {$msg ne "0"} {
148      set result "failed"
149    }
150  }
151
152  sqlite3_close $::DB
153  set result
154}
155
156puts "Running thread-tests for ~20 seconds"
157thread_spawn finished(0) {set isWriter 0} $ThreadProgram
158thread_spawn finished(1) {set isWriter 1} $ThreadProgram
159if {![info exists finished(0)]} { vwait finished(0) }
160if {![info exists finished(1)]} { vwait finished(1) }
161
162do_test thread005-1.2 {
163  list $finished(0) $finished(1)
164} {ok ok}
165
166do_test thread005-1.3 {
167  sqlite3 db test.db
168  execsql { ATTACH 'test2.db' AS aux }
169  execsql { SELECT count(*) FROM t1 WHERE b IS NULL }
170} {0}
171
172sqlite3_enable_shared_cache $::enable_shared_cache
173finish_test
174
175