/ Check-in [3ed18906]
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:Avoid creating a master journal unless two or more databases in the transaction can actually benefit from that master journal.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3ed1890612bd45bd9c72f670d2cbb0b8fbd35d92
User & Date: drh 2016-02-22 14:57:38
Context
2016-02-22
16:04
Always use the sqlite3VdbeDeleteAuxdata() routine for clearing auxdata on function parameter, rather than having a separate deleteAuxdataFromFrame() for doing the job for trigger frames. check-in: 64386fa3 user: drh tags: trunk
14:57
Avoid creating a master journal unless two or more databases in the transaction can actually benefit from that master journal. check-in: 3ed18906 user: drh tags: trunk
13:23
Change magic numbers associated with synchronous settings to named constants. check-in: 9230ba6c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbeaux.c.

2151
2152
2153
2154
2155
2156
2157
2158


2159
2160
2161
2162
2163
2164
2165
....
2179
2180
2181
2182
2183
2184
2185












2186
2187
2188







2189
2190
2191
2192
2193
2194
2195
2196
** A read or write transaction may or may not be active on database handle
** db. If a transaction is active, commit it. If there is a
** write-transaction spanning more than one database file, this routine
** takes care of the master journal trickery.
*/
static int vdbeCommit(sqlite3 *db, Vdbe *p){
  int i;
  int nTrans = 0;  /* Number of databases with an active write-transaction */


  int rc = SQLITE_OK;
  int needXcommit = 0;

#ifdef SQLITE_OMIT_VIRTUALTABLE
  /* With this option, sqlite3VtabSync() is defined to be simply 
  ** SQLITE_OK so p is not used. 
  */
................................................................................
  ** including the temp database. (b) is important because if more than 
  ** one database file has an open write transaction, a master journal
  ** file is required for an atomic commit.
  */ 
  for(i=0; rc==SQLITE_OK && i<db->nDb; i++){ 
    Btree *pBt = db->aDb[i].pBt;
    if( sqlite3BtreeIsInTrans(pBt) ){












      needXcommit = 1;
      if( i!=1 ) nTrans++;
      sqlite3BtreeEnter(pBt);







      rc = sqlite3PagerExclusiveLock(sqlite3BtreePager(pBt));
      sqlite3BtreeLeave(pBt);
    }
  }
  if( rc!=SQLITE_OK ){
    return rc;
  }








|
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>

<

>
>
>
>
>
>
>
|







2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
....
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200

2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
** A read or write transaction may or may not be active on database handle
** db. If a transaction is active, commit it. If there is a
** write-transaction spanning more than one database file, this routine
** takes care of the master journal trickery.
*/
static int vdbeCommit(sqlite3 *db, Vdbe *p){
  int i;
  int nTrans = 0;  /* Number of databases with an active write-transaction
                   ** that are candidates for a two-phase commit using a
                   ** master-journal */
  int rc = SQLITE_OK;
  int needXcommit = 0;

#ifdef SQLITE_OMIT_VIRTUALTABLE
  /* With this option, sqlite3VtabSync() is defined to be simply 
  ** SQLITE_OK so p is not used. 
  */
................................................................................
  ** including the temp database. (b) is important because if more than 
  ** one database file has an open write transaction, a master journal
  ** file is required for an atomic commit.
  */ 
  for(i=0; rc==SQLITE_OK && i<db->nDb; i++){ 
    Btree *pBt = db->aDb[i].pBt;
    if( sqlite3BtreeIsInTrans(pBt) ){
      /* Whether or not a database might need a master journal depends upon
      ** its journal mode (among other things).  This matrix determines which
      ** journal modes use a master journal and which do not */
      static const u8 aMJNeeded[] = {
        /* DELETE   */  1,
        /* PERSIST   */ 1,
        /* OFF       */ 0,
        /* TRUNCATE  */ 1,
        /* MEMORY    */ 0,
        /* WAL       */ 0
      };
      Pager *pPager;   /* Pager associated with pBt */
      needXcommit = 1;

      sqlite3BtreeEnter(pBt);
      pPager = sqlite3BtreePager(pBt);
      if( db->aDb[i].safety_level!=PAGER_SYNCHRONOUS_OFF
       && aMJNeeded[sqlite3PagerGetJournalMode(pPager)]
      ){ 
        assert( i!=1 );
        nTrans++;
      }
      rc = sqlite3PagerExclusiveLock(pPager);
      sqlite3BtreeLeave(pBt);
    }
  }
  if( rc!=SQLITE_OK ){
    return rc;
  }

Changes to test/pager1.test.

525
526
527
528
529
530
531

532
533
534
535
536
537
538
539
540
541
542
543
544

545
546
547
548
549
550
551
...
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
...
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
# file-system is saved just before the xDelete() call to remove the 
# master journal file from the file-system.
#
set pwd [get_pwd]
testvfs tv -default 1
tv script copy_on_mj_delete
set ::mj_filename_length 0

proc copy_on_mj_delete {method filename args} {
  if {[string match *mj* [file tail $filename]]} { 
    #
    # NOTE: Is the file name relative?  If so, add the length of the current
    #       directory.
    #
    if {[is_relative_file $filename]} {
      set ::mj_filename_length \
        [expr {[string length $filename] + [string length $::pwd]}]
    } else {
      set ::mj_filename_length [string length $filename]
    }
    faultsim_save 

  }
  return SQLITE_OK
}

foreach {tn1 tcl} {
  1 { set prefix "test.db" }
  2 { 
................................................................................
    }

    set padding [string repeat x [expr $nPadding %32]]
    set prefix "test.db${padding}"
  }
} {
  eval $tcl
  foreach {tn2 sql} {
    o { 
      PRAGMA main.synchronous=OFF;
      PRAGMA aux.synchronous=OFF;
      PRAGMA journal_mode = DELETE;
    }
    o512 { 
      PRAGMA main.synchronous=OFF;
      PRAGMA aux.synchronous=OFF;
      PRAGMA main.page_size = 512;
      PRAGMA aux.page_size = 512;
      PRAGMA journal_mode = DELETE;
    }
    n { 
      PRAGMA main.synchronous=NORMAL;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA journal_mode = DELETE;
    }
    f { 
      PRAGMA main.synchronous=FULL;
      PRAGMA aux.synchronous=FULL;
      PRAGMA journal_mode = DELETE;
    }







































  } {

    set tn "${tn1}.${tn2}"
  
    # Set up a connection to have two databases, test.db (main) and 
    # test.db2 (aux). Then run a multi-file transaction on them. The
    # VFS will snapshot the file-system just before the master-journal
    # file is deleted to commit the transaction.
    #
    tv filter xDelete
    do_test pager1-4.4.$tn.1 {

      faultsim_delete_and_reopen $prefix
      execsql "
        ATTACH '${prefix}2' AS aux;
        $sql
        CREATE TABLE a(x);
        CREATE TABLE aux.b(x);
        INSERT INTO a VALUES('double-you');
................................................................................
        BEGIN;
          INSERT INTO a SELECT * FROM b WHERE rowid<=3;
          INSERT INTO b SELECT * FROM a WHERE rowid<=3;
        COMMIT;
      }
    } {}
    tv filter {}







    
    # Check that the transaction was committed successfully.
    #
    do_execsql_test pager1-4.4.$tn.2 {
      SELECT * FROM a
    } {double-you why zed won too free}
    do_execsql_test pager1-4.4.$tn.3 {
      SELECT * FROM b
    } {won too free double-you why zed}
    

    # Restore the file-system and reopen the databases. Check that it now
    # appears that the transaction was not committed (because the file-system
    # was restored to the state where it had not been).
    #
    do_test pager1-4.4.$tn.4 {
      faultsim_restore_and_reopen $prefix
      execsql "ATTACH '${prefix}2' AS aux"
    } {}
    do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
    do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}

    
    # Restore the file-system again. This time, before reopening the databases,
    # delete the master-journal file from the file-system. It now appears that
    # the transaction was committed (no master-journal file == no rollback).
    #
    do_test pager1-4.4.$tn.7 {

      faultsim_restore_and_reopen $prefix
      foreach f [glob ${prefix}-mj*] { forcedelete $f }




      execsql "ATTACH '${prefix}2' AS aux"

    } {}
    do_execsql_test pager1-4.4.$tn.8 {
      SELECT * FROM a
    } {double-you why zed won too free}
    do_execsql_test pager1-4.4.$tn.9 {
      SELECT * FROM b
    } {won too free double-you why zed}
................................................................................
  }

  cd $pwd
}
db close
tv delete
forcedelete $dirname


