File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / wal3.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

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

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>