Debian Codegraph @

test/wal3.test

raw
1 # 2010 April 13
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 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the operation of the library in
13 # "PRAGMA journal_mode=WAL" mode.
14 #
15 # TESTRUNNER: slow
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19 source $testdir/lock_common.tcl
20 source $testdir/wal_common.tcl
21 source $testdir/malloc_common.tcl
22 ifcapable !wal {finish_test ; return }
23
24 set a_string_counter 1
25 proc a_string {n} {
26 global a_string_counter
27 incr a_string_counter
28 string range [string repeat "${a_string_counter}." $n] 1 $n
29 }
30 db func a_string a_string
31
32 #-------------------------------------------------------------------------
33 # When a rollback or savepoint rollback occurs, the client may remove
34 # elements from one of the hash tables in the wal-index. This block
35 # of test cases tests that nothing appears to go wrong when this is
36 # done.
37 #
38 do_test wal3-1.0 {
39 execsql {
40 PRAGMA cache_size = 2000;
41 PRAGMA page_size = 1024;
42 PRAGMA auto_vacuum = off;
43 PRAGMA synchronous = normal;
44 PRAGMA journal_mode = WAL;
45 PRAGMA wal_autocheckpoint = 0;
46 BEGIN;
47 CREATE TABLE t1(x);
48 INSERT INTO t1 VALUES( a_string(800) ); /* 1 */
49 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */
50 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */
51 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */
52 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */
53 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */
54 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 64 */
55 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 128*/
56 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 256 */
57 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 512 */
58 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 1024 */
59 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2048 */
60 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 1970; /* 4018 */
61 COMMIT;
62 PRAGMA cache_size = 10;
63 }
64 set x [wal_frame_count test.db-wal 1024]
65 if {[permutation]=="memsubsys1"} {
66 if {$x==4251 || $x==4290} {set x 4056}
67 }
68 set x
69 } 4056
70
71 for {set i 1} {$i < 50} {incr i} {
72
73 do_test wal3-1.$i.1 {
74 set str [a_string 800]
75 execsql { UPDATE t1 SET x = $str WHERE rowid = $i }
76 lappend L [wal_frame_count test.db-wal 1024]
77 execsql {
78 BEGIN;
79 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 100;
80 ROLLBACK;
81 PRAGMA integrity_check;
82 }
83 } {ok}
84
85 # Check that everything looks OK from the point of view of an
86 # external connection.
87 #
88 sqlite3 db2 test.db
89 do_test wal3-1.$i.2 {
90 execsql { SELECT count(*) FROM t1 } db2
91 } 4018
92 do_test wal3-1.$i.3 {
93 execsql { SELECT x FROM t1 WHERE rowid = $i }
94 } $str
95 do_test wal3-1.$i.4 {
96 execsql { PRAGMA integrity_check } db2
97 } {ok}
98 db2 close
99
100 # Check that the file-system in its current state can be recovered.
101 #
102 forcecopy test.db test2.db
103 forcecopy test.db-wal test2.db-wal
104 forcedelete test2.db-journal
105 sqlite3 db2 test2.db
106 do_test wal3-1.$i.5 {
107 execsql { SELECT count(*) FROM t1 } db2
108 } 4018
109 do_test wal3-1.$i.6 {
110 execsql { SELECT x FROM t1 WHERE rowid = $i }
111 } $str
112 do_test wal3-1.$i.7 {
113 execsql { PRAGMA integrity_check } db2
114 } {ok}
115 db2 close
116 }
117
118 proc byte_is_zero {file offset} {
119 if {[file size test.db] <= $offset} { return 1 }
120 expr { [hexio_read $file $offset 1] == "00" }
121 }
122
123 do_multiclient_test i {
124
125 set testname(1) multiproc
126 set testname(2) singleproc
127 set tn $testname($i)
128
129 do_test wal3-2.$tn.1 {
130 sql1 {
131 PRAGMA page_size = 1024;
132 PRAGMA journal_mode = WAL;
133 }
134 sql1 {
135 CREATE TABLE t1(a, b);
136 INSERT INTO t1 VALUES(1, 'one');
137 BEGIN;
138 SELECT * FROM t1;
139 }
140 } {1 one}
141 do_test wal3-2.$tn.2 {
142 sql2 {
143 CREATE TABLE t2(a, b);
144 INSERT INTO t2 VALUES(2, 'two');
145 BEGIN;
146 SELECT * FROM t2;
147 }
148 } {2 two}
149 do_test wal3-2.$tn.3 {
150 sql3 {
151 CREATE TABLE t3(a, b);
152 INSERT INTO t3 VALUES(3, 'three');
153 BEGIN;
154 SELECT * FROM t3;
155 }
156 } {3 three}
157
158 # Try to checkpoint the database using [db]. It should be possible to
159 # checkpoint everything except the table added by [db3] (checkpointing
160 # these frames would clobber the snapshot currently being used by [db2]).
161 #
162 # After [db2] has committed, a checkpoint can copy the entire log to the
163 # database file. Checkpointing after [db3] has committed is therefore a
164 # no-op, as the entire log has already been backfilled.
165 #
166 do_test wal3-2.$tn.4 {
167 sql1 {
168 COMMIT;
169 PRAGMA wal_checkpoint;
170 }
171 byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024]
172 } {1}
173 do_test wal3-2.$tn.5 {
174 sql2 {
175 COMMIT;
176 PRAGMA wal_checkpoint;
177 }
178 list [byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024]] \
179 [byte_is_zero test.db [expr $AUTOVACUUM ? 5*1024 : 4*1024]]
180 } {0 1}
181 do_test wal3-2.$tn.6 {
182 sql3 {
183 COMMIT;
184 PRAGMA wal_checkpoint;
185 }
186 list [byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024]] \
187 [byte_is_zero test.db [expr $AUTOVACUUM ? 5*1024 : 4*1024]]
188 } {0 1}
189 }
190 catch {db close}
191
192 #-------------------------------------------------------------------------
193 # Test that that for the simple test:
194 #
195 # CREATE TABLE x(y);
196 # INSERT INTO x VALUES('z');
197 # PRAGMA wal_checkpoint;
198 #
199 # in WAL mode the xSync method is invoked as expected for each of
200 # synchronous=off, synchronous=normal and synchronous=full.
201 #
202 foreach {tn syncmode synccount} {
203 1 off
204 {}
205 2 normal
206 {test.db-wal normal test.db normal}
207 3 full
208 {test.db-wal normal test.db-wal normal test.db-wal normal test.db normal}
209 } {
210
211 proc sync_counter {args} {
212 foreach {method filename id flags} $args break
213 lappend ::syncs [file tail $filename] $flags
214 }
215 do_test wal3-3.$tn {
216 forcedelete test.db test.db-wal test.db-journal
217
218 testvfs T
219 T filter {}
220 T script sync_counter
221 sqlite3 db test.db -vfs T
222
223 execsql "PRAGMA synchronous = $syncmode"
224 execsql "PRAGMA checkpoint_fullfsync = 0"
225 execsql { PRAGMA journal_mode = WAL }
226 execsql { CREATE TABLE filler(a,b,c); }
227
228 set ::syncs [list]
229 T filter xSync
230 execsql {
231 CREATE TABLE x(y);
232 INSERT INTO x VALUES('z');
233 PRAGMA wal_checkpoint;
234 }
235 T filter {}
236 set ::syncs
237 } $synccount
238
239 db close
240 T delete
241 }
242
243
244 #-------------------------------------------------------------------------
245 # Only one client may run recovery at a time. Test this mechanism.
246 #
247 # When client-2 tries to open a read transaction while client-1 is
248 # running recovery, it fails to obtain a lock on an aReadMark[] slot
249 # (because they are all locked by recovery). It then tries to obtain
250 # a shared lock on the RECOVER lock to see if there really is a
251 # recovery running or not.
252 #
253 # This block of tests checks the effect of an SQLITE_BUSY or SQLITE_IOERR
254 # being returned when client-2 attempts a shared lock on the RECOVER byte.
255 #
256 # An SQLITE_BUSY should be converted to an SQLITE_BUSY_RECOVERY. An
257 # SQLITE_IOERR should be returned to the caller.
258 #
259 do_test wal3-5.1 {
260 faultsim_delete_and_reopen
261 execsql {
262 PRAGMA journal_mode = WAL;
263 CREATE TABLE t1(a, b);
264 INSERT INTO t1 VALUES(1, 2);
265 INSERT INTO t1 VALUES(3, 4);
266 }
267 faultsim_save_and_close
268 } {}
269
270 testvfs T -default 1
271 T script method_callback
272
273 proc method_callback {method args} {
274 if {$method == "xShmBarrier"} {
275 incr ::barrier_count
276 if {$::barrier_count == 2} {
277 # This code is executed within the xShmBarrier() callback invoked
278 # by the client running recovery as part of writing the recovered
279 # wal-index header. If a second client attempts to access the
280 # database now, it reads a corrupt (partially written) wal-index
281 # header. But it cannot even get that far, as the first client
282 # is still holding all the locks (recovery takes an exclusive lock
283 # on *all* db locks, preventing access by any other client).
284 #
285 # If global variable ::wal3_do_lockfailure is non-zero, then set
286 # things up so that an IO error occurs within an xShmLock() callback
287 # made by the second client (aka [db2]).
288 #
289 sqlite3 db2 test.db
290 if { $::wal3_do_lockfailure } { T filter xShmLock }
291 set ::testrc [ catch { db2 eval "SELECT * FROM t1" } ::testmsg ]
292 T filter {}
293 db2 close
294 }
295 }
296
297 if {$method == "xShmLock"} {
298 foreach {file handle spec} $args break
299 if { $spec == "2 1 lock shared" } {
300 return SQLITE_IOERR
301 }
302 }
303
304 return SQLITE_OK
305 }
306
307 # Test a normal SQLITE_BUSY return.
308 #
309 T filter xShmBarrier
310 set testrc ""
311 set testmsg ""
312 set barrier_count 0
313 set wal3_do_lockfailure 0
314 do_test wal3-5.2 {
315 faultsim_restore_and_reopen
316 execsql { SELECT * FROM t1 }
317 } {1 2 3 4}
318 do_test wal3-5.3 {
319 list $::testrc $::testmsg
320 } {1 {database is locked}}
321 db close
322
323 # Test an SQLITE_IOERR return.
324 #
325 T filter xShmBarrier
326 set barrier_count 0
327 set wal3_do_lockfailure 1
328 set testrc ""
329 set testmsg ""
330 do_test wal3-5.4 {
331 faultsim_restore_and_reopen
332 execsql { SELECT * FROM t1 }
333 } {1 2 3 4}
334 do_test wal3-5.5 {
335 list $::testrc $::testmsg
336 } {1 {disk I/O error}}
337
338 db close
339 T delete
340
341 #-------------------------------------------------------------------------
342 # When opening a read-transaction on a database, if the entire log has
343 # already been copied to the database file, the reader grabs a special
344 # kind of read lock (on aReadMark[0]). This set of test cases tests the
345 # outcome of the following:
346 #
347 # + The reader discovering that between the time when it determined
348 # that the log had been completely backfilled and the lock is obtained
349 # that a writer has written to the log. In this case the reader should
350 # acquire a different read-lock (not aReadMark[0]) and read the new
351 # snapshot.
352 #
353 # + The attempt to obtain the lock on aReadMark[0] fails with SQLITE_BUSY.
354 # This can happen if a checkpoint is ongoing. In this case also simply
355 # obtain a different read-lock.
356 #
357 catch {db close}
358 testvfs T -default 1
359 do_test wal3-6.1.1 {
360 forcedelete test.db test.db-journal test.db wal
361 sqlite3 db test.db
362 execsql { PRAGMA auto_vacuum = off }
363 execsql { PRAGMA journal_mode = WAL }
364 execsql {
365 CREATE TABLE t1(a, b);
366 INSERT INTO t1 VALUES('o', 't');
367 INSERT INTO t1 VALUES('t', 'f');
368 }
369 } {}
370 do_test wal3-6.1.2 {
371 sqlite3 db2 test.db
372 sqlite3 db3 test.db
373 execsql { BEGIN ; SELECT * FROM t1 } db3
374 } {o t t f}
375 do_test wal3-6.1.3 {
376 execsql { PRAGMA wal_checkpoint } db2
377 } {0 4 4}
378
379 # At this point the log file has been fully checkpointed. However,
380 # connection [db3] holds a lock that prevents the log from being wrapped.
381 # Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But
382 # as it is obtaining the lock, [db2] appends to the log file.
383 #
384 T filter xShmLock
385 T script lock_callback
386 proc lock_callback {method file handle spec} {
387 if {$spec == "3 1 lock shared"} {
388 # This is the callback for [db] to obtain the read lock on aReadMark[0].
389 # Disable future callbacks using [T filter {}] and write to the log
390 # file using [db2]. [db3] is preventing [db2] from wrapping the log
391 # here, so this is an append.
392 T filter {}
393 db2 eval { INSERT INTO t1 VALUES('f', 's') }
394 }
395 return SQLITE_OK
396 }
397 do_test wal3-6.1.4 {
398 execsql {
399 BEGIN;
400 SELECT * FROM t1;
401 }
402 } {o t t f f s}
403
404 # [db] should be left holding a read-lock on some slot other than
405 # aReadMark[0]. Test this by demonstrating that the read-lock is preventing
406 # the log from being wrapped.
407 #
408 do_test wal3-6.1.5 {
409 db3 eval COMMIT
410 db2 eval { PRAGMA wal_checkpoint }
411 set sz1 [file size test.db-wal]
412 db2 eval { INSERT INTO t1 VALUES('s', 'e') }
413 set sz2 [file size test.db-wal]
414 expr {$sz2>$sz1}
415 } {1}
416
417 # Test that if [db2] had not interfered when [db] was trying to grab
418 # aReadMark[0], it would have been possible to wrap the log in 3.6.1.5.
419 #
420 do_test wal3-6.1.6 {
421 execsql { COMMIT }
422 execsql { PRAGMA wal_checkpoint } db2
423 execsql {
424 BEGIN;
425 SELECT * FROM t1;
426 }
427 } {o t t f f s s e}
428 do_test wal3-6.1.7 {
429 db2 eval { PRAGMA wal_checkpoint }
430 set sz1 [file size test.db-wal]
431 db2 eval { INSERT INTO t1 VALUES('n', 't') }
432 set sz2 [file size test.db-wal]
433 expr {$sz2==$sz1}
434 } {1}
435
436 db3 close
437 db2 close
438 db close
439
440 do_test wal3-6.2.1 {
441 forcedelete test.db test.db-journal test.db wal
442 sqlite3 db test.db
443 sqlite3 db2 test.db
444 execsql { PRAGMA auto_vacuum = off }
445 execsql { PRAGMA journal_mode = WAL }
446 execsql {
447 CREATE TABLE t1(a, b);
448 INSERT INTO t1 VALUES('h', 'h');
449 INSERT INTO t1 VALUES('l', 'b');
450 }
451 } {}
452
453 T filter xShmLock
454 T script lock_callback
455 proc lock_callback {method file handle spec} {
456 if {$spec == "3 1 unlock exclusive"} {
457 T filter {}
458 set ::R [db2 eval {
459 BEGIN;
460 SELECT * FROM t1;
461 }]
462 }
463 }
464 do_test wal3-6.2.2 {
465 execsql { PRAGMA wal_checkpoint }
466 } {0 4 4}
467 do_test wal3-6.2.3 {
468 set ::R
469 } {h h l b}
470 do_test wal3-6.2.4 {
471 set sz1 [file size test.db-wal]
472 execsql { INSERT INTO t1 VALUES('b', 'c'); }
473 set sz2 [file size test.db-wal]
474 expr {$sz2 > $sz1}
475 } {1}
476 do_test wal3-6.2.5 {
477 db2 eval { COMMIT }
478 execsql { PRAGMA wal_checkpoint }
479 set sz1 [file size test.db-wal]
480 execsql { INSERT INTO t1 VALUES('n', 'o'); }
481 set sz2 [file size test.db-wal]
482 expr {$sz2 == $sz1}
483 } {1}
484
485 db2 close
486 db close
487 T delete
488
489 #-------------------------------------------------------------------------
490 # When opening a read-transaction on a database, if the entire log has
491 # not yet been copied to the database file, the reader grabs a read
492 # lock on aReadMark[x], where x>0. The following test cases experiment
493 # with the outcome of the following:
494 #
495 # + The reader discovering that between the time when it read the
496 # wal-index header and the lock was obtained that a writer has
497 # written to the log. In this case the reader should re-read the
498 # wal-index header and lock a snapshot corresponding to the new
499 # header.
500 #
501 # + The value in the aReadMark[x] slot has been modified since it was
502 # read.
503 #
504 catch {db close}
505 testvfs T -default 1
506 do_test wal3-7.1.1 {
507 forcedelete test.db test.db-journal test.db wal
508 sqlite3 db test.db
509 execsql {
510 PRAGMA journal_mode = WAL;
511 CREATE TABLE blue(red PRIMARY KEY, green);
512 }
513 } {wal}
514
515 T script method_callback
516 T filter xOpen
517 proc method_callback {method args} {
518 if {$method == "xOpen"} { return "reader" }
519 }
520 do_test wal3-7.1.2 {
521 sqlite3 db2 test.db
522 execsql { SELECT * FROM blue } db2
523 } {}
524
525 T filter xShmLock
526 set ::locks [list]
527 proc method_callback {method file handle spec} {
528 if {$handle != "reader" } { return }
529 if {$method == "xShmLock"} {
530 catch { execsql { INSERT INTO blue VALUES(1, 2) } }
531 catch { execsql { INSERT INTO blue VALUES(3, 4) } }
532 }
533 lappend ::locks $spec
534 }
535 do_test wal3-7.1.3 {
536 execsql { SELECT * FROM blue } db2
537 } {1 2 3 4}
538 do_test wal3-7.1.4 {
539 set ::locks
540 } {{4 1 lock shared} {4 1 unlock shared} {5 1 lock shared} {5 1 unlock shared}}
541
542 set ::locks [list]
543 proc method_callback {method file handle spec} {
544 if {$handle != "reader" } { return }
545 if {$method == "xShmLock"} {
546 catch { execsql { INSERT INTO blue VALUES(5, 6) } }
547 }
548 lappend ::locks $spec
549 }
550 do_test wal3-7.2.1 {
551 execsql { SELECT * FROM blue } db2
552 } {1 2 3 4 5 6}
553 do_test wal3-7.2.2 {
554 set ::locks
555 } {{5 1 lock shared} {5 1 unlock shared} {4 1 lock shared} {4 1 unlock shared}}
556
557 db close
558 db2 close
559 T delete
560
561
562 #-------------------------------------------------------------------------
563 # When a connection opens a read-lock on the database, it searches for
564 # an aReadMark[] slot that is already set to the mxFrame value for the
565 # new transaction. If it cannot find one, it attempts to obtain an
566 # exclusive lock on an aReadMark[] slot for the purposes of modifying
567 # the value, then drops back to a shared-lock for the duration of the
568 # transaction.
569 #
570 # This test case verifies that if an exclusive lock cannot be obtained
571 # on any aReadMark[] slot (because there are already several readers),
572 # the client takes a shared-lock on a slot without modifying the value
573 # and continues.
574 #
575 set nConn 50
576 if { [string match *BSD $tcl_platform(os)] } { set nConn 25 }
577 do_test wal3-9.0 {
578 forcedelete test.db test.db-journal test.db wal
579 sqlite3 db test.db
580 execsql {
581 PRAGMA page_size = 1024;
582 PRAGMA journal_mode = WAL;
583 CREATE TABLE whoami(x);
584 INSERT INTO whoami VALUES('nobody');
585 }
586 } {wal}
587 for {set i 0} {$i < $nConn} {incr i} {
588 set c db$i
589 do_test wal3-9.1.$i {
590 sqlite3 $c test.db
591 execsql { UPDATE whoami SET x = $c }
592 execsql {
593 BEGIN;
594 SELECT * FROM whoami
595 } $c
596 } $c
597 }
598 for {set i 0} {$i < $nConn} {incr i} {
599 set c db$i
600 do_test wal3-9.2.$i {
601 execsql { SELECT * FROM whoami } $c
602 } $c
603 }
604
605 set sz [expr 1024 * (2+$AUTOVACUUM)]
606 do_test wal3-9.3 {
607 for {set i 0} {$i < ($nConn-1)} {incr i} { db$i close }
608 execsql { PRAGMA wal_checkpoint }
609 byte_is_zero test.db [expr $sz-1024]
610 } {1}
611 do_test wal3-9.4 {
612 db[expr $nConn-1] close
613 execsql { PRAGMA wal_checkpoint }
614 set sz2 [file size test.db]
615 byte_is_zero test.db [expr $sz-1024]
616 } {0}
617
618 do_multiclient_test tn {
619 do_test wal3-10.$tn.1 {
620 sql1 {
621 PRAGMA page_size = 1024;
622 CREATE TABLE t1(x);
623 PRAGMA journal_mode = WAL;
624 PRAGMA wal_autocheckpoint = 100000;
625 BEGIN;
626 INSERT INTO t1 VALUES(randomblob(800));
627 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2
628 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4
629 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8
630 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 16
631 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 32
632 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 64
633 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 128
634 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 256
635 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 512
636 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 1024
637 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2048
638 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4096
639 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8192
640 COMMIT;
641 CREATE INDEX i1 ON t1(x);
642 }
643
644 expr {[file size test.db-wal] > [expr 1032*9000]}
645 } 1
646
647 do_test wal3-10.$tn.2 {
648 sql2 {PRAGMA integrity_check}
649 } {ok}
650 }
651
652 finish_test