SQLite—-Page Cache之事务处理(3)

写在前面：由于内容较多，所以断续没有写完的内容。

11、删除日志文件(Deleting The Rollback Journal)

一旦更改写入设备，日志文件将会被删除，这是事务真正提交的时刻。如果在这之前系统发生崩溃，就会进行恢复处理，使得数据库和没发生改变一样；如果在这之后系统发生崩溃，表明所有的更改都已经写入磁盘。SQLite就是根据日志存在情况决定是否对数据库进行恢复处理。

删除文件本质上不是一个原子操作，但是从用户进程的角度来看是一个原子操作，所以一个事务看起来是一个原子操作。 在许多系统中，删除文件也是一个高代价的操作。作为优化，SQLite可以配置成把日志文件的长度截为0或者把日志文件头清零。 document/2015-09-15/55f7c5015a12a

12、释放锁(Releasing The Lock)

作为原子提交的最后一步，释放排斥锁使得其它进程可以开始访问数据库了。 下图中，我们指明了当锁被释放的时候用户空间所拥有的信息已经被清空了.对于老版本的SQLite你可这么认为。但最新的SQLite会保存些用户空间的缓存不会被清空—万一下一个事务开始的时候，这些数据刚好可以用上呢。重新利用这些内存要比再次从操作系统磁盘缓存或者硬盘中读取要来得轻松与快捷得多，何乐而不为呢？在再次使用这些数据之前，我们必须先取得一个共享锁，同时我们还不得不去检查一下，保证还没有其他进程在我们拥有共享锁之前对数据库文件进行了修改。数据库文件的第一页中有一个计数器，数据库文件每做一次修改，这个计数器就会增长一下。我们可以通过检查这个计数器就可得知是否有其他进程修改过数据库文件。如果数据库文件已经被修改过了，那么用户内存空间的缓存就不得不清空，并重新读入。大多数情况下，这种情况不大会发生，因此用户空间的内存缓存将是有效的，这对于性能提高来说作用是显著的。 document/2015-09-15/55f7c5110eb5c

以上两步是在sqlite3BtreeCommit()—-btree.c函数中实现的。

