SQLite 知識摘要 --- 事務

標籤：immediate   state   tar   本地   儲存   多個   支援   針對   mod   

在許多時候，我們在使用大資料的時候會發現，儘管sqlite資料庫的執行效率已經很快了，但是還是滿足不了我們的需求，這時候我們會很容易考慮到使用並發的方式去訪問sqlite資料庫，但是sqlite資料專屬的機制有會讓我們在使用中遇到各種問題，如死結，報錯等等。下午就詳細介紹一下sqlite的事務，瞭解sqlite事務對我們並行作業sqlite資料庫具有極大的協助。

本篇預備知識

我們先來瞭解下SQLite執行事務的基本流程，狀態變化過程，再分析怎麼使用才更優。SQLite定義的鎖的狀態有如下幾種：

• UNLOCK：最初始狀態，沒有任何鎖在資料庫上；
• SHARED：共用狀態，允許讀取資料，但是不能寫入和修改，同一時間允許有多個SHARED存在, 共用鎖定只是針對作業系統的磁碟緩衝；
• RESERVED：這個鎖意味著進程將要對資料庫進行寫操作。某一時刻只能有一個RESERVED鎖，但是RESERVED鎖和SHARED鎖可以共存，而且可以對資料庫加新的SHARED鎖。引入這個狀態是為了提高並發性，在這個狀態下可以先修改快取資料，直到將修改寫入磁碟的時候再加上獨佔鎖定；
• EXCLUSIVE：真正將資料寫入磁碟的過程，此時不允許其他任何寫入讀取操作，是獨佔鎖定；
• PENDING：可以理解為一個中間狀態，從限制小的狀態往限制高的狀態變化的一個過程。例如從RESERVED向EXCLUSIVE轉變的時候需要經過這個狀態，需要等待已有的讀寫串連完成之後再進入EXCLUSIVE。

事務在執行過程中鎖狀態之間變化，如所示：

Screen Shot 2017-03-28 at 10.55.14 PM.png

SQLite一次事務過程

一次事務.png

整個詳細的流程看 ?? 這裡

一般來說，Reserved Lock 和記錄檔是一一對應的。如果當pager 首次開啟資料庫，會做一次完整性檢查。如果發現有記錄檔但是沒有Reserved Lock ,資料庫會進入復原模式。

Recovery.png

進入復原模式後，會直接從shared 狀態到pending 狀態。那麼在資料庫連接成功恢複資料庫以前不會有其他動作。

按照正常的事務，一次操作大概要經曆：

• 一次檔案建立（復原日誌）
• 兩次寫入 (修改前把未經處理資料寫入復原日誌／資料頁的修改---資料頁在系統緩衝)
• 兩次flush 檔案 (復原日誌和資料庫變更沖入本地磁碟)
• 一次復原日誌刪除
• 三次加鎖

我們可以根據實際的使用情境來進行最佳化。

最佳化點：

• 需要批次更新資料，可以顯式使用事務
• 磁碟同步方式
• 設定高效的記錄模式
• 使用事務來避免死結

明確交易

一個新事務的建立和關閉，消耗是非常大的，因為它需要開啟、修改和關閉記錄檔。在預設情況下，調用Sqlite statement執行一條SQL語句，就會建立一個事務，在執行完這條語句後自動關閉事務。那如果我們連續執行很多條SQL的話，會不斷建立和關閉事務，這是非常浪費的，對效能的影響是非常大。對於這種情況，我們可以使用BEGIN TRANSACTION和END TRANSACTION來自助選擇事務建立和提交的時機，例如：

    sqlite_exec(sqlitedb, "BEGIN TRANSACTION;",...);    ...    執行N條SQL    ...    sqlite_exec(sqlitedb, "END TRANSACTION;",...);

加上BEGIN END之後，N條SQL只建立了一個事務，不加的話會開N個事務來完成，效果可想而知。

磁碟同步方式

SQLite在將資料提交給系統(OSBuffers)後由系統寫入磁碟，但是在這個過程中系統有可能會出現掉電或者寫入失敗等異常情況，如果SQLite不等待系統執行結果，可能會誤認為操作已成功，但實際上資料已經不一致了。對於這種情況，SQLite提供了3種同步方式：

PRAGMA synchronous = (0 | OFF) | (1 | NORMAL) | (2 | FULL)

在FULL模式下， SQLite資料庫引擎總是會暫停以確定資料已經寫入磁碟，這種模式可以保證系統崩潰或者掉電後重啟資料不會收到損壞，很安全但很慢。在NORMAL模式下，SQLite資料庫引擎在大部分情況下會暫停，但不像FULL模式下那麼頻繁，這種方式比FULL模式快，但是存在極小機率在系統掉電或故障時資料庫遭到損壞。在OFF模式下，SQLite將資料提交給系統之後不會等待結果，直接繼續執行，也就是說在一次事務的過程中會少了兩次Flush檔案操作。這種模式下如果系統在寫入的時候崩潰或者異常，資料庫就可能會被破壞，但這種模式下有些操作可以比FULL下快一個數量級。

Synchronous_off.png

預設情況是NORMAL，如果對安全性要求極高的話，可以選擇FULL模式，如果非常追求效率又不介意資料庫損壞的話（例如定期做資料庫自動備份，損壞了仍可還原），可以選擇OFF。

設定高效的記錄模式

記錄檔是SQLite實現復原至關重要的東西。預設情況下，SQLite在將修改寫入磁碟之前，會先將修改日誌刷入磁碟再將修改頁面寫入磁碟，寫入完成之後再將日誌清理掉。如果在寫入的過程中Crash，SQLite能在下次啟動時根據記錄檔恢複。但這會增加額外的磁碟讀寫開銷，影響整體的事務執行時間。不過Sqlite提供了多種記錄模式，可以通過如下命令設定：

PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF

DELETE是預設，就是在事務執行後將記錄檔刪除；TRUNCATE方式則是不刪除檔案，直接將檔案內容清空（在很多系統上，這種清空比刪除檔案要快）；PERSIST方式也不會刪除檔案，而是將檔案頭中長度欄位置為0，在某些平台上這種方式會優於前兩者；MEMORY方式則直接將日誌放在記憶體，不用磁碟儲存，這樣速度會很快但是如果宕機，日誌也會丟失，資料可能被破壞無法恢複；WAL(Write-Ahead Logging)是Sqlite3.7以後才有的一種模式，這種模式的原理是修改並不直接寫入到資料庫檔案中，而是寫入到另外一個稱為WAL的檔案中，在隨後的某個時間才被寫回到資料庫檔案中，這種方式可以提高事務的並發性，但是一旦進入這種模式就無法更改頁面大小，不能以唯讀方式開啟資料庫，且訪問資料庫的所有程式必須在同一主機上並支援共用記憶體技術，對於讀取多寫入少的情境反而會更慢,像這種讀寫頻繁的app，很適合用WAL；OFF則是完全禁用復原日誌的功能。

一般情況下，可選擇TRUNCATE或PERSIST模式，會有效能上的協助；對於那些即時性要求非常高但是資料一致性要求不是很高的情境，可以選擇MEMORY模式；3.7以上的版本，如果修改的資料量不是特別大或者不是讀取多寫入少的情境，可以考慮WAL模式。

使用事務來避免死結

事務建立的時候會對資料庫檔案加鎖，所以在多線程情況下需要注意及時結束事務，否則會影響到其他動作。儘管SQLite有預防死結的機制（原理是在擷取鎖的時候重試有限次，超過就返回SQLITE_BUSY錯誤），避免程式死掉，但還是會出現下面這種並非我們想看到的現象：

deadLock.png

最終Session A和Session B都失敗了，這個問題可以通過選擇合適的事務類型來避免。

3種事務類型

• DEFERRED
• IMMEDIATE
• EXCLUSIVE
  我們可以通過下面的BEGIN命令來指定：

BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION

DEFERRED是預設類型，事務起始不會擷取任何鎖，從UNLOCKED狀態開始，直到事務需要對資料庫進行讀或者寫的時候才會擷取對應的鎖；IMMEDIATE起始就嘗試擷取RESERVED鎖，保證沒有別的串連可以寫資料庫，但是別的串連可以對資料庫進行讀操作，也就是說它會阻止其它的串連BEGIN IMMEDIATE或者BEGIN EXCLUSIVE；而EXCLUSIVE事務會試著擷取對資料庫的EXCLUSIVE鎖，一旦成功，EXCLUSIVE事務保證沒有其它任何串連，所以就可對資料庫進行讀寫操作了。

例子中，Session A和Session B都需要寫資料庫，如果兩者建立的時候都選擇的是IMMEDIATE事務，那這種失敗的情況就不會發生了。

非常感謝vedon_fu
連結：http://www.jianshu.com/p/9d0a7d3e5001
來源：簡書
著作權歸作者所有。商業轉載請聯絡作者獲得授權，非商業轉載請註明出處。

SQLite 知識摘要 --- 事務