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