/ Check-in [ac348ae2]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add extra test cases for blocking checkpoints.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | blocking-checkpoint
Files: files | file ages | folders
SHA1: ac348ae25cb0239dc525bb473cc83cdb1b3de205
User & Date: dan 2010-11-19 07:17:10
Context
2010-11-19
09:58
Add file test/tt3_checkpoint.c that adds a multi-threaded test for blocking checkpoints to threadtest3. check-in: 648dd157 user: dan tags: blocking-checkpoint
07:17
Add extra test cases for blocking checkpoints. check-in: ac348ae2 user: dan tags: blocking-checkpoint
2010-11-18
19:28
Fixes for SQLITE_BUSY handling in blocking checkpoint code. check-in: 4c663a4d user: dan tags: blocking-checkpoint
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/wal5.test.

135
136
137
138
139
140
141



142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200



201


202























































203

proc file_page_counts {} {
  list [db_page_count  test.db ] \
       [wal_page_count test.db ] \
       [db_page_count  test.db2] \
       [wal_page_count test.db2]
}




do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.1.$tn.1 {
    sql1 {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}

  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.1.$tn.3 { sql1 { PRAGMA wal_checkpoint } } {0 5 5}
  do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5}
}

do_multiclient_test tn {

  setup_and_attach_aux

  do_test 2.2.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
      INSERT INTO t2 VALUES(3, 4);
    }
  } {}

  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7}
  do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.2.$tn.4 { sql1 { PRAGMA wal_checkpoint = RESTART } } {1 5 5}
  do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7}
}

do_multiclient_test tn {

  setup_and_attach_aux

  do_test 2.3.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}

  do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {}
  do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {}
  do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7}

  do_test 2.3.$tn.7 { sql1 { PRAGMA wal_checkpoint = FULL } } {1 7 5}
  do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7}
}































































finish_test








>
>
>










<






<

<









<







<

<








<





<




>
>
>
|
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

155
156
157
158
159
160

161

162
163
164
165
166
167
168
169
170

171
172
173
174
175
176
177

178

179
180
181
182
183
184
185
186

187
188
189
190
191

192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
proc file_page_counts {} {
  list [db_page_count  test.db ] \
       [wal_page_count test.db ] \
       [db_page_count  test.db2] \
       [wal_page_count test.db2]
}

# Test that executing "PRAGMA wal_checkpoint" checkpoints all attached
# databases, not just the main db.
#
do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.1.$tn.1 {
    sql1 {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}

  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.1.$tn.3 { sql1 { PRAGMA wal_checkpoint } } {0 5 5}
  do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5}
}

do_multiclient_test tn {

  setup_and_attach_aux

  do_test 2.2.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
      INSERT INTO t2 VALUES(3, 4);
    }
  } {}

  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7}
  do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.2.$tn.4 { sql1 { PRAGMA wal_checkpoint = RESTART } } {1 5 5}
  do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7}
}

do_multiclient_test tn {

  setup_and_attach_aux

  do_test 2.3.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}

  do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {}
  do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {}
  do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7}

  do_test 2.3.$tn.7 { sql1 { PRAGMA wal_checkpoint = FULL } } {1 7 5}
  do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7}
}

# Check that checkpoints block on the correct locks. And respond correctly
# if they cannot obtain those locks. There are three locks that a checkpoint
# may block on (in the following order):
#
#   1. The writer lock: FULL and RESTART checkpoints block until any writer
#      process releases its lock.
#
#   2. Readers using part of the log file. FULL and RESTART checkpoints block
#      until readers using part (but not all) of the log file have finished.
#
#   3. Readers using any of the log file. After copying data into the
#      database file, RESTART checkpoints block until readers using any part
#      of the log file have finished.
#
foreach {tn1 checkpoint busy_on ckpt_expected expected} {
  1   PASSIVE   -   {0 5 5}   -
  2   TYPO      -   {0 5 5}   -

  3   FULL      -   {0 7 7}   2
  4   FULL      1   {1 5 5}   1
  5   FULL      2   {1 7 5}   2
  6   FULL      3   {0 7 7}   2

  7   RESTART   -   {0 7 7}   3
  8   RESTART   1   {1 5 5}   1
  9   RESTART   2   {1 7 5}   2
  10  RESTART   3   {1 7 7}   3

} {
  do_multiclient_test tn {
    setup_and_attach_aux
  
    proc busyhandler {x} {
      set ::max_busyhandler $x
      if {$::busy_on!="-" && $x==$::busy_on} { return 1 }
      switch -- $x {
        1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" }
        2 { sql3 "COMMIT" }
        3 { sql2 "COMMIT" }
      }
      return 0
    }
    set ::max_busyhandler -
  
    do_test 2.4.$tn1.$tn.1 {
      sql1 {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
      }
      sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) }
      sql3 { BEGIN; SELECT * FROM t1 }
    } {1 2}
  
    do_test 2.4.$tn1.$tn.2 {
      code1 { db busy busyhandler }
      sql1 "PRAGMA wal_checkpoint = $checkpoint"
    } $ckpt_expected
    do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected
  }
}


finish_test