/ Check-in [3ed18906]
Login

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbeaux.c.

  2151   2151   ** A read or write transaction may or may not be active on database handle
  2152   2152   ** db. If a transaction is active, commit it. If there is a
  2153   2153   ** write-transaction spanning more than one database file, this routine
  2154   2154   ** takes care of the master journal trickery.
  2155   2155   */
  2156   2156   static int vdbeCommit(sqlite3 *db, Vdbe *p){
  2157   2157     int i;
  2158         -  int nTrans = 0;  /* Number of databases with an active write-transaction */
         2158  +  int nTrans = 0;  /* Number of databases with an active write-transaction
         2159  +                   ** that are candidates for a two-phase commit using a
         2160  +                   ** master-journal */
  2159   2161     int rc = SQLITE_OK;
  2160   2162     int needXcommit = 0;
  2161   2163   
  2162   2164   #ifdef SQLITE_OMIT_VIRTUALTABLE
  2163   2165     /* With this option, sqlite3VtabSync() is defined to be simply 
  2164   2166     ** SQLITE_OK so p is not used. 
  2165   2167     */
................................................................................
  2179   2181     ** including the temp database. (b) is important because if more than 
  2180   2182     ** one database file has an open write transaction, a master journal
  2181   2183     ** file is required for an atomic commit.
  2182   2184     */ 
  2183   2185     for(i=0; rc==SQLITE_OK && i<db->nDb; i++){ 
  2184   2186       Btree *pBt = db->aDb[i].pBt;
  2185   2187       if( sqlite3BtreeIsInTrans(pBt) ){
         2188  +      /* Whether or not a database might need a master journal depends upon
         2189  +      ** its journal mode (among other things).  This matrix determines which
         2190  +      ** journal modes use a master journal and which do not */
         2191  +      static const u8 aMJNeeded[] = {
         2192  +        /* DELETE   */  1,
         2193  +        /* PERSIST   */ 1,
         2194  +        /* OFF       */ 0,
         2195  +        /* TRUNCATE  */ 1,
         2196  +        /* MEMORY    */ 0,
         2197  +        /* WAL       */ 0
         2198  +      };
         2199  +      Pager *pPager;   /* Pager associated with pBt */
  2186   2200         needXcommit = 1;
  2187         -      if( i!=1 ) nTrans++;
  2188   2201         sqlite3BtreeEnter(pBt);
  2189         -      rc = sqlite3PagerExclusiveLock(sqlite3BtreePager(pBt));
         2202  +      pPager = sqlite3BtreePager(pBt);
         2203  +      if( db->aDb[i].safety_level!=PAGER_SYNCHRONOUS_OFF
         2204  +       && aMJNeeded[sqlite3PagerGetJournalMode(pPager)]
         2205  +      ){ 
         2206  +        assert( i!=1 );
         2207  +        nTrans++;
         2208  +      }
         2209  +      rc = sqlite3PagerExclusiveLock(pPager);
  2190   2210         sqlite3BtreeLeave(pBt);
  2191   2211       }
  2192   2212     }
  2193   2213     if( rc!=SQLITE_OK ){
  2194   2214       return rc;
  2195   2215     }
  2196   2216   

Changes to test/pager1.test.

   525    525   # file-system is saved just before the xDelete() call to remove the 
   526    526   # master journal file from the file-system.
   527    527   #
   528    528   set pwd [get_pwd]
   529    529   testvfs tv -default 1
   530    530   tv script copy_on_mj_delete
   531    531   set ::mj_filename_length 0
          532  +set ::mj_delete_cnt 0
   532    533   proc copy_on_mj_delete {method filename args} {
   533    534     if {[string match *mj* [file tail $filename]]} { 
   534    535       #
   535    536       # NOTE: Is the file name relative?  If so, add the length of the current
   536    537       #       directory.
   537    538       #
   538    539       if {[is_relative_file $filename]} {
   539    540         set ::mj_filename_length \
   540    541           [expr {[string length $filename] + [string length $::pwd]}]
   541    542       } else {
   542    543         set ::mj_filename_length [string length $filename]
   543    544       }
   544    545       faultsim_save 
          546  +    incr ::mj_delete_cnt
   545    547     }
   546    548     return SQLITE_OK
   547    549   }
   548    550   
   549    551   foreach {tn1 tcl} {
   550    552     1 { set prefix "test.db" }
   551    553     2 { 
................................................................................
   575    577       }
   576    578   
   577    579       set padding [string repeat x [expr $nPadding %32]]
   578    580       set prefix "test.db${padding}"
   579    581     }
   580    582   } {
   581    583     eval $tcl
   582         -  foreach {tn2 sql} {
          584  +  foreach {tn2 sql usesMJ} {
   583    585       o { 
   584    586         PRAGMA main.synchronous=OFF;
   585    587         PRAGMA aux.synchronous=OFF;
   586    588         PRAGMA journal_mode = DELETE;
   587         -    }
          589  +    } 0
   588    590       o512 { 
   589    591         PRAGMA main.synchronous=OFF;
   590    592         PRAGMA aux.synchronous=OFF;
   591    593         PRAGMA main.page_size = 512;
   592    594         PRAGMA aux.page_size = 512;
   593    595         PRAGMA journal_mode = DELETE;
   594         -    }
          596  +    } 0
   595    597       n { 
   596    598         PRAGMA main.synchronous=NORMAL;
   597    599         PRAGMA aux.synchronous=NORMAL;
   598    600         PRAGMA journal_mode = DELETE;
   599         -    }
          601  +    } 1
   600    602       f { 
   601    603         PRAGMA main.synchronous=FULL;
   602    604         PRAGMA aux.synchronous=FULL;
   603    605         PRAGMA journal_mode = DELETE;
   604         -    }
          606  +    } 1
          607  +    w1 { 
          608  +      PRAGMA main.synchronous=NORMAL;
          609  +      PRAGMA aux.synchronous=NORMAL;
          610  +      PRAGMA journal_mode = WAL;
          611  +    } 0
          612  +    w2 { 
          613  +      PRAGMA main.synchronous=NORMAL;
          614  +      PRAGMA aux.synchronous=NORMAL;
          615  +      PRAGMA main.journal_mode=DELETE;
          616  +      PRAGMA aux.journal_mode=WAL;
          617  +    } 0
          618  +    o1a { 
          619  +      PRAGMA main.synchronous=FULL;
          620  +      PRAGMA aux.synchronous=OFF;
          621  +      PRAGMA journal_mode=DELETE;
          622  +    } 0
          623  +    o1b { 
          624  +      PRAGMA main.synchronous=OFF;
          625  +      PRAGMA aux.synchronous=NORMAL;
          626  +      PRAGMA journal_mode=DELETE;
          627  +    } 0
          628  +    m1 { 
          629  +      PRAGMA main.synchronous=NORMAL;
          630  +      PRAGMA aux.synchronous=NORMAL;
          631  +      PRAGMA main.journal_mode=DELETE;
          632  +      PRAGMA aux.journal_mode = MEMORY;
          633  +    } 0
          634  +    t1 { 
          635  +      PRAGMA main.synchronous=NORMAL;
          636  +      PRAGMA aux.synchronous=NORMAL;
          637  +      PRAGMA main.journal_mode=DELETE;
          638  +      PRAGMA aux.journal_mode = TRUNCATE;
          639  +    } 1
          640  +    p1 { 
          641  +      PRAGMA main.synchronous=NORMAL;
          642  +      PRAGMA aux.synchronous=NORMAL;
          643  +      PRAGMA main.journal_mode=DELETE;
          644  +      PRAGMA aux.journal_mode = PERSIST;
          645  +    } 1
   605    646     } {
   606    647   
   607    648       set tn "${tn1}.${tn2}"
   608    649     
   609    650       # Set up a connection to have two databases, test.db (main) and 
   610    651       # test.db2 (aux). Then run a multi-file transaction on them. The
   611    652       # VFS will snapshot the file-system just before the master-journal
   612    653       # file is deleted to commit the transaction.
   613    654       #
   614    655       tv filter xDelete
   615    656       do_test pager1-4.4.$tn.1 {
          657  +      set ::mj_delete_cnt 0
   616    658         faultsim_delete_and_reopen $prefix
   617    659         execsql "
   618    660           ATTACH '${prefix}2' AS aux;
   619    661           $sql
   620    662           CREATE TABLE a(x);
   621    663           CREATE TABLE aux.b(x);
   622    664           INSERT INTO a VALUES('double-you');
................................................................................
   630    672           BEGIN;
   631    673             INSERT INTO a SELECT * FROM b WHERE rowid<=3;
   632    674             INSERT INTO b SELECT * FROM a WHERE rowid<=3;
   633    675           COMMIT;
   634    676         }
   635    677       } {}
   636    678       tv filter {}
          679  +
          680  +    # Verify that a master journal was deleted only for those cases where
          681  +    # master journals really ought to be used
          682  +    #
          683  +    do_test pager1-4.4.$tn.1b {
          684  +      set ::mj_delete_cnt
          685  +    } $usesMJ
   637    686       
   638    687       # Check that the transaction was committed successfully.
   639    688       #
   640    689       do_execsql_test pager1-4.4.$tn.2 {
   641    690         SELECT * FROM a
   642    691       } {double-you why zed won too free}
   643    692       do_execsql_test pager1-4.4.$tn.3 {
   644    693         SELECT * FROM b
   645    694       } {won too free double-you why zed}
   646    695       
   647         -    # Restore the file-system and reopen the databases. Check that it now
   648         -    # appears that the transaction was not committed (because the file-system
   649         -    # was restored to the state where it had not been).
   650         -    #
   651         -    do_test pager1-4.4.$tn.4 {
   652         -      faultsim_restore_and_reopen $prefix
   653         -      execsql "ATTACH '${prefix}2' AS aux"
   654         -    } {}
   655         -    do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
   656         -    do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}
          696  +    if {$usesMJ} {
          697  +      # Restore the file-system and reopen the databases. Check that it now
          698  +      # appears that the transaction was not committed (because the file-system
          699  +      # was restored to the state where it had not been).
          700  +      #
          701  +      do_test pager1-4.4.$tn.4 {
          702  +        faultsim_restore_and_reopen $prefix
          703  +        execsql "ATTACH '${prefix}2' AS aux"
          704  +      } {}
          705  +      do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
          706  +      do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}
          707  +    }
   657    708       
   658    709       # Restore the file-system again. This time, before reopening the databases,
   659    710       # delete the master-journal file from the file-system. It now appears that
   660    711       # the transaction was committed (no master-journal file == no rollback).
   661    712       #
   662    713       do_test pager1-4.4.$tn.7 {
   663         -      faultsim_restore_and_reopen $prefix
   664         -      foreach f [glob ${prefix}-mj*] { forcedelete $f }
          714  +      if {$::mj_delete_cnt>0} {
          715  +        faultsim_restore_and_reopen $prefix
          716  +        foreach f [glob ${prefix}-mj*] { forcedelete $f }
          717  +      } else {
          718  +        db close
          719  +        sqlite3 db $prefix
          720  +      }
   665    721         execsql "ATTACH '${prefix}2' AS aux"
          722  +      glob -nocomplain ${prefix}-mj*
   666    723       } {}
   667    724       do_execsql_test pager1-4.4.$tn.8 {
   668    725         SELECT * FROM a
   669    726       } {double-you why zed won too free}
   670    727       do_execsql_test pager1-4.4.$tn.9 {
   671    728         SELECT * FROM b
   672    729       } {won too free double-you why zed}
................................................................................
   673    730     }
   674    731   
   675    732     cd $pwd
   676    733   }
   677    734   db close
   678    735   tv delete
   679    736   forcedelete $dirname
   680         -
   681    737   
   682    738   # Set up a VFS to make a copy of the file-system just before deleting a
   683    739   # journal file to commit a transaction. The transaction modifies exactly
   684    740   # two database pages (and page 1 - the change counter).
   685    741   #
   686    742   testvfs tv -default 1
   687    743   tv sectorsize 512