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