SQLite Page Cache之事务处理(3)

浅浅的花香味﹌ 2022-07-26 12:20 135阅读 0赞

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

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

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

![commit-A.gif][]

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

![commit-B.gif][]

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

代码如下：

![ExpandedBlockStart.gif][]   
//提交事务,至此一个事务完成.主要做两件事:  
//删除日志文件,释放数据库文件的写锁  
int sqlite3BtreeCommit(Btree \*p)\{  
BtShared \*pBt = p\->pBt;  
  
btreeIntegrity(p);  
  
/\* If the handle has a write-transaction open, commit the shared-btrees  
\*\* transaction and set the shared state to TRANS\_READ.  
\*/  
if( p\->inTrans==TRANS\_WRITE )\{  
int rc;  
assert( pBt\->inTransaction==TRANS\_WRITE );  
assert( pBt\->nTransaction>0 );  
  
//调用pager，提交事务  
 rc = sqlite3pager\_commit(pBt\->pPager);  
if( rc!=SQLITE\_OK )\{  
return rc;  
\}  
pBt\->inTransaction = TRANS\_READ;  
pBt\->inStmt =0;  
\}  
unlockAllTables(p);  
  
/\* If the handle has any kind of transaction open, decrement the transaction  
\*\* count of the shared btree. If the transaction count reaches 0, set  
\*\* the shared state to TRANS\_NONE. The unlockBtreeIfUnused() call below  
\*\* will unlock the pager.  
\*/  
if( p\->inTrans!=TRANS\_NONE )\{  
pBt\->nTransaction\--;  
if( 0==pBt\->nTransaction )\{  
pBt\->inTransaction = TRANS\_NONE;  
\}  
\}  
\}  
  
//提交事务，主要调用pager\_unwritelock()函数  
int sqlite3pager\_commit(Pager \*pPager)\{  
int rc;  
PgHdr \*pPg;  
  
if( pPager\->errCode )\{  
return pPager\->errCode;  
\}  
if( pPager\->state<PAGER\_RESERVED )\{  
return SQLITE\_ERROR;  
\}  
TRACE2("COMMIT %d\\n", PAGERID(pPager));  
if( MEMDB )\{  
pPg = pager\_get\_all\_dirty\_pages(pPager);  
while( pPg )\{  
clearHistory(PGHDR\_TO\_HIST(pPg, pPager));  
pPg\->dirty =0;  
pPg\->inJournal =0;  
pPg\->inStmt =0;  
pPg\->needSync =0;  
pPg\->pPrevStmt = pPg\->pNextStmt =0;  
pPg = pPg\->pDirty;  
\}  
pPager\->pDirty =0;  
\#ifndef NDEBUG  
for(pPg=pPager\->pAll; pPg; pPg=pPg\->pNextAll)\{  
PgHistory \*pHist = PGHDR\_TO\_HIST(pPg, pPager);  
assert( !pPg\->alwaysRollback );  
assert( !pHist\->pOrig );  
assert( !pHist\->pStmt );  
\}  
\#endif  
pPager\->pStmt =0;  
pPager\->state = PAGER\_SHARED;  
return SQLITE\_OK;  
\}  
if( pPager\->dirtyCache==0 )\{  
/\* Exit early (without doing the time-consuming sqlite3OsSync() calls)  
\*\* if there have been no changes to the database file. \*/  
assert( pPager\->needSync==0 );  
rc = pager\_unwritelock(pPager);  
pPager\->dbSize =\-1;  
return rc;  
\}  
assert( pPager\->journalOpen );  
rc = sqlite3pager\_sync(pPager, 0, 0);  
  
//删除文件,释放写锁  
if( rc==SQLITE\_OK )\{  
rc = pager\_unwritelock(pPager);  
pPager\->dbSize =\-1;  
\}  
return rc;  
\}  
  
