/ Check-in [2d6f2485]
Login

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

Overview
Comment:Change WAL test cases to account for the improved concurrency in the new checkpoint logic.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | wal-incr-ckpt
Files: files | file ages | folders
SHA1: 2d6f2485053e6d2a67abda9a80693ca68f4556d2
User & Date: drh 2010-05-31 13:11:49
Context
2010-05-31
14:18
Update another test case in wal.test. check-in: 99fde69e user: dan tags: wal-incr-ckpt
13:11
Change WAL test cases to account for the improved concurrency in the new checkpoint logic. check-in: 2d6f2485 user: drh tags: wal-incr-ckpt
12:30
Do not fail a checkpoint just because active readers prevent backfill. check-in: 9aa4243e user: drh tags: wal-incr-ckpt
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/wal.test.

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
...
689
690
691
692
693
694
695
696
697
698
699
700

701
702
703
704
705
706
707

  # Writers do not block checkpoints any more either.
  #
  do_test wal-10.$tn.14 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {}}

  # Similar to the test above. Except this time, a new read transaction is
  # started (db3) while the checkpointer is waiting for an old one (db2) to 
  # finish. The checkpointer can finish, but any subsequent write operations 
  # must wait until after db3 has closed the read transaction, as db3 is a
  # "region D" writer.
  #
  do_test wal-10.$tn.15 {
    sql2 { COMMIT; BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.16 {
    catchsql { PRAGMA wal_checkpoint } 
  } {1 {database is locked}}
  proc busyhandler x {
    if {$x==3} { sql3 { BEGIN; SELECT * FROM t1 } }
    if {$x==4} { sql2 COMMIT }
    if {$x<5}  { return 0 }
    return 1
  }
  db busy busyhandler
  do_test wal-10.$tn.17 {
    execsql { PRAGMA wal_checkpoint } 
  } {}
  do_test wal-10.$tn.18 {
    sql3 { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.19 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {1 {database is locked}}
  do_test wal-10.$tn.20 {
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.21 {
    sql3 COMMIT

  } {}
  do_test wal-10.$tn.22 {
    execsql { INSERT INTO t1 VALUES(13, 14) }
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # Set [db3] up as a "region D" reader again. Then upgrade it to a writer
  # and back down to a reader. Then, check that a checkpoint is not possible
  # (as [db3] still has a snapshot locked).
  #
  do_test wal-10.$tn.23 {
    execsql { PRAGMA wal_checkpoint }
  } {}
  do_test wal-10.$tn.24 {
    sql2 { BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
  do_test wal-10.$tn.25 {
    execsql { PRAGMA wal_checkpoint }
  } {}
  do_test wal-10.$tn.26 {
    catchsql { INSERT INTO t1 VALUES(15, 16) }
  } {1 {database is locked}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(15, 16) }
  } {}
  do_test wal-10.$tn.28 {
    code3 {
      set ::STMT [sqlite3_prepare db3 "SELECT * FROM t1" -1 TAIL]
      sqlite3_step $::STMT
    }
    sql3 COMMIT
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
  db busy {}
  do_test wal-10.$tn.29 {
    execsql { INSERT INTO t1 VALUES(17, 18) }
    catchsql { PRAGMA wal_checkpoint }
  } {1 {database is locked}}

  do_test wal-10.$tn.30 {
    code3 { sqlite3_finalize $::STMT }
    execsql { PRAGMA wal_checkpoint }
  } {}

  # At one point, if a reader failed to upgrade to a writer because it
  # was reading an old snapshot, the write-locks were not being released.
  # Test that this bug has been fixed.
  #
  do_test wal-10.$tn.31 {

    execsql { BEGIN ; SELECT * FROM t1 }
    sql2 { INSERT INTO t1 VALUES(19, 20) }
    catchsql { INSERT INTO t1 VALUES(21, 22) }
  } {1 {database is locked}}
  do_test wal-10.$tn.32 {
    # This statement would fail when the bug was present.
    sql2 { INSERT INTO t1 VALUES(21, 22) }
  } {}
  do_test wal-10.$tn.33 {
    execsql { SELECT * FROM t1 ; COMMIT }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}
  do_test wal-10.$tn.34 {
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22}

  # Test that if a checkpointer cannot obtain the required locks, it
  # releases all locks before returning a busy error.
  #
  do_test wal-10.$tn.35 {
    execsql { 
      DELETE FROM t1;
................................................................................
      INSERT INTO t1 VALUES('c', 'd');
    }
    sql2 {
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}
  proc busyhandler x { return 1 }
  db busy busyhandler
  do_test wal-10.$tn.36 {
    catchsql { PRAGMA wal_checkpoint }
  } {1 {database is locked}}

  do_test wal-10.$tn.36 {
    sql3 { INSERT INTO t1 VALUES('e', 'f') }
    sql2 { SELECT * FROM t1 }
  } {a b c d}
  do_test wal-10.$tn.37 {
    sql2 COMMIT
    execsql { PRAGMA wal_checkpoint }







|
|
<
<
<






|
<
<
<
<
<
<
<




|



|


|


>


<



|
|
<












|

|






<

|
<

|

<
>










>

|
|



|



|


|







 







<
<


<
>







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
...
677
678
679
680
681
682
683


684
685

686
687
688
689
690
691
692
693

  # Writers do not block checkpoints any more either.
  #
  do_test wal-10.$tn.14 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {}}

  # The following series of test cases used to verify another blocking
  # case in WAL - a case which no longer blocks.



  #
  do_test wal-10.$tn.15 {
    sql2 { COMMIT; BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.16 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {}}







  do_test wal-10.$tn.17 {
    execsql { PRAGMA wal_checkpoint } 
  } {}
  do_test wal-10.$tn.18 {
    sql3 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.19 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {0 {}}
  do_test wal-10.$tn.20 {
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
  do_test wal-10.$tn.21 {
    sql3 COMMIT
    sql2 COMMIT
  } {}
  do_test wal-10.$tn.22 {

    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # Another series of tests that used to demonstrate blocking behavior
  # but which now work.

  #
  do_test wal-10.$tn.23 {
    execsql { PRAGMA wal_checkpoint }
  } {}
  do_test wal-10.$tn.24 {
    sql2 { BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
  do_test wal-10.$tn.25 {
    execsql { PRAGMA wal_checkpoint }
  } {}
  do_test wal-10.$tn.26 {
    catchsql { INSERT INTO t1 VALUES(15, 16) }
  } {0 {}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(17, 18) }
  } {}
  do_test wal-10.$tn.28 {
    code3 {
      set ::STMT [sqlite3_prepare db3 "SELECT * FROM t1" -1 TAIL]
      sqlite3_step $::STMT
    }

    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}

  do_test wal-10.$tn.29 {
    execsql { INSERT INTO t1 VALUES(19, 20) }
    catchsql { PRAGMA wal_checkpoint }

  } {0 {}}
  do_test wal-10.$tn.30 {
    code3 { sqlite3_finalize $::STMT }
    execsql { PRAGMA wal_checkpoint }
  } {}

  # At one point, if a reader failed to upgrade to a writer because it
  # was reading an old snapshot, the write-locks were not being released.
  # Test that this bug has been fixed.
  #
  do_test wal-10.$tn.31 {
    sql2 COMMIT
    execsql { BEGIN ; SELECT * FROM t1 }
    sql2 { INSERT INTO t1 VALUES(21, 22) }
    catchsql { INSERT INTO t1 VALUES(23, 24) }
  } {1 {database is locked}}
  do_test wal-10.$tn.32 {
    # This statement would fail when the bug was present.
    sql2 { INSERT INTO t1 VALUES(23, 24) }
  } {}
  do_test wal-10.$tn.33 {
    execsql { SELECT * FROM t1 ; COMMIT }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
  do_test wal-10.$tn.34 {
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24}

  # Test that if a checkpointer cannot obtain the required locks, it
  # releases all locks before returning a busy error.
  #
  do_test wal-10.$tn.35 {
    execsql { 
      DELETE FROM t1;
................................................................................
      INSERT INTO t1 VALUES('c', 'd');
    }
    sql2 {
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}


  do_test wal-10.$tn.36 {
    catchsql { PRAGMA wal_checkpoint }

  } {0 {}}
  do_test wal-10.$tn.36 {
    sql3 { INSERT INTO t1 VALUES('e', 'f') }
    sql2 { SELECT * FROM t1 }
  } {a b c d}
  do_test wal-10.$tn.37 {
    sql2 COMMIT
    execsql { PRAGMA wal_checkpoint }