代码如下：

  1. //提交事务,至此一个事务完成.主要做两件事:
  2. //删除日志文件,释放数据库文件的写锁
  3. int sqlite3BtreeCommit(Btree *p){
  4.   BtShared *pBt = p->pBt;
  6.   btreeIntegrity(p);
  8.   /* If the handle has a write-transaction open, commit the shared-btrees 
  9.   ** transaction and set the shared state to TRANS_READ.
  10.   */
  11.   if( p->inTrans==TRANS_WRITE ){
  12.     int rc;
  13.     assert( pBt->inTransaction==TRANS_WRITE );
  14.     assert( pBt->nTransaction>0 );
  16.    //调用pager，提交事务
  17.     rc = sqlite3pager_commit(pBt->pPager);
  18.     if( rc!=SQLITE_OK ){
  19.       return rc;
  20.     }
  21.     pBt->inTransaction = TRANS_READ;
  22.     pBt->inStmt = 0;
  23.   }
  24.   unlockAllTables(p);
  26.   /* If the handle has any kind of transaction open, decrement the transaction
  27.   ** count of the shared btree. If the transaction count reaches 0, set
  28.   ** the shared state to TRANS_NONE. The unlockBtreeIfUnused() call below
  29.   ** will unlock the pager.
  30.   */
  31.   if( p->inTrans!=TRANS_NONE ){
  32.     pBt->nTransaction--;
  33.     if( 0==pBt->nTransaction ){
  34.       pBt->inTransaction = TRANS_NONE;
  35.     }
  36.   }
  37. }
  39. //提交事务，主要调用pager_unwritelock()函数
  40. int sqlite3pager_commit(Pager *pPager){
  41.   int rc;
  42.   PgHdr *pPg;
  44.   if( pPager->errCode ){
  45.     return pPager->errCode;
  46.   }
  47.   if( pPager->state<PAGER_RESERVED ){
  48.     return SQLITE_ERROR;
  49.   }
  50.   TRACE2("COMMIT %d\n", PAGERID(pPager));
  51.   if( MEMDB ){
  52.     pPg = pager_get_all_dirty_pages(pPager);
  53.     while( pPg ){
  54.       clearHistory(PGHDR_TO_HIST(pPg, pPager));
  55.       pPg->dirty = 0;
  56.       pPg->inJournal = 0;
  57.       pPg->inStmt = 0;
  58.       pPg->needSync = 0;
  59.       pPg->pPrevStmt = pPg->pNextStmt = 0;
  60.       pPg = pPg->pDirty;
  61.     }
  62.     pPager->pDirty = 0;
  63. #ifndef NDEBUG
  64.     for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
  65.       PgHistory *pHist = PGHDR_TO_HIST(pPg, pPager);
  66.       assert( !pPg->alwaysRollback );
  67.       assert( !pHist->pOrig );
  68.       assert( !pHist->pStmt );
  69.     }
  70. #endif
  71.     pPager->pStmt = 0;
  72.     pPager->state = PAGER_SHARED;
  73.     return SQLITE_OK;
  74.   }
  75.   if( pPager->dirtyCache==0 ){
  76.     /* Exit early (without doing the time-consuming sqlite3OsSync() calls)
  77.     ** if there have been no changes to the database file. */
  78.     assert( pPager->needSync==0 );
  79.     rc = pager_unwritelock(pPager);
  80.     pPager->dbSize = -1;
  81.     return rc;
  82.   }
  83.   assert( pPager->journalOpen );
  84.   rc = sqlite3pager_sync(pPager, 0, 0);
  86.   //删除文件,释放写锁
  87.   if( rc==SQLITE_OK ){
  88.     rc = pager_unwritelock(pPager);
  89.     pPager->dbSize = -1;
  90.   }
  91.   return rc;
  92. }
  94. //对数据库加read lock，删除日志文件
  95. static int pager_unwritelock(Pager *pPager){
  96.   PgHdr *pPg;
  97.   int rc;
  98.   assert( !MEMDB );
  99.   if( pPager->state<PAGER_RESERVED ){
  100.     return SQLITE_OK;
  101.   }
  102.   sqlite3pager_stmt_commit(pPager);
  103.   if( pPager->stmtOpen ){
  104.     sqlite3OsClose(&pPager->stfd);
  105.     pPager->stmtOpen = 0;
  106.   }
  107.   if( pPager->journalOpen ){
  109.    //关闭日志文件
  110.     sqlite3OsClose(&pPager->jfd);
  111.     pPager->journalOpen = 0;
  112.     //删除日志文件
  113.     sqlite3OsDelete(pPager->zJournal);
  114.     sqliteFree( pPager->aInJournal );
  115.     pPager->aInJournal = 0;
  116.     for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
  117.       pPg->inJournal = 0;
  118.       pPg->dirty = 0;
  119.       pPg->needSync = 0;
  120. #ifdef SQLITE_CHECK_PAGES
  121.       pPg->pageHash = pager_pagehash(pPg);
  122. #endif
  123.     }
  124.     pPager->pDirty = 0;
  125.     pPager->dirtyCache = 0;
  126.     pPager->nRec = 0;
  127.   }else{
  128.     assert( pPager->aInJournal==0 );
  129.     assert( pPager->dirtyCache==0 || pPager->useJournal==0 );
  130.   }
  132.   //释放写锁，加读锁
  133.   rc = sqlite3OsUnlock(pPager->fd, SHARED_LOCK);
  134.   pPager->state = PAGER_SHARED;
  135.   pPager->origDbSize = 0;
  136.   pPager->setMaster = 0;
  137.   pPager->needSync = 0;
  138.   pPager->pFirstSynced = pPager->pFirst;
  139.   return rc;
  140. }

下图可进一步描述该过程： document/2015-09-15/55f7c538bb005 最后来看看sqlite3BtreeSync()和sqlite3BtreeCommit()是如何被调用的。

