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

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

Overview
Comment:Fixes to the test cases in wal2.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | wal-incr-ckpt
Files: files | file ages | folders
SHA1: cd5fbcbce8b55f24c0bf349b179c26e333ff7172
User & Date: dan 2010-06-01 07:51:48
Context
2010-06-01
10:44
If the checkpoint fails to obtain an exclusive lock on one of the read-lock bytes, do not consider this an error. check-in: 9e95e357 user: dan tags: wal-incr-ckpt
07:51
Fixes to the test cases in wal2.test. check-in: cd5fbcbc user: dan tags: wal-incr-ckpt
01:08
Comment edits and cleanup in wal.c. No functional code changes. check-in: e8e666ab user: drh tags: wal-incr-ckpt
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/wal2.test.

367
368
369
370
371
372
373









374
375
376
377
378
379
380
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
...
553
554
555
556
557
558
559





560
561
562
563
564
565
566
567
568

569
570






















571

572
573
574





575
576
577
578
579
580
581




582
583
584
585




586
587
588













589
590


591




592
593
594

595
596
597
598







599


600












601
602
603





604
605

606
607


608
609
610
611
612
613
614
# Test that if a database connection is forced to run recovery before it
# can perform a checkpoint, it does not transition into RECOVER state.
#
# UPDATE: This has now changed. When running a checkpoint, if recovery is
# required the client grabs all exclusive locks (just as it would for a
# recovery performed as a pre-cursor to a normal database transaction).
#









do_test wal2-5.1 {
  proc tvfs_cb {method args} {
    set ::shm_file [lindex $args 0]
    if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
    return $::tvfs_cb_return
  }
  set tvfs_cb_return SQLITE_OK
................................................................................
    INSERT INTO x VALUES(1);
  }

  incr_tvfs_hdr $::shm_file 1 1
  set ::locks [list]
  execsql { PRAGMA wal_checkpoint }
  set ::locks
} {CHECKPOINT UNLOCK}
db close
tvfs delete

#-------------------------------------------------------------------------
# This block, test cases wal2-6.*, tests the operation of WAL with
# "PRAGMA locking_mode=EXCLUSIVE" set.
#
................................................................................
  list [file exists test.db-wal] [file exists test.db-journal]
} {0 1}
do_test wal2-6.3.7 {
  execsql { PRAGMA lock_status }
} {main exclusive temp closed}
db close






do_test wal2-6.4.1 {
  file delete -force test.db test.db-wal test.db-journal
  proc tvfs_cb {method args} {
    set ::shm_file [lindex $args 0]
    if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
    return "SQLITE_OK"
  }
  testvfs tvfs tvfs_cb
  sqlite3 db test.db -vfs tvfs


  execsql {






















    PRAGMA journal_mode = WAL;

    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES('Leonard');
    INSERT INTO t1 VALUES('Arthur');





  }

  set ::locks [list]
  execsql { PRAGMA locking_mode = exclusive }
  set ::locks
} {}
do_test wal2-6.4.2 {




  execsql { SELECT * FROM t1 }
} {Leonard Arthur}
do_test wal2-6.4.3 {
  set ::locks




} {READ}
do_test wal2-6.4.4 {
  execsql { 













    INSERT INTO t1 VALUES('Julius Henry');
    SELECT * FROM t1;


  }




} {Leonard Arthur {Julius Henry}}
do_test wal2-6.4.5 {
  set ::locks

} {READ}
do_test wal2-6.4.6 {
  execsql {
    PRAGMA locking_mode = NORMAL;







    DELETE FROM t1;


  }












  set ::locks
} {READ UNLOCK}
do_test wal2-6.4.7 {





  set ::locks [list]
  execsql { INSERT INTO t1 VALUES('Karl') }

  set ::locks
} {READ WRITE READ UNLOCK}


db close
tvfs delete