//对数据库加read lock，删除日志文件  
staticint pager\_unwritelock(Pager \*pPager)\{  
PgHdr \*pPg;  
int rc;  
assert( !MEMDB );  
if( pPager\->state<PAGER\_RESERVED )\{  
return SQLITE\_OK;  
\}  
sqlite3pager\_stmt\_commit(pPager);  
if( pPager\->stmtOpen )\{  
sqlite3OsClose(&pPager\->stfd);  
pPager\->stmtOpen =0;  
\}  
if( pPager\->journalOpen )\{  
  
//关闭日志文件  
 sqlite3OsClose(&pPager\->jfd);  
pPager\->journalOpen =0;  
//删除日志文件  
 sqlite3OsDelete(pPager\->zJournal);  
sqliteFree( pPager\->aInJournal );  
pPager\->aInJournal =0;  
for(pPg=pPager\->pAll; pPg; pPg=pPg\->pNextAll)\{  
pPg\->inJournal =0;  
pPg\->dirty =0;  
pPg\->needSync =0;  
\#ifdef SQLITE\_CHECK\_PAGES  
pPg\->pageHash = pager\_pagehash(pPg);  
\#endif  
\}  
pPager\->pDirty =0;  
pPager\->dirtyCache =0;  
pPager\->nRec =0;  
\}else\{  
assert( pPager\->aInJournal==0 );  
assert( pPager\->dirtyCache==0|| pPager\->useJournal==0 );  
\}  
  
//释放写锁，加读锁  
 rc = sqlite3OsUnlock(pPager\->fd, SHARED\_LOCK);  
pPager\->state = PAGER\_SHARED;  
pPager\->origDbSize =0;  
pPager\->setMaster =0;  
pPager\->needSync =0;  
pPager\->pFirstSynced = pPager\->pFirst;  
return rc;  
\}

下图可进一步描述该过程：

![sqlite04-03.GIF][]

最后来看看sqlite3BtreeSync()和sqlite3BtreeCommit()是如何被调用的。

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

![ExpandedBlockStart.gif][]   
//虚拟机停机指令  
case OP\_Halt: \{ /\* no-push \*/  
p\->pTos = pTos;  
p\->rc = pOp\->p1;  
p\->pc = pc;  
p\->errorAction = pOp\->p2;  
if( pOp\->p3 )\{  
sqlite3SetString(&p\->zErrMsg, pOp\->p3, (char\*)0);  
\}  
//设置虚拟机状态SQLITE\_MAGIC\_RUN 为 SQLITE\_MAGIC\_HALT,  
//并提交事务  
 rc = sqlite3VdbeHalt(p);  
assert( rc==SQLITE\_BUSY || rc==SQLITE\_OK );  
if( rc==SQLITE\_BUSY )\{  
p\->rc = SQLITE\_BUSY;  
return SQLITE\_BUSY;  
\}  
return p\->rc ? SQLITE\_ERROR : SQLITE\_DONE;  
\}  
  