一般来说，事务提交方式为自动提交的话，在虚拟机中的OP_Halt指令实现提交事务，相关代码如下：

  1. //虚拟机停机指令
  2. case OP_Halt: {            /* no-push */
  3.   p->pTos = pTos;
  4.   p->rc = pOp->p1;
  5.   p->pc = pc;
  6.   p->errorAction = pOp->p2;
  7.   if( pOp->p3 ){
  8.     sqlite3SetString(&p->zErrMsg, pOp->p3, (char*)0);
  9.   }
  10.   //设置虚拟机状态SQLITE_MAGIC_RUN 为 SQLITE_MAGIC_HALT,
  11.   //并提交事务
  12.   rc = sqlite3VdbeHalt(p);
  13.   assert( rc==SQLITE_BUSY || rc==SQLITE_OK );
  14.   if( rc==SQLITE_BUSY ){
  15.     p->rc = SQLITE_BUSY;
  16.     return SQLITE_BUSY;
  17.   }
  18.   return p->rc ? SQLITE_ERROR : SQLITE_DONE;
  19. }

//当虚拟机要停机时,调用该函数,如果VDBE改变了数据库且为自动 //提交模式，则提交这些改变

  1. int sqlite3VdbeHalt(Vdbe *p){
  2.   sqlite3 *db = p->db;
  3.   int i;
  4.   int (*xFunc)(Btree *pBt) = 0;  /* Function to call on each btree backend */
  5.   int isSpecialError;            /* Set to true if SQLITE_NOMEM or IOERR */
  7.   /* This function contains the logic that determines if a statement or
  8.   ** transaction will be committed or rolled back as a result of the
  9.   ** execution of this virtual machine. 
  10.   **
  11.   ** Special errors:
  12.   **
  13.   **     If an SQLITE_NOMEM error has occured in a statement that writes to
  14.   **     the database, then either a statement or transaction must be rolled
  15.   **     back to ensure the tree-structures are in a consistent state. A
  16.   **     statement transaction is rolled back if one is open, otherwise the
  17.   **     entire transaction must be rolled back.
  18.   **
  19.   **     If an SQLITE_IOERR error has occured in a statement that writes to
  20.   **     the database, then the entire transaction must be rolled back. The
  21.   **     I/O error may have caused garbage to be written to the journal 
  22.   **     file. Were the transaction to continue and eventually be rolled 
  23.   **     back that garbage might end up in the database file.
  24.   **     
  25.   **     In both of the above cases, the Vdbe.errorAction variable is 
  26.   **     ignored. If the sqlite3.autoCommit flag is false and a transaction
  27.   **     is rolled back, it will be set to true.
  28.   **
  29.   ** Other errors:
  30.   **
  31.   ** No error:
  32.   **
  33.   */
  35.   if( sqlite3MallocFailed() ){
  36.     p->rc = SQLITE_NOMEM;
  37.   }
  38.   if( p->magic!=VDBE_MAGIC_RUN ){
  39.     /* Already halted.  Nothing to do. */
  40.     assert( p->magic==VDBE_MAGIC_HALT );
  41.     return SQLITE_OK;
  42.   }
  43.   //释放虚拟机中所有的游标
  44.   closeAllCursors(p);
  45.   checkActiveVdbeCnt(db);
  47.   /* No commit or rollback needed if the program never started */
  48.   if( p->pc>=0 ){
  50.     /* Check for one of the special errors - SQLITE_NOMEM or SQLITE_IOERR */
  51.     isSpecialError = ((p->rc==SQLITE_NOMEM || p->rc==SQLITE_IOERR)?1:0);
  52.     if( isSpecialError ){
  53.       /* This loop does static analysis of the query to see which of the
  54.       ** following three categories it falls into:
  55.       **
  56.       **     Read-only
  57.       **     Query with statement journal
  58.       **     Query without statement journal
  59.       **
  60.       ** We could do something more elegant than this static analysis (i.e.
  61.       ** store the type of query as part of the compliation phase), but 
  62.       ** handling malloc() or IO failure is a fairly obscure edge case so 
  63.       ** this is probably easier. Todo: Might be an opportunity to reduce 
  64.       ** code size a very small amount though
  65.       */
  66.       int isReadOnly = 1;
  67.       int isStatement = 0;
  68.       assert(p->aOp || p->nOp==0);
  69.       for(i=0; i<p->nOp; i++){ 
  70.         switch( p->aOp[i].opcode ){
  71.           case OP_Transaction:
  72.             isReadOnly = 0;
  73.             break;
  74.           case OP_Statement:
  75.             isStatement = 1;
  76.             break;
  77.         }
  78.       }
  80.       /* If the query was read-only, we need do no rollback at all. Otherwise,
  81.       ** proceed with the special handling.
  82.       */
  83.       if( !isReadOnly ){
  84.         if( p->rc==SQLITE_NOMEM && isStatement ){
  85.           xFunc = sqlite3BtreeRollbackStmt;
  86.         }else{
  87.           /* We are forced to roll back the active transaction. Before doing
  88.           ** so, abort any other statements this handle currently has active.
  89.           */
  90.           sqlite3AbortOtherActiveVdbes(db, p);
  91.           sqlite3RollbackAll(db);
  92.           db->autoCommit = 1;
  93.         }
  94.       }
  95.     }
  97.     /* If the auto-commit flag is set and this is the only active vdbe, then
  98.     ** we do either a commit or rollback of the current transaction. 
  99.     **
  100.     ** Note: This block also runs if one of the special errors handled 
  101.     ** above has occured. 
  102.     */
  103.     //如果自动提交事务，则提交事务
  104.     if( db->autoCommit && db->activeVdbeCnt==1 ){
  105.       if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){
  106.     /* The auto-commit flag is true, and the vdbe program was 
  107.         ** successful or hit an 'OR FAIL' constraint. This means a commit 
  108.         ** is required.
  109.         */
  110.         //提交事务
  111.         int rc = vdbeCommit(db);
  112.         if( rc==SQLITE_BUSY ){
  113.           return SQLITE_BUSY;
  114.         }else if( rc!=SQLITE_OK ){
  115.           p->rc = rc;
  116.           sqlite3RollbackAll(db);
  117.         }else{
  118.           sqlite3CommitInternalChanges(db);
  119.         }
  120.       }else{
  121.         sqlite3RollbackAll(db);
  122.       }
  123.     }else if( !xFunc ){
  124.       if( p->rc==SQLITE_OK || p->errorAction==OE_Fail ){
  125.         xFunc = sqlite3BtreeCommitStmt;
  126.       }else if( p->errorAction==OE_Abort ){
  127.         xFunc = sqlite3BtreeRollbackStmt;
  128.       }else{
  129.         sqlite3AbortOtherActiveVdbes(db, p);
  130.         sqlite3RollbackAll(db);
  131.         db->autoCommit = 1;
  132.       }
  133.     }
  135.     /* If xFunc is not NULL, then it is one of sqlite3BtreeRollbackStmt or
  136.     ** sqlite3BtreeCommitStmt. Call it once on each backend. If an error occurs
  137.     ** and the return code is still SQLITE_OK, set the return code to the new
  138.     ** error value.
  139.     */
  140.     assert(!xFunc ||
  141.       xFunc==sqlite3BtreeCommitStmt ||
  142.       xFunc==sqlite3BtreeRollbackStmt
  143.     );
  144.     for(i=0; xFunc && i<db->nDb; i++){ 
  145.       int rc;
  146.       Btree *pBt = db->aDb[i].pBt;
  147.       if( pBt ){
  148.         rc = xFunc(pBt);
  149.         if( rc && (p->rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT) ){
  150.           p->rc = rc;
  151.           sqlite3SetString(&p->zErrMsg, 0);
  152.         }
  153.       }
  154.     }
  156.     /* If this was an INSERT, UPDATE or DELETE and the statement was committed, 
  157.     ** set the change counter. 
  158.     */
  159.     if( p->changeCntOn && p->pc>=0 ){
  160.       if( !xFunc || xFunc==sqlite3BtreeCommitStmt ){
  161.         sqlite3VdbeSetChanges(db, p->nChange);
  162.       }else{
  163.         sqlite3VdbeSetChanges(db, 0);
  164.       }
  165.       p->nChange = 0;
  166.     }
  168.     /* Rollback or commit any schema changes that occurred. */
  169.     if( p->rc!=SQLITE_OK && db->flags&SQLITE_InternChanges ){
  170.       sqlite3ResetInternalSchema(db, 0);
  171.       db->flags = (db->flags | SQLITE_InternChanges);
  172.     }
  173.   }
  175.   /* We have successfully halted and closed the VM.  Record this fact. */
  176.   if( p->pc>=0 ){
  177.     db->activeVdbeCnt--;
  178.   }
  179.   p->magic = VDBE_MAGIC_HALT;
  180.   checkActiveVdbeCnt(db);
  182.   return SQLITE_OK;
  183. }
  1. //提交事务,主要调用:
  2. //sqlite3BtreeSync()---同步btree, sqlite3BtreeCommit()---提交事务
  3. static int vdbeCommit(sqlite3 *db){
  4.   int i;
  5.   int nTrans = 0;  /* Number of databases with an active write-transaction */
  6.   int rc = SQLITE_OK;
  7.   int needXcommit = 0;
  9.   for(i=0; i<db->nDb; i++){ 
  10.     Btree *pBt = db->aDb[i].pBt;
  11.     if( pBt && sqlite3BtreeIsInTrans(pBt) ){
  12.       needXcommit = 1;
  13.       if( i!=1 ) nTrans++;
  14.     }
  15.   }
  17.   /* If there are any write-transactions at all, invoke the commit hook */
  18.   if( needXcommit && db->xCommitCallback ){
  19.     sqlite3SafetyOff(db);
  20.     rc = db->xCommitCallback(db->pCommitArg);
  21.     sqlite3SafetyOn(db);
  22.     if( rc ){
  23.       return SQLITE_CONSTRAINT;
  24.     }
  25.   }
  27.   /* The simple case - no more than one database file (not counting the
  28.   ** TEMP database) has a transaction active.   There is no need for the
  29.   ** master-journal.
  30.   **
  31.   ** If the return value of sqlite3BtreeGetFilename() is a zero length
  32.   ** string, it means the main database is :memory:.  In that case we do
  33.   ** not support atomic multi-file commits, so use the simple case then
  34.   ** too.
  35.   */
  36.   //简单的情况,只有一个数据库文件,不需要master-journal
  37.   if( 0==strlen(sqlite3BtreeGetFilename(db->aDb[0].pBt)) || nTrans<=1 ){
  38.     for(i=0; rc==SQLITE_OK && i<db->nDb; i++){ 
  39.       Btree *pBt = db->aDb[i].pBt;
  40.       if( pBt ){
  41.           //同步btree
  42.         rc = sqlite3BtreeSync(pBt, 0);
  43.       }
  44.     }
  46.     /* Do the commit only if all databases successfully synced */
  47.     //commite事务
  48.     if( rc==SQLITE_OK ){
  49.       for(i=0; i<db->nDb; i++){
  50.         Btree *pBt = db->aDb[i].pBt;
  51.         if( pBt ){
  52.           sqlite3BtreeCommit(pBt);
  53.         }
  54.       }
  55.     }
  56.   }
  58.   /* The complex case - There is a multi-file write-transaction active.
  59.   ** This requires a master journal file to ensure the transaction is
  60.   ** committed atomicly.
  61.   */
  62. #ifndef SQLITE_OMIT_DISKIO
  63.   else{
  64.     int needSync = 0;
  65.     char *zMaster = 0;   /* File-name for the master journal */
  66.     char const *zMainFile = sqlite3BtreeGetFilename(db->aDb[0].pBt);
  67.     OsFile *master = 0;
  69.     /* Select a master journal file name */
  70.     do {
  71.       u32 random;
  72.       sqliteFree(zMaster);
  73.       sqlite3Randomness(sizeof(random), &random);
  74.       zMaster = sqlite3MPrintf("%s-mj%08X", zMainFile, random&0x7fffffff);
  75.       if( !zMaster ){
  76.         return SQLITE_NOMEM;
  77.       }
  78.     }while( sqlite3OsFileExists(zMaster) );
  80.     /* Open the master journal. */
  81.     rc = sqlite3OsOpenExclusive(zMaster, &master, 0);
  82.     if( rc!=SQLITE_OK ){
  83.       sqliteFree(zMaster);
  84.       return rc;
  85.     }
  87.     /* Write the name of each database file in the transaction into the new
  88.     ** master journal file. If an error occurs at this point close
  89.     ** and delete the master journal file. All the individual journal files
  90.     ** still have 'null' as the master journal pointer, so they will roll
  91.     ** back independently if a failure occurs.
  92.     */
  93.     for(i=0; i<db->nDb; i++){ 
  94.       Btree *pBt = db->aDb[i].pBt;
  95.       if( i==1 ) continue;   /* Ignore the TEMP database */
  96.       if( pBt && sqlite3BtreeIsInTrans(pBt) ){
  97.         char const *zFile = sqlite3BtreeGetJournalname(pBt);
  98.         if( zFile[0]==0 ) continue;  /* Ignore :memory: databases */
  99.         if( !needSync && !sqlite3BtreeSyncDisabled(pBt) ){
  100.           needSync = 1;
  101.         }
  102.         rc = sqlite3OsWrite(master, zFile, strlen(zFile)+1);
  103.         if( rc!=SQLITE_OK ){
  104.           sqlite3OsClose(&master);
  105.           sqlite3OsDelete(zMaster);
  106.           sqliteFree(zMaster);
  107.           return rc;
  108.         }
  109.       }
  110.     }
  113.     /* Sync the master journal file. Before doing this, open the directory
  114.     ** the master journal file is store in so that it gets synced too.
  115.     */
  116.     zMainFile = sqlite3BtreeGetDirname(db->aDb[0].pBt);
  117.     rc = sqlite3OsOpenDirectory(master, zMainFile);
  118.     if( rc!=SQLITE_OK ||
  119.           (needSync && (rc=sqlite3OsSync(master,0))!=SQLITE_OK) ){
  120.       sqlite3OsClose(&master);
  121.       sqlite3OsDelete(zMaster);
  122.       sqliteFree(zMaster);
  123.       return rc;
  124.     }
  126.     /* Sync all the db files involved in the transaction. The same call
  127.     ** sets the master journal pointer in each individual journal. If
  128.     ** an error occurs here, do not delete the master journal file.
  129.     **
  130.     ** If the error occurs during the first call to sqlite3BtreeSync(),
  131.     ** then there is a chance that the master journal file will be
  132.     ** orphaned. But we cannot delete it, in case the master journal
  133.     ** file name was written into the journal file before the failure
  134.     ** occured.
  135.     */
  136.     for(i=0; i<db->nDb; i++){ 
  137.       Btree *pBt = db->aDb[i].pBt;
  138.       if( pBt && sqlite3BtreeIsInTrans(pBt) ){
  139.         rc = sqlite3BtreeSync(pBt, zMaster);
  140.         if( rc!=SQLITE_OK ){
  141.           sqlite3OsClose(&master);
  142.           sqliteFree(zMaster);
  143.           return rc;
  144.         }
  145.       }
  146.     }
  147.     sqlite3OsClose(&master);
  149.     /* Delete the master journal file. This commits the transaction. After
  150.     ** doing this the directory is synced again before any individual
  151.     ** transaction files are deleted.
  152.     */
  153.     rc = sqlite3OsDelete(zMaster);
  154.     assert( rc==SQLITE_OK );
  155.     sqliteFree(zMaster);
  156.     zMaster = 0;
  157.     rc = sqlite3OsSyncDirectory(zMainFile);
  158.     if( rc!=SQLITE_OK ){
  159.       /* This is not good. The master journal file has been deleted, but
  160.       ** the directory sync failed. There is no completely safe course of
  161.       ** action from here. The individual journals contain the name of the
  162.       ** master journal file, but there is no way of knowing if that
  163.       ** master journal exists now or if it will exist after the operating
  164.       ** system crash that may follow the fsync() failure.
  165.       */
  166.       return rc;
  167.     }
  169.     /* All files and directories have already been synced, so the following
  170.     ** calls to sqlite3BtreeCommit() are only closing files and deleting
  171.     ** journals. If something goes wrong while this is happening we don't
  172.     ** really care. The integrity of the transaction is already guaranteed,
  173.     ** but some stray 'cold' journals may be lying around. Returning an
  174.     ** error code won't help matters.
  175.     */
  176.     for(i=0; i<db->nDb; i++){ 
  177.       Btree *pBt = db->aDb[i].pBt;
  178.       if( pBt ){
  179.         sqlite3BtreeCommit(pBt);
  180.       }
  181.     }
  182.   }
  183. #endif
  185.   return rc;
  186. }