do_test wal2-6.5.1 {
  sqlite3 db test.db
  execsql {
    PRAGMA journal_mode = wal;







>
>
>
>
>
>
>
>
>







 







|







 







>
>
>
>
>









>

<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
|
|
|
>
>
>
>
>


<
<
<
|
<
>
>
>
>
|
|
<
<
>
>
>
>
|
<
<
>
>
>
>
>
>
>
>
>
>
>
>
>


>
>

>
>
>
>
|
<
<
>
|
<
<
|
>
>
>
>
>
>
>
|
>
>

>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
>
>
>
>
>

<
>
|
<
>
>







367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
...
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
...
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584

585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618



619

620
621
622
623
624
625


626
627
628
629
630


631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653


654
655


656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680


681
682
683
684
685
686

687
688

689
690
691
692
693
694
695
696
697
# Test that if a database connection is forced to run recovery before it
# can perform a checkpoint, it does not transition into RECOVER state.
#
# UPDATE: This has now changed. When running a checkpoint, if recovery is
# required the client grabs all exclusive locks (just as it would for a
# recovery performed as a pre-cursor to a normal database transaction).
#
set expected_locks [list]
lappend expected_locks {1 1 lock exclusive}   ;# Lock checkpoint
lappend expected_locks {0 1 lock exclusive}   ;# Lock writer
lappend expected_locks {2 6 lock exclusive}   ;# Lock recovery & all aReadMark[]
lappend expected_locks {2 6 unlock exclusive} ;# Unlock recovery & aReadMark[]
lappend expected_locks {0 1 unlock exclusive} ;# Unlock writer
lappend expected_locks {3 1 lock exclusive}   ;# Lock aReadMark[0]
lappend expected_locks {3 1 unlock exclusive} ;# Unlock aReadMark[0]
lappend expected_locks {1 1 unlock exclusive} ;# Unlock checkpoint
do_test wal2-5.1 {
  proc tvfs_cb {method args} {
    set ::shm_file [lindex $args 0]
    if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
    return $::tvfs_cb_return
  }
  set tvfs_cb_return SQLITE_OK
................................................................................
    INSERT INTO x VALUES(1);
  }

  incr_tvfs_hdr $::shm_file 1 1
  set ::locks [list]
  execsql { PRAGMA wal_checkpoint }
  set ::locks
} $expected_locks
db close
tvfs delete

#-------------------------------------------------------------------------
# This block, test cases wal2-6.*, tests the operation of WAL with
# "PRAGMA locking_mode=EXCLUSIVE" set.
#
................................................................................
  list [file exists test.db-wal] [file exists test.db-journal]
} {0 1}
do_test wal2-6.3.7 {
  execsql { PRAGMA lock_status }
} {main exclusive temp closed}
db close


# This test - wal2-6.4.* - uses a single database connection and the
# [testvfs] instrumentation to test that xShmLock() is being called
# as expected when a WAL database is used with locking_mode=exclusive.
#
do_test wal2-6.4.1 {
  file delete -force test.db test.db-wal test.db-journal
  proc tvfs_cb {method args} {
    set ::shm_file [lindex $args 0]
    if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
    return "SQLITE_OK"
  }
  testvfs tvfs tvfs_cb
  sqlite3 db test.db -vfs tvfs
} {}


set RECOVERY {
  {0 1 lock exclusive} {1 7 lock exclusive} 
  {1 7 unlock exclusive} {0 1 unlock exclusive}
}
set READMARK0_READ {
  {3 1 lock shared} {3 1 unlock shared}
}
set READMARK0_WRITE {
  {3 1 lock shared} 
  {0 1 lock exclusive} {3 1 unlock shared} 
  {4 1 lock exclusive} {4 1 unlock exclusive} {4 1 lock shared} 
  {0 1 unlock exclusive} {4 1 unlock shared}
}
set READMARK1_SET {
  {4 1 lock exclusive} {4 1 unlock exclusive}
}
set READMARK1_READ {
  {4 1 lock shared} {4 1 unlock shared}
}