# Set up a VFS to make a copy of the file-system just before deleting a
# journal file to commit a transaction. The transaction modifies exactly
# two database pages (and page 1 - the change counter).
#
testvfs tv -default 1
tv sectorsize 512







>













>







 







|




|






|




|




|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>











>







 







>
>
>
>
>
>
>










>
|
|
|
|
|
|
|
|
|
|
>






>
|
|
>
>
>
>

>







 







<







525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
...
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
...
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
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
...
730
731
732
733
734
735
736

737
738
739
740
741
742
743
# file-system is saved just before the xDelete() call to remove the 
# master journal file from the file-system.
#
set pwd [get_pwd]
testvfs tv -default 1
tv script copy_on_mj_delete
set ::mj_filename_length 0
set ::mj_delete_cnt 0
proc copy_on_mj_delete {method filename args} {
  if {[string match *mj* [file tail $filename]]} { 
    #
    # NOTE: Is the file name relative?  If so, add the length of the current
    #       directory.
    #
    if {[is_relative_file $filename]} {
      set ::mj_filename_length \
        [expr {[string length $filename] + [string length $::pwd]}]
    } else {
      set ::mj_filename_length [string length $filename]
    }
    faultsim_save 
    incr ::mj_delete_cnt
  }
  return SQLITE_OK
}

