xref: /sqlite-3.40.0/test/thread005.test (revision df0f3c06) 
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.3 2009/03/20 10:24:04 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
83unset -nocomplain finished
84
85do_test thread005-1.1 {
86  sqlite3 db test.db
87  execsql { ATTACH 'test2.db' AS aux }
88  execsql {
89    CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b UNIQUE);
90    INSERT INTO t1 VALUES(1, 1);
91    INSERT INTO t1 VALUES(2, 2);
92  }
93  db close
94} {}
95
96
97set ThreadProgram {
98  proc execsql {zSql {db {}}} {
99    if {$db eq ""} {set db $::DB}
100
101    set lRes [list]
102    set rc SQLITE_OK
103
104    while {$rc=="SQLITE_OK" && $zSql ne ""} {
105      set STMT [sqlite3_prepare_v2 $db $zSql -1 zSql]
106      while {[set rc [sqlite3_step $STMT]] eq "SQLITE_ROW"} {
107        for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
108          lappend lRes [sqlite3_column_text $STMT 0]
109        }
110      }
111      set rc [sqlite3_finalize $STMT]
112    }
113
114    if {$rc != "SQLITE_OK"} { error "$rc [sqlite3_errmsg $db]" }
115    return $lRes
116  }
117
118  if {$isWriter} {
119    set Sql {
120      BEGIN;
121        DELETE FROM t1 WHERE a = (SELECT max(a) FROM t1);
122        INSERT INTO t1 VALUES(NULL, NULL);
123        UPDATE t1 SET b = a WHERE a = (SELECT max(a) FROM t1);
124        SELECT count(*) FROM t1 WHERE b IS NULL;
125      COMMIT;
126    }
127  } else {
128    set Sql {
129      BEGIN;
130      SELECT count(*) FROM t1 WHERE b IS NULL;
131      COMMIT;
132    }
133  }
134
135  set ::DB [sqlite3_open test.db]
136
137  execsql { ATTACH 'test2.db' AS aux }
138
139  set result "ok"
140  set finish [expr [clock_seconds]+5]
141  while {$result eq "ok" && [clock_seconds] < $finish} {
142    set rc [catch {execsql $Sql} msg]
143    if {$rc} {
144      if {[string match "SQLITE_LOCKED*" $msg]} {
145        catch { execsql ROLLBACK }
146      } else {
147        sqlite3_close $::DB
148        error $msg
149      }
150    } elseif {$msg ne "0"} {
151      set result "failed"
152    }
153  }
154
155  sqlite3_close $::DB
156  set result
157}
158
159# There is a race-condition in btree.c that means that if two threads
160# attempt to open the same database at roughly the same time, and there
161# does not already exist a shared-cache corresponding to that database,
162# then two shared-caches can be created instead of one. Things still more
163# or less work, but the two database connections do not use the same
164# shared-cache.
165#
166# If the threads run by this test hit this race-condition, the tests
167# fail (because SQLITE_BUSY may be unexpectedly returned instead of
168# SQLITE_LOCKED). To prevent this from happening, open a couple of
169# connections to test.db and test2.db now to make sure that there are
170# already shared-caches in memory for all databases opened by the
171# test threads.
172#
173sqlite3 db test.db
174sqlite3 db test2.db
175
176puts "Running thread-tests for ~20 seconds"
177thread_spawn finished(0) {set isWriter 0} $ThreadProgram
178thread_spawn finished(1) {set isWriter 1} $ThreadProgram
179if {![info exists finished(0)]} { vwait finished(0) }
180if {![info exists finished(1)]} { vwait finished(1) }
181
182catch { db close }
183catch { db2 close }
184
185do_test thread005-1.2 {
186  list $finished(0) $finished(1)
187} {ok ok}
188
189do_test thread005-1.3 {
190  sqlite3 db test.db
191  execsql { ATTACH 'test2.db' AS aux }
192  execsql { SELECT count(*) FROM t1 WHERE b IS NULL }
193} {0}
194
195sqlite3_enable_shared_cache $::enable_shared_cache
196finish_test
197