foreach {tn sql res expected_locks} {
  2 {
    PRAGMA journal_mode = WAL;
    BEGIN;
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES('Leonard');
      INSERT INTO t1 VALUES('Arthur');
    COMMIT;
  } {wal} {
    $RECOVERY 
    $READMARK0_READ 
    $READMARK0_WRITE
  }




  3 {

    # This test should do the READMARK1_SET locking to populate the 
    # aReadMark[1] slot with the current mxFrame value. Followed by
    # READMARK1_READ to read the database.
    #
    SELECT * FROM t1
  } {Leonard Arthur} {


    $READMARK1_SET
    $READMARK1_READ
  }

  4 {


    # aReadMark[1] is already set to mxFrame. So just READMARK1_READ
    # this time, not READMARK1_SET.
    #
    SELECT * FROM t1 ORDER BY x
  } {Arthur Leonard} { 
    $READMARK1_READ 
  }

  5 {
    PRAGMA locking_mode = exclusive
  } {exclusive} { } 

  6 {
    INSERT INTO t1 VALUES('Julius Henry');
    SELECT * FROM t1;
  } {Leonard Arthur {Julius Henry}} {
    $READMARK1_READ
  }

  7 {
    INSERT INTO t1 VALUES('Karl');
    SELECT * FROM t1;
  } {Leonard Arthur {Julius Henry} Karl} { }



  8 {


    PRAGMA locking_mode = normal
  } {normal} { }

  9 {
    SELECT * FROM t1 ORDER BY x
  } {Arthur {Julius Henry} Karl Leonard} { }

  10 {
    DELETE FROM t1
  } {} {
    $READMARK1_READ
  }

  11 {
    SELECT * FROM t1
  } {} {
    $READMARK1_SET
    $READMARK1_READ
  }
} {

  set L [list]
  foreach el [subst $expected_locks] { lappend L $el }

  set S ""


  foreach sq [split $sql "\n"] { 
    set sq [string trim $sq]
    if {[string match {#*} $sq]==0} {append S "$sq\n"}
  }

  set ::locks [list]

  do_test wal2-6.4.$tn.1 { execsql $S } $res
  do_test wal2-6.4.$tn.2 { set ::locks  } $L

}

db close
tvfs delete

do_test wal2-6.5.1 {
  sqlite3 db test.db
  execsql {
    PRAGMA journal_mode = wal;

Changes to test/walcrash2.test.

87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

108
109
110
111
112
113
          INSERT INTO t1 SELECT * FROM t1 LIMIT 3;        -- 20 rows, 20 pages
      }
    } 
    close $C
    file size test.db-wal
  } [wal_file_size 16 1024]
}
sqlite3 db2 test.db
breakpoint
      db2 eval {
        PRAGMA cache_size = 15;
        BEGIN;
          INSERT INTO t1 VALUES(randomblob(900));         --  1 row,  1  page
          INSERT INTO t1 SELECT * FROM t1;                --  2 rows, 3  pages
          INSERT INTO t1 SELECT * FROM t1;                --  4 rows, 5  pages
          INSERT INTO t1 SELECT * FROM t1;                --  8 rows, 9  pages
          INSERT INTO t1 SELECT * FROM t1;                -- 16 rows, 17 pages
          INSERT INTO t1 SELECT * FROM t1 LIMIT 3;        -- 20 rows, 20 pages
      }

do_test walcrash2-1.3 {

  execsql { SELECT count(*) FROM t1 } db2
} {0}
catch { db2 close }

finish_test








<
<
<
<
<
<
<
<
<
<
<
<
<

>






87
88
89
90
91
92
93













94
95
96
97
98
99
100
101
          INSERT INTO t1 SELECT * FROM t1 LIMIT 3;        -- 20 rows, 20 pages
      }
    } 
    close $C
    file size test.db-wal
  } [wal_file_size 16 1024]
}













do_test walcrash2-1.3 {
  sqlite3 db2 test.db
  execsql { SELECT count(*) FROM t1 } db2
} {0}
catch { db2 close }

finish_test