//当虚拟机要停机时,调用该函数,如果VDBE改变了数据库且为自动  
//提交模式，则提交这些改变  
int sqlite3VdbeHalt(Vdbe \*p)\{  
sqlite3 \*db = p\->db;  
int i;  
int (\*xFunc)(Btree \*pBt) =0; /\* Function to call on each btree backend \*/  
int isSpecialError; /\* Set to true if SQLITE\_NOMEM or IOERR \*/  
  
/\* This function contains the logic that determines if a statement or  
\*\* transaction will be committed or rolled back as a result of the  
\*\* execution of this virtual machine.  
\*\*  
\*\* Special errors:  
\*\*  
\*\* If an SQLITE\_NOMEM error has occured in a statement that writes to  
\*\* the database, then either a statement or transaction must be rolled  
\*\* back to ensure the tree-structures are in a consistent state. A  
\*\* statement transaction is rolled back if one is open, otherwise the  
\*\* entire transaction must be rolled back.  
\*\*  
\*\* If an SQLITE\_IOERR error has occured in a statement that writes to  
\*\* the database, then the entire transaction must be rolled back. The  
\*\* I/O error may have caused garbage to be written to the journal  
\*\* file. Were the transaction to continue and eventually be rolled  
\*\* back that garbage might end up in the database file.  
\*\*  
\*\* In both of the above cases, the Vdbe.errorAction variable is  
\*\* ignored. If the sqlite3.autoCommit flag is false and a transaction  
\*\* is rolled back, it will be set to true.  
\*\*  
\*\* Other errors:  
\*\*  
\*\* No error:  
\*\*  
\*/  
  
if( sqlite3MallocFailed() )\{  
p\->rc = SQLITE\_NOMEM;  
\}  
if( p\->magic!=VDBE\_MAGIC\_RUN )\{  
/\* Already halted. Nothing to do. \*/  
assert( p\->magic==VDBE\_MAGIC\_HALT );  
return SQLITE\_OK;  
\}  
//释放虚拟机中所有的游标  
 closeAllCursors(p);  
checkActiveVdbeCnt(db);  
  
/\* No commit or rollback needed if the program never started \*/  
if( p\->pc>=0 )\{  
  
/\* Check for one of the special errors - SQLITE\_NOMEM or SQLITE\_IOERR \*/  
isSpecialError = ((p\->rc==SQLITE\_NOMEM || p\->rc==SQLITE\_IOERR)?1:0);  
if( isSpecialError )\{  
/\* This loop does static analysis of the query to see which of the  
\*\* following three categories it falls into:  
\*\*  
\*\* Read-only  
\*\* Query with statement journal  
\*\* Query without statement journal  
\*\*  
\*\* We could do something more elegant than this static analysis (i.e.  
\*\* store the type of query as part of the compliation phase), but  
\*\* handling malloc() or IO failure is a fairly obscure edge case so  
\*\* this is probably easier. Todo: Might be an opportunity to reduce  
\*\* code size a very small amount though![dot.gif][]  
\*/  
int isReadOnly =1;  
int isStatement =0;  
assert(p\->aOp || p\->nOp==0);  
for(i=0; i<p\->nOp; i\++)\{  
switch( p\->aOp\[i\].opcode )\{  
case OP\_Transaction:  
isReadOnly =0;  
break;  
case OP\_Statement:  
isStatement =1;  
break;  
\}  
\}  
  
/\* If the query was read-only, we need do no rollback at all. Otherwise,  
\*\* proceed with the special handling.  
\*/  
if( !isReadOnly )\{  
if( p\->rc==SQLITE\_NOMEM && isStatement )\{  
xFunc = sqlite3BtreeRollbackStmt;  
\}else\{  
/\* We are forced to roll back the active transaction. Before doing  
\*\* so, abort any other statements this handle currently has active.  
\*/  
sqlite3AbortOtherActiveVdbes(db, p);  
sqlite3RollbackAll(db);  
db\->autoCommit =1;  
\}  
\}  
\}  
  
/\* If the auto-commit flag is set and this is the only active vdbe, then  
\*\* we do either a commit or rollback of the current transaction.  
\*\*  
\*\* Note: This block also runs if one of the special errors handled  
\*\* above has occured.  
\*/  
//如果自动提交事务，则提交事务  
if( db\->autoCommit && db\->activeVdbeCnt==1 )\{  
if( p\->rc==SQLITE\_OK || (p\->errorAction==OE\_Fail &&!isSpecialError) )\{  
/\* The auto-commit flag is true, and the vdbe program was  
\*\* successful or hit an 'OR FAIL' constraint. This means a commit  
\*\* is required.  
\*/  
//提交事务  
int rc = vdbeCommit(db);  
if( rc==SQLITE\_BUSY )\{  
return SQLITE\_BUSY;  
\}elseif( rc!=SQLITE\_OK )\{  
p\->rc = rc;  
sqlite3RollbackAll(db);  
\}else\{  
sqlite3CommitInternalChanges(db);  
\}  
\}else\{  
sqlite3RollbackAll(db);  
\}  
\}elseif( !xFunc )\{  
if( p\->rc==SQLITE\_OK || p\->errorAction==OE\_Fail )\{  
xFunc = sqlite3BtreeCommitStmt;  
\}elseif( p\->errorAction==OE\_Abort )\{  
xFunc = sqlite3BtreeRollbackStmt;  
\}else\{  
sqlite3AbortOtherActiveVdbes(db, p);  
sqlite3RollbackAll(db);  
db\->autoCommit =1;  
\}  
\}  
  
/\* If xFunc is not NULL, then it is one of sqlite3BtreeRollbackStmt or  
\*\* sqlite3BtreeCommitStmt. Call it once on each backend. If an error occurs  
\*\* and the return code is still SQLITE\_OK, set the return code to the new  
\*\* error value.  
\*/  
assert(!xFunc ||  
xFunc==sqlite3BtreeCommitStmt ||  
xFunc==sqlite3BtreeRollbackStmt  
);  
for(i=0; xFunc && i<db\->nDb; i\++)\{  
int rc;  
Btree \*pBt = db\->aDb\[i\].pBt;  
if( pBt )\{  
rc = xFunc(pBt);  
if( rc && (p\->rc==SQLITE\_OK || p\->rc==SQLITE\_CONSTRAINT) )\{  
p\->rc = rc;  
sqlite3SetString(&p\->zErrMsg, 0);  
\}  
\}  
\}  
  
/\* If this was an INSERT, UPDATE or DELETE and the statement was committed,  
\*\* set the change counter.  
\*/  
if( p\->changeCntOn && p\->pc>=0 )\{  
if( !xFunc || xFunc==sqlite3BtreeCommitStmt )\{  
sqlite3VdbeSetChanges(db, p\->nChange);  
\}else\{  
sqlite3VdbeSetChanges(db, 0);  
\}  
p\->nChange =0;  
\}  
  
/\* Rollback or commit any schema changes that occurred. \*/  
if( p\->rc!=SQLITE\_OK && db\->flags&SQLITE\_InternChanges )\{  
sqlite3ResetInternalSchema(db, 0);  
db\->flags = (db\->flags | SQLITE\_InternChanges);  
\}  
\}  
  
/\* We have successfully halted and closed the VM. Record this fact. \*/  
if( p\->pc>=0 )\{  
db\->activeVdbeCnt\--;  
\}  
p\->magic = VDBE\_MAGIC\_HALT;  
checkActiveVdbeCnt(db);  
  
return SQLITE\_OK;  
\}  
  
//提交事务,主要调用:  
//sqlite3BtreeSync()---同步btree, sqlite3BtreeCommit()---提交事务  
staticint vdbeCommit(sqlite3 \*db)\{  
int i;  
int nTrans =0; /\* Number of databases with an active write-transaction \*/  
int rc = SQLITE\_OK;  
int needXcommit =0;  
  
for(i=0; i<db\->nDb; i\++)\{  
Btree \*pBt = db\->aDb\[i\].pBt;  
if( pBt && sqlite3BtreeIsInTrans(pBt) )\{  
needXcommit =1;  
if( i!=1 ) nTrans\++;  
\}  
\}  
  
/\* If there are any write-transactions at all, invoke the commit hook \*/  
if( needXcommit && db\->xCommitCallback )\{  
sqlite3SafetyOff(db);  
rc = db\->xCommitCallback(db\->pCommitArg);  
sqlite3SafetyOn(db);  
if( rc )\{  
return SQLITE\_CONSTRAINT;  
\}  
\}  
  
/\* The simple case - no more than one database file (not counting the  
\*\* TEMP database) has a transaction active. There is no need for the  
\*\* master-journal.  
\*\*  
\*\* If the return value of sqlite3BtreeGetFilename() is a zero length  
\*\* string, it means the main database is :memory:. In that case we do  
\*\* not support atomic multi-file commits, so use the simple case then  
\*\* too.  
\*/  
//简单的情况,只有一个数据库文件,不需要master-journal  
if( 0==strlen(sqlite3BtreeGetFilename(db\->aDb\[0\].pBt)) || nTrans<=1 )\{  
for(i=0; rc==SQLITE\_OK && i<db\->nDb; i\++)\{  
Btree \*pBt = db\->aDb\[i\].pBt;  
if( pBt )\{  
//同步btree  
 rc = sqlite3BtreeSync(pBt, 0);  
\}  
\}  
  
/\* Do the commit only if all databases successfully synced \*/  
//commite事务  
if( rc==SQLITE\_OK )\{  
for(i=0; i<db\->nDb; i\++)\{  
Btree \*pBt = db\->aDb\[i\].pBt;  
if( pBt )\{  
sqlite3BtreeCommit(pBt);  
\}  
\}  
\}  
\}  
  
/\* The complex case - There is a multi-file write-transaction active.  
\*\* This requires a master journal file to ensure the transaction is  
\*\* committed atomicly.  
\*/  
\#ifndef SQLITE\_OMIT\_DISKIO  
else\{  
int needSync =0;  
char\*zMaster =0; /\* File-name for the master journal \*/  
charconst\*zMainFile = sqlite3BtreeGetFilename(db\->aDb\[0\].pBt);  
OsFile \*master =0;  
  
/\* Select a master journal file name \*/  
do \{  
u32 random;  
sqliteFree(zMaster);  
sqlite3Randomness(sizeof(random), &random);  
zMaster = sqlite3MPrintf("%s-mj%08X", zMainFile, random&0x7fffffff);  
if( !zMaster )\{  
return SQLITE\_NOMEM;  
\}  
\}while( sqlite3OsFileExists(zMaster) );  
  
/\* Open the master journal. \*/  
rc = sqlite3OsOpenExclusive(zMaster, &master, 0);  
if( rc!=SQLITE\_OK )\{  
sqliteFree(zMaster);  
return rc;  
\}  
  
/\* Write the name of each database file in the transaction into the new  
\*\* master journal file. If an error occurs at this point close  
\*\* and delete the master journal file. All the individual journal files  
\*\* still have 'null' as the master journal pointer, so they will roll  
\*\* back independently if a failure occurs.  
\*/  
for(i=0; i<db\->nDb; i\++)\{  
Btree \*pBt = db\->aDb\[i\].pBt;  
if( i==1 ) continue; /\* Ignore the TEMP database \*/  
if( pBt && sqlite3BtreeIsInTrans(pBt) )\{  
charconst\*zFile = sqlite3BtreeGetJournalname(pBt);  
if( zFile\[0\]==0 ) continue; /\* Ignore :memory: databases \*/  
if( !needSync &&!sqlite3BtreeSyncDisabled(pBt) )\{  
needSync =1;  
\}  
rc = sqlite3OsWrite(master, zFile, strlen(zFile)\+1);  
if( rc!=SQLITE\_OK )\{  
sqlite3OsClose(&master);  
sqlite3OsDelete(zMaster);  
sqliteFree(zMaster);  
return rc;  
\}  
\}  
\}  
  
  
/\* Sync the master journal file. Before doing this, open the directory  
\*\* the master journal file is store in so that it gets synced too.  
\*/  
zMainFile = sqlite3BtreeGetDirname(db\->aDb\[0\].pBt);  
rc = sqlite3OsOpenDirectory(master, zMainFile);  
if( rc!=SQLITE\_OK ||  
(needSync && (rc=sqlite3OsSync(master,0))!=SQLITE\_OK) )\{  
sqlite3OsClose(&master);  
sqlite3OsDelete(zMaster);  
sqliteFree(zMaster);  
return rc;  
\}  
  
/\* Sync all the db files involved in the transaction. The same call  
\*\* sets the master journal pointer in each individual journal. If  
\*\* an error occurs here, do not delete the master journal file.  
\*\*  
\*\* If the error occurs during the first call to sqlite3BtreeSync(),  
\*\* then there is a chance that the master journal file will be  
\*\* orphaned. But we cannot delete it, in case the master journal  
\*\* file name was written into the journal file before the failure  
\*\* occured.  
\*/  
for(i=0; i<db\->nDb; i\++)\{  
Btree \*pBt = db\->aDb\[i\].pBt;  
if( pBt && sqlite3BtreeIsInTrans(pBt) )\{  
rc = sqlite3BtreeSync(pBt, zMaster);  
if( rc!=SQLITE\_OK )\{  
sqlite3OsClose(&master);  
sqliteFree(zMaster);  
return rc;  
\}  
\}  
\}  
sqlite3OsClose(&master);  
  
/\* Delete the master journal file. This commits the transaction. After  
\*\* doing this the directory is synced again before any individual  
\*\* transaction files are deleted.  
\*/  
rc = sqlite3OsDelete(zMaster);  
assert( rc==SQLITE\_OK );  
sqliteFree(zMaster);  
zMaster =0;  
rc = sqlite3OsSyncDirectory(zMainFile);  
if( rc!=SQLITE\_OK )\{  
/\* This is not good. The master journal file has been deleted, but  
\*\* the directory sync failed. There is no completely safe course of  
\*\* action from here. The individual journals contain the name of the  
\*\* master journal file, but there is no way of knowing if that  
\*\* master journal exists now or if it will exist after the operating  
\*\* system crash that may follow the fsync() failure.  
\*/  
return rc;  
\}  
  
/\* All files and directories have already been synced, so the following  
\*\* calls to sqlite3BtreeCommit() are only closing files and deleting  
\*\* journals. If something goes wrong while this is happening we don't  
\*\* really care. The integrity of the transaction is already guaranteed,  
\*\* but some stray 'cold' journals may be lying around. Returning an  
\*\* error code won't help matters.  
\*/  
for(i=0; i<db\->nDb; i\++)\{  
Btree \*pBt = db\->aDb\[i\].pBt;  
if( pBt )\{  
sqlite3BtreeCommit(pBt);  
\}  
\}  
\}  
\#endif  
  
return rc;  
\}

[commit-A.gif]: /images/20220724/bdc8d8f2b59047c8919cba76b6d9f23c.png
[commit-B.gif]: /images/20220724/dacbfa4fe4704a16bd2ba17158a0c843.png
[ExpandedBlockStart.gif]: /images/20220724/e9c902d9fcb043fa85292503970a5133.png
[sqlite04-03.GIF]: /images/20220724/ce79be184bc347a682c23c7faf28898a.png
[dot.gif]: /images/20220724/9893b76d4ee14d859c1e5a4cb9432777.png