SQLite Page Cache之事务处理(2)

Myth丶恋晨 2022-07-26 12:20 112阅读 0赞

写在前面：个人认为pager层是SQLite实现最为核心的模块，它具有四大功能：I/O，页面缓存，并发控制和日志恢复。而这些功能不仅是上层Btree的基础，而且对系统的性能和健壮性有关至关重要的影响。其中并发控制和日志恢复是事务处理实现的基础。SQLite并发控制的机制非常简单——封锁机制；别外，它的查询优化机制也非常简单——基于索引。这一切使得整个SQLite的实现变得简单，SQLite变得很小，运行速度也非常快，所以，特别适合嵌入式设备。好了，接下来讨论事务的剩余部分。  
6、修改位于用户进程空间的页面(Changing Database Pages In User Space)  
页面的原始数据写入日志之后，就可以修改页面了——位于用户进程空间。每个数据库连接都有自己私有的空间，所以页面的变化只对该连接可见，而对其它连接的数据仍然是磁盘缓存中的数据。从这里可以明白一件事：一个进程在修改页面数据的同时，其它进程可以继续进行读操作。图中的红色表示修改的页面。

![commit-5.gif][]

7、日志文件刷入磁盘(Flushing The Rollback Journal File To Mass Storage)  
接下来把日志文件的内容刷入磁盘，这对于数据库从意外中恢复来说是至关重要的一步。而且这通常也是一个耗时的操作，因为磁盘I/O速度很慢。  
这个步骤不只把日志文件刷入磁盘那么简单，它的实现实际上分成两步：首先把日志文件的内容刷入磁盘（即页面数据）；然后把日志文件中页面的数目写入日志文件头，再把header刷入磁盘（这一过程在代码中清晰可见）。

![commit-6.gif][]

代码如下：

![ExpandedBlockStart.gif][]   
/\*  
\*\*Sync日志文件,保证所有的脏页面写入磁盘日志文件  
\*/  
staticint syncJournal(Pager \*pPager)\{  
PgHdr \*pPg;  
int rc = SQLITE\_OK;  
  
/\* Sync the journal before modifying the main database  
\*\* (assuming there is a journal and it needs to be synced.)  
\*/  
if( pPager\->needSync )\{  
if( !pPager\->tempFile )\{  
assert( pPager\->journalOpen );  
/\* assert( !pPager->noSync ); // noSync might be set if synchronous  
\*\* was turned off after the transaction was started. Ticket \#615 \*/  
\#ifndef NDEBUG  
\{  
/\* Make sure the pPager->nRec counter we are keeping agrees  
\*\* with the nRec computed from the size of the journal file.  
\*/  
i64 jSz;  
rc = sqlite3OsFileSize(pPager\->jfd, &jSz);  
if( rc!=0 ) return rc;  
assert( pPager\->journalOff==jSz );  
\}  
\#endif  
\{  
/\* Write the nRec value into the journal file header. If in  
\*\* full-synchronous mode, sync the journal first. This ensures that  
\*\* all data has really hit the disk before nRec is updated to mark  
\*\* it as a candidate for rollback.  
\*/  
if( pPager\->fullSync )\{  
TRACE2("SYNC journal of %d\\n", PAGERID(pPager));  
//首先保证脏页面中所有的数据都已经写入日志文件  
 rc = sqlite3OsSync(pPager\->jfd, 0);  
if( rc!=0 ) return rc;  
\}  
rc = sqlite3OsSeek(pPager\->jfd,  
pPager\->journalHdr \+sizeof(aJournalMagic));  
if( rc ) return rc;  
//页面的数目写入日志文件  
 rc = write32bits(pPager\->jfd, pPager\->nRec);  
if( rc ) return rc;  
  
rc = sqlite3OsSeek(pPager\->jfd, pPager\->journalOff);  
if( rc ) return rc;  
\}  
TRACE2("SYNC journal of %d\\n", PAGERID(pPager));  
rc = sqlite3OsSync(pPager\->jfd, pPager\->full\_fsync);  
if( rc!=0 ) return rc;  
pPager\->journalStarted =1;  
\}  
pPager\->needSync =0;  
  
/\* Erase the needSync flag from every page.  
\*/  
//清除needSync标志位  
for(pPg=pPager\->pAll; pPg; pPg=pPg\->pNextAll)\{  
pPg\->needSync =0;  
\}  
pPager\->pFirstSynced = pPager\->pFirst;  
\}  
  
\#ifndef NDEBUG  
/\* If the Pager.needSync flag is clear then the PgHdr.needSync  
\*\* flag must also be clear for all pages. Verify that this  
\*\* invariant is true.  
\*/  
else\{  
for(pPg=pPager\->pAll; pPg; pPg=pPg\->pNextAll)\{  
assert( pPg\->needSync==0 );  
\}  
assert( pPager\->pFirstSynced==pPager\->pFirst );  
\}  
\#endif  
  
return rc;  
\}