foreach {tn1 tcl} {
  1 { set prefix "test.db" }
  2 { 
................................................................................
    }

    set padding [string repeat x [expr $nPadding %32]]
    set prefix "test.db${padding}"
  }
} {
  eval $tcl
  foreach {tn2 sql usesMJ} {
    o { 
      PRAGMA main.synchronous=OFF;
      PRAGMA aux.synchronous=OFF;
      PRAGMA journal_mode = DELETE;
    } 0
    o512 { 
      PRAGMA main.synchronous=OFF;
      PRAGMA aux.synchronous=OFF;
      PRAGMA main.page_size = 512;
      PRAGMA aux.page_size = 512;
      PRAGMA journal_mode = DELETE;
    } 0
    n { 
      PRAGMA main.synchronous=NORMAL;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA journal_mode = DELETE;
    } 1
    f { 
      PRAGMA main.synchronous=FULL;
      PRAGMA aux.synchronous=FULL;
      PRAGMA journal_mode = DELETE;
    } 1
    w1 { 
      PRAGMA main.synchronous=NORMAL;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA journal_mode = WAL;
    } 0
    w2 { 
      PRAGMA main.synchronous=NORMAL;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA main.journal_mode=DELETE;
      PRAGMA aux.journal_mode=WAL;
    } 0
    o1a { 
      PRAGMA main.synchronous=FULL;
      PRAGMA aux.synchronous=OFF;
      PRAGMA journal_mode=DELETE;
    } 0
    o1b { 
      PRAGMA main.synchronous=OFF;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA journal_mode=DELETE;
    } 0
    m1 { 
      PRAGMA main.synchronous=NORMAL;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA main.journal_mode=DELETE;
      PRAGMA aux.journal_mode = MEMORY;
    } 0
    t1 { 
      PRAGMA main.synchronous=NORMAL;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA main.journal_mode=DELETE;
      PRAGMA aux.journal_mode = TRUNCATE;
    } 1
    p1 { 
      PRAGMA main.synchronous=NORMAL;
      PRAGMA aux.synchronous=NORMAL;
      PRAGMA main.journal_mode=DELETE;
      PRAGMA aux.journal_mode = PERSIST;
    } 1
  } {

    set tn "${tn1}.${tn2}"
  
    # Set up a connection to have two databases, test.db (main) and 
    # test.db2 (aux). Then run a multi-file transaction on them. The
    # VFS will snapshot the file-system just before the master-journal
    # file is deleted to commit the transaction.
    #
    tv filter xDelete
    do_test pager1-4.4.$tn.1 {
      set ::mj_delete_cnt 0
      faultsim_delete_and_reopen $prefix
      execsql "
        ATTACH '${prefix}2' AS aux;
        $sql
        CREATE TABLE a(x);
        CREATE TABLE aux.b(x);
        INSERT INTO a VALUES('double-you');
................................................................................
        BEGIN;
          INSERT INTO a SELECT * FROM b WHERE rowid<=3;
          INSERT INTO b SELECT * FROM a WHERE rowid<=3;
        COMMIT;
      }
    } {}
    tv filter {}

    # Verify that a master journal was deleted only for those cases where
    # master journals really ought to be used
    #
    do_test pager1-4.4.$tn.1b {
      set ::mj_delete_cnt
    } $usesMJ
    
    # Check that the transaction was committed successfully.
    #
    do_execsql_test pager1-4.4.$tn.2 {
      SELECT * FROM a
    } {double-you why zed won too free}
    do_execsql_test pager1-4.4.$tn.3 {
      SELECT * FROM b
    } {won too free double-you why zed}
    
    if {$usesMJ} {
      # Restore the file-system and reopen the databases. Check that it now
      # appears that the transaction was not committed (because the file-system
      # was restored to the state where it had not been).
      #
      do_test pager1-4.4.$tn.4 {
        faultsim_restore_and_reopen $prefix
        execsql "ATTACH '${prefix}2' AS aux"
      } {}
      do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
      do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}
    }
    
    # Restore the file-system again. This time, before reopening the databases,
    # delete the master-journal file from the file-system. It now appears that
    # the transaction was committed (no master-journal file == no rollback).
    #
    do_test pager1-4.4.$tn.7 {
      if {$::mj_delete_cnt>0} {
        faultsim_restore_and_reopen $prefix
        foreach f [glob ${prefix}-mj*] { forcedelete $f }
      } else {
        db close
        sqlite3 db $prefix
      }
      execsql "ATTACH '${prefix}2' AS aux"
      glob -nocomplain ${prefix}-mj*
    } {}
    do_execsql_test pager1-4.4.$tn.8 {
      SELECT * FROM a
    } {double-you why zed won too free}
    do_execsql_test pager1-4.4.$tn.9 {
      SELECT * FROM b
    } {won too free double-you why zed}
................................................................................
  }

  cd $pwd
}
db close
tv delete
forcedelete $dirname


# Set up a VFS to make a copy of the file-system just before deleting a
# journal file to commit a transaction. The transaction modifies exactly
# two database pages (and page 1 - the change counter).
#
testvfs tv -default 1
tv sectorsize 512