8、获取排斥锁（Obtaining An Exclusive Lock）  
在对数据库文件进行修改之前(注：这里不是内存中的页面),我们必须得到数据库文件的排斥锁(Exclusive Lock)。得到排斥锁的过程可分为两步：首先得到Pending lock；然后Pending lock升级到exclusive lock。  
Pending lock允许其它已经存在的Shared lock继续读数据库文件，但是不允许产生新的shared lock，这样做目的是为了防止写操作发生饿死情况。一旦所有的shared lock完成操作，则pending lock升级到exclusive lock。

![commit-7.gif][]

9、修改的页面写入文件(Writing Changes To The Database File)  
一旦得到exclusive lock，其它的进程就不能进行读操作，此时就可以把修改的页面写回数据库文件，但是通常OS都把结果暂时保存到磁盘缓存中，直到某个时刻才会真正把结果写入磁盘。

![commit-8.gif][]

以上两步的实现代码：

![ExpandedBlockStart.gif][]   
//把所有的脏页面写入数据库  
//到这里开始获取EXCLUSIVEQ锁，并将页面写回操作系统文件  
staticint pager\_write\_pagelist(PgHdr \*pList)\{  
Pager \*pPager;  
int rc;  
  
if( pList==0 ) return SQLITE\_OK;  
pPager = pList\->pPager;  
  
/\* At this point there may be either a RESERVED or EXCLUSIVE lock on the  
\*\* database file. If there is already an EXCLUSIVE lock, the following  
\*\* calls to sqlite3OsLock() are no-ops.  
\*\*  
\*\* Moving the lock from RESERVED to EXCLUSIVE actually involves going  
\*\* through an intermediate state PENDING. A PENDING lock prevents new  
\*\* readers from attaching to the database but is unsufficient for us to  
\*\* write. The idea of a PENDING lock is to prevent new readers from  
\*\* coming in while we wait for existing readers to clear.  
\*\*  
\*\* While the pager is in the RESERVED state, the original database file  
\*\* is unchanged and we can rollback without having to playback the  
\*\* journal into the original database file. Once we transition to  
\*\* EXCLUSIVE, it means the database file has been changed and any rollback  
\*\* will require a journal playback.  
\*/  
//加EXCLUSIVE\_LOCK锁  
 rc = pager\_wait\_on\_lock(pPager, EXCLUSIVE\_LOCK);  
if( rc!=SQLITE\_OK )\{  
return rc;  
\}  
  
while( pList )\{  
assert( pList\->dirty );  
rc = sqlite3OsSeek(pPager\->fd, (pList\->pgno\-1)\*(i64)pPager\->pageSize);  
if( rc ) return rc;  
/\* If there are dirty pages in the page cache with page numbers greater  
\*\* than Pager.dbSize, this means sqlite3pager\_truncate() was called to  
\*\* make the file smaller (presumably by auto-vacuum code). Do not write  
\*\* any such pages to the file.  
\*/  
if( pList\->pgno<=pPager\->dbSize )\{  
char\*pData = CODEC2(pPager, PGHDR\_TO\_DATA(pList), pList\->pgno, 6);  
TRACE3("STORE %d page %d\\n", PAGERID(pPager), pList\->pgno);  
//写入文件  
 rc = sqlite3OsWrite(pPager\->fd, pData, pPager\->pageSize);  
TEST\_INCR(pPager\->nWrite);  
\}  
\#ifndef NDEBUG  
else\{  
TRACE3("NOSTORE %d page %d\\n", PAGERID(pPager), pList\->pgno);  
\}  
\#endif  
if( rc ) return rc;  
//设置dirty  
 pList\->dirty =0;  
\#ifdef SQLITE\_CHECK\_PAGES  
pList\->pageHash = pager\_pagehash(pList);  
\#endif  
//指向下一个脏页面  
 pList = pList\->pDirty;  
\}  
return SQLITE\_OK;  
\}

10、修改结果刷入存储设备(Flushing Changes To Mass Storage)  
为了保证修改结果真正写入磁盘，这一步必不要少。对于数据库存的完整性，这一步也是关键的一步。由于要进行实际的I/O操作，所以和第7步一样，将花费较多的时间。

![commit-9.gif][]

最后来看看这几步是如何实现的：

其实以上以上几步是在函数sqlite3BtreeSync()---btree.c中调用的(而关于该函数的调用后面再讲)。

代码如下：

![ExpandedBlockStart.gif][]   
//同步btree对应的数据库文件  
//该函数返回之后，只需要提交写事务,删除日志文件  
int sqlite3BtreeSync(Btree \*p, constchar\*zMaster)\{  
int rc = SQLITE\_OK;  
if( p\->inTrans==TRANS\_WRITE )\{  
BtShared \*pBt = p\->pBt;  
Pgno nTrunc =0;  
\#ifndef SQLITE\_OMIT\_AUTOVACUUM  
if( pBt\->autoVacuum )\{  
rc = autoVacuumCommit(pBt, &nTrunc);  
if( rc!=SQLITE\_OK )\{  
return rc;  
\}  
\}  
\#endif  
  
//调用pager进行sync  
 rc = sqlite3pager\_sync(pBt\->pPager, zMaster, nTrunc);  
\}  
return rc;  
\}  
  
//把pager所有脏页面写回文件  
int sqlite3pager\_sync(Pager \*pPager, constchar\*zMaster, Pgno nTrunc)\{  
int rc = SQLITE\_OK;  
  
TRACE4("DATABASE SYNC: File=%s zMaster=%s nTrunc=%d\\n",  
pPager\->zFilename, zMaster, nTrunc);  
  
/\* If this is an in-memory db, or no pages have been written to, or this  
\*\* function has already been called, it is a no-op.  
\*/  
//pager不处于PAGER\_SYNCED状态,dirtyCache为1,  
//则进行sync操作  
if( pPager\->state!=PAGER\_SYNCED &&!MEMDB && pPager\->dirtyCache )\{  
PgHdr \*pPg;  
assert( pPager\->journalOpen );  
  
/\* If a master journal file name has already been written to the  
\*\* journal file, then no sync is required. This happens when it is  
\*\* written, then the process fails to upgrade from a RESERVED to an  
\*\* EXCLUSIVE lock. The next time the process tries to commit the  
\*\* transaction the m-j name will have already been written.  
\*/  
if( !pPager\->setMaster )\{  
//pager修改计数  
 rc = pager\_incr\_changecounter(pPager);  
if( rc!=SQLITE\_OK ) goto sync\_exit;  
\#ifndef SQLITE\_OMIT\_AUTOVACUUM  
if( nTrunc!=0 )\{  
/\* If this transaction has made the database smaller, then all pages  
\*\* being discarded by the truncation must be written to the journal  
\*\* file.  
\*/  
Pgno i;  
void\*pPage;  
int iSkip = PAGER\_MJ\_PGNO(pPager);  
for( i=nTrunc\+1; i<=pPager\->origDbSize; i\++ )\{  
if( !(pPager\->aInJournal\[i/8\] & (1<<(i&7))) && i!=iSkip )\{  
rc = sqlite3pager\_get(pPager, i, &pPage);  
if( rc!=SQLITE\_OK ) goto sync\_exit;  
rc = sqlite3pager\_write(pPage);  
sqlite3pager\_unref(pPage);  
if( rc!=SQLITE\_OK ) goto sync\_exit;  
\}  
\}  
\}  
\#endif  
rc = writeMasterJournal(pPager, zMaster);  
if( rc!=SQLITE\_OK ) goto sync\_exit;  
  
//sync日志文件  
 rc = syncJournal(pPager);  
if( rc!=SQLITE\_OK ) goto sync\_exit;  
\}  
  
\#ifndef SQLITE\_OMIT\_AUTOVACUUM  
if( nTrunc!=0 )\{  
rc = sqlite3pager\_truncate(pPager, nTrunc);  
if( rc!=SQLITE\_OK ) goto sync\_exit;  
\}  
\#endif  
  
/\* Write all dirty pages to the database file \*/  
pPg = pager\_get\_all\_dirty\_pages(pPager);  
  
  
//把所有脏页面写回操作系统文件  
 rc = pager\_write\_pagelist(pPg);  
if( rc!=SQLITE\_OK ) goto sync\_exit;  
  
/\* Sync the database file. \*/  
//sync数据库文件  
if( !pPager\->noSync )\{  
rc = sqlite3OsSync(pPager\->fd, 0);  
\}  
  
pPager\->state = PAGER\_SYNCED;  
\}elseif( MEMDB && nTrunc!=0 )\{  
rc = sqlite3pager\_truncate(pPager, nTrunc);  
\}  
  
sync\_exit:  
return rc;  
\}

下图可以进一步解释该过程：

![sqlite04-02.GIF][]

[commit-5.gif]: /images/20220724/df732e7d40d7429e83871307bb18f417.png
[commit-6.gif]: /images/20220724/d4564116d05241b3975a55dca20d2dcd.png
[ExpandedBlockStart.gif]: /images/20220724/cde42e49b80e4ac5903245e2855bddef.png
[commit-7.gif]: /images/20220724/4ade42c758c2439288bc72c78d8b58df.png
[commit-8.gif]: /images/20220724/bfc1d716b7f04bf18ec56c9c8f377be2.png
[commit-9.gif]: /images/20220724/bfea0e29d582493e88353fd695452a4b.png
[sqlite04-02.GIF]: /images/20220724/5b389c4e078448189620a80027145525.png