Weizheng Liu Github page

SQLite WAL 文件翻譯

1. 概述

SQLite 實現資料庫原子性(Atomic)的預設模式是使用 rollback journal，在版本 3.7.0(2010-07-21) 時加入了新的模式選項 “Write-Ahead Log” (簡稱WAL)。

使用 WAL 模式替代預設的 rollback journal 有幾個好處：

  1. 在大部分的情況 WAL 有顯著的速度提升。

  2. WAL 的讀寫不會阻擋彼此，提供了更多同步的讀取。

  3. 硬碟的 I/O 指令比較連續。

  4. WAL 使用比較少的 fsync() 指令，減少了 fsync() 失敗可能導致的錯誤。

但還是有一些壞處：

  1. WAL 通常需要 VFS 支援記憶體共享。Unix 與 Windows 的 VFS 都有支援但某些客製的作業系統可能不會有。(例外：不使用共享記憶體的 WAL #8)

  2. 使用資料庫的所有 process 必須在同一台主機上，WAL 不支援網路檔案系統(NFS)

  3. 在使用多個 Attached 資料庫時，每次交易(transaction)對每個資料庫有原子性，但對整體來說沒有。

  4. 在使用 WAL 模式後無法改變 page_size ，不管是空的資料庫、使用 VACUUM、使用 backup API 恢復都不行。必須在 rollback journal 模式才能改變 page size。

  5. ~~唯讀的WAL資料庫無法打開。開啟資料庫的過程必須有對與資料庫關聯的 -shm WAL 索引共享記憶體檔有寫入權限，如果該檔案不存在，則要有該目錄的寫入權限。~~版本 3.22.0(2018-01-22) 之後只要 -shm 與 -wal 檔案皆存在就可以打開唯讀 WAL 資料庫。

  6. 在大量讀取極少寫入的情境下，WAL 模式可能會比使用預設的 rollback-journal 慢一點點(也許慢 1% 或 2%)

  7. 每一個資料庫會有額外的 -wal 檔案與 -shm 共享記憶體檔案，對於使用 SQLite 作為應用程式檔案格式的用途來說變的比較不合適。

  8. 多了叫做 checkpointing 的額外行為，儘管預設是自動的，但是仍是一個開發者需要注意的地方。

  9. ~~WAL 在小的交易(transaction)上表現較好，在交易大於 100MB 時傳統的 rollback journal 可能會快些。在大於 1 GB的交易時，WAL 模式可能會出現 I/O 或是空間不足的錯誤，如果交易大於數MB，建議使用其中一種 rollback journal 。~~ 從版本 3.11.0(2016-02-15) 開始，WAL 模式對於大交易的表現與 rollback journal 無異。

2. 如何運作

傳統的 rollback journal 複製現有資料準備寫入時先把原本的資料複製成一份 rollback journal 檔案，再直接寫入資料庫。如果有發生錯誤或是 Rollback，就會從 rollback journal 檔拿原本的資料來回復到原本的狀態。Rollback journal 檔案會在 Commit 時刪除。

WAL 模式的作法相反，準備寫入時，資料庫不動，先把新的資料寫入 WAL 檔案，最後在 WAL 檔案寫下這次 end mark 的點後 Commit。因此有可能整個過程都沒有動到原本的資料庫，這讓其他的讀取動作在寫入 WAL 檔的時候可以對主要的資料庫同時進行讀取，數個新交易會持續加在 WAL 檔後面。

2.1 Checkpointing

當然，總要有一個時候把 WAL 檔案寫回資料庫，這個動作叫做 Checkpoint。

WAL 模式與 rollback journal 的另一個明顯區別是，在 rollback journal 時，有兩個主要的動作：讀取與寫入。在 WAL 模式時則是三個：讀取、寫入與 checkpointing。

SQLite 在預設的 WAL 模式會在 WAL 檔案到達 1000 頁的時候自動進行 checkpoint。(可以使用 SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 編譯選項來指定不同的預設值)對使用WAL模式資料庫的應用程式層面不需要為 checkpoint 發生時做額外的處理。但如果想要的話，應用程式可以調整自動 checkpoint 的門檻，或是可以關掉自動 checkpoint 自己在空閒時或是另一個 process 手動執行。

2.2 Concurrency

WAL 模式資料庫在進行讀取得時候，會先記得在 WAL 檔案的上一個正確 Commit 位置。叫做 end mark。正當多個讀取連接到資料庫的時候，因為 WAL 有可能也正在被寫入，所以每個讀取可能會有不同的 end mark。但對任何一個讀取來說，end mark 在這次交易都會維持不變這確保了單個讀取交易只會看到它連接當下可以讀取的項目。

當一個讀取需要頁的內容時，他會先檢查 WAL 檔案看看，如果有的話他會把在 end mark 前需要的頁複製一份，然後再去讀原本的資料庫檔案。讀取可以在不同的 processes，為了要避免所有的讀取都去掃整個 WAL 檔案(依照checkpoint設定，WAL 檔案可能會漲到數MB)，在共享記憶體會有一個叫 wal-index 的紀錄來幫助讀取者快速且用到最小的I/O指令來拿到要找的紀錄。wal-index 大幅的增進了讀取的效能，但是使用共享記憶體代表所有的讀者必須存在同一台機器上，這是 WAL 模式不能在網路檔案系統(NFS)使用的原因。

因為讀取不會有干擾寫入的動作，寫入也只需要單純的把新的內容放到 WAL 檔的後面，所以讀取與寫入可以同時進行。但是因為只有一個 WAL 檔案，因此一次同時只能有一個寫入。

在 checkpoint 進行時會把 WAL 檔案的內容放回原本的資料庫檔案。checkpoint 可以跟其他讀取同步進行但是 checkpoint 在碰到任何讀取的 end mark 時必須要停止。如果不停的話可能會覆蓋掉該讀取正在使用的部分。checkpoint 會在 wal-index 註記搬運的進度，並在下一次繼續上次沒完成的進度。

這導致一個非常長的讀取會阻擋 checkpionter 進行，但是正常情況下每個讀取都會結束因此 checkpointer 可以繼續進行。

當一個寫入發生時，會去檢查 checkpointer 的進度，如果整個 WAL 檔案已經被寫進資料庫、兩邊同步 且沒有其他讀取這在使用 WAL 的時候，他會從 WAL 檔案的開頭開始放新的資料，這個機制可以避免 WAL 檔案無止盡的成長。

2.3 Performance Considerations

因為只需要寫入一次(rollback-journal 交易需要兩次)，以及是連續的寫入，讓WAL模式寫入非常的快。再加上，如果想要更加提升效能的話，可以犧牲一些耐用性選擇不讓 WAL 檔案與硬碟同步。(當 PRAGMA synchronous 設定為 FULL，寫入會主動把每一個交易 commit 同步到硬碟，當設定成 NORMAL 則僅會在重要的時候同步)

另外，讀取效能會隨著WAL檔案變大開始降低，因為每個讀取都需要先檢查WAL檔案，檢查需要的時間與檔案大小成正比。wal-index 可以加快這個流程但是還是會受到WAL檔案大小的影響。因此，為了維持好的效能，需要有規律的執行 checkpoint 來降低 WAL 檔案的大小。

Checkpointing 的確需要執行同步來避免因為斷電或是強制重新啟動導致的資料損毀。WAL 必須要先與硬碟同步後才能把 WAL 檔案的資料寫入資料庫，然後資料庫檔案必須被同步後才能把 WAL 檔案重置。Checkpoint 也需要執行更多的查詢。Checkpointer 會試著盡量進行連續性的資料把寫入資料庫，但在寫入的同時依然時常會被查詢中斷。這些情形會讓 checkpoint 比寫入交易還慢。

預設的機制讓成功的交易持續寫入 WAL 檔案直到 WAL 的頁數到達 1000 頁，在後續的每個 COMMIT 會執行 checkpoint 直到 WAL 被重設到小於 1000 頁。預設的情況下， checkpoint 會在每個 COMMIT 後超過上限的同個 thread 自動執行。這會導致雖然大部分的 COMMIT 都非常快，但是遇到有需要執行 checkpoint 的話，那個 COMMIT 會比較慢。如果想要避開這個情形，應用程式可以關閉自動 checkpointing，改成定時在不同的 thread 或 process 執行。(相關指令與執行介面連結在下方)

要注意的是，如果 PRAGMA synchronous 設為 NORMAL 的時候，只會在 checkpoint 時將 WAL 檔案同步到資料庫(Unix 使用fsync(), Windows 使用 FlushFileBuffers())，如果應用程式在另一個 thread 或 process 執行 checkpoint ，主要執行查詢或更新的 thread 或 process 將不會被阻塞，這個對在高附載硬碟執行的程式有避免阻塞鎖死的幫助。但這麼做的代價就是如果斷電或是強制重啟時還沒 checkpoint 前的交易可能會失去。

另外要注意的是可以在平均讀取速度與平均寫入速度中做取捨。如果要加強讀取表現，可以讓 WAL 檔案越小越好，讓 checkpoint 執行的頻繁些，甚至是每個 COMMIT 都執行。如果要最佳化寫入，一個作法是把 checkpoint 分攤到越多寫入越好，這代表要執行 checkpoint 越少次越好，在每次 checkpoint 之前讓 WAL 檔案越大越好。執行 checkpoint 的頻率對於不同的應用程式對於讀寫的需求有不同的最佳設定。預設是在 WAL 檔案到達 1000 頁的時候執行 checkpoint，這個設定似乎在測試的應用程式表現良好，但其他設定可能會在不同平台或是情境更加合適。

3. 啟動與設定 WAL 模式

SQLite 預設使用 journal_mode=DELETE，要轉換為 WAL 模式，使用以下指令：

PRAGMA journal_mode=WAL;

journal_mode 指令後會回傳新的模式名稱，上面指令執行成功時會回傳 “wal” ，如果無法轉換為 WAL (例如，如果使用的 VFS 不支援記憶體共享)，journaling mode 會維持不變並回傳原本的模式名稱(在這個例子是 “delete”)。

3.1 自動 Checkpoint

SQLite 預設設定為每當有 COMMIT 讓 WAL 檔案超過 1000 頁或是最後一個 database 連線結束時會自動執行 checkpoint。預設的設定是為了符合大部分的使用情況設置的，但如果應用程式想要更多的控制，可以使用 wal_checkpoint_pragma 或呼叫 C 介面的 sqlite3_wal_checkpoint() 強制執行一次 checkpoint。改變自動 checkpoint 的門檻或是完全關掉自動 checkpoint 可以使用 wal_autocheckpoint_pragma 或是 C 介面的 sqlite3_wal_autocheckpoint()。另外可以使用 sqlite3_wal_hook() 來註冊一個 callback 在每次交易 commit 到 WAL時會被呼叫，這個 callback 會依照情況再呼叫 sqlite3_wal_checkpoint() 或 sqlite3_wal_checkpoint_v2() 。(自動 checkpoint 機制是簡單的封裝 sqlite3_wal_hook() 實現的。)

3.2 應用程式發起的 Checkpoint

應用程式可以使用任何可寫入的資料庫連線對資料庫發起一個 checkpiont 可以使用 sqlite3_wal_checkpoint() 或是 sqlite3_wal_checkpoint_v2()。有三種層級的 checkpoint 可以選擇：PASSIVE、FULL、RESTART。預設的是 PASSIVE，它會在盡量不干擾其他資料庫連線的前提下盡量執行，如果有其他讀取或寫入可能會讓它不會執行完畢。所有由 sqlite3_wal_checkpoint() 與自動 checkpoint 發起的 checkpoint 皆是 PASSIVE。FULL 與 RESTART checkpoint 會更激進的來讓 checkpoint 完成，且只能使用 sqlite3_wal_checkpoint_v2() 發起。FULL 與 RESTART 的詳細資訊可參考 sqlite3_wal_checkpoint_v2() 文件。

3.3 WAL 模式的持續性

跟其他的 journaling 模式不同，WAL 模式是持續的，當一個 process 設置了 WAL 模式，關閉再打開資料庫，這個資料庫依然會是 WAL 模式。相比之下，如果設定了 TRUNCATE 模式後重開資料庫，資料庫會回到原本的 DELETE 模式而不是維持 TRUNCATE的設定。

WAL 模式的持續性代表可以在不改變應用程式本身的情況下將 SQLite 轉換成 WAL 模式。只要使用 SQLite 的命令工具或其他工具對資料庫執行 PRAGMA journal_mode=WAL; ，再重開應用程式即可。

如果 WAL 模式在其中一個連線被設定，會套用到所有對同個資料庫的連線。

4. WAL 檔案

對一個 WAL 模式的資料庫開啟連線時，SQLite 會使用另一個檔案稱作 “Write Ahead Log” 也稱 WAL 檔案。通常這個檔案的檔名是由資料庫檔案名稱再加上 “-wal”。另外如果 SQLite 使用 SQLITE_ENABLE_8_3_NAMES 會有不同的命名規則。

只要有任何資料庫連線存在，WAL 檔就會保留。通常 WAL 檔案會在最後一個連線關閉時自動刪除。但如果最後一個連線沒有正常關閉或是 SQLITE_FCNTL_PERSIST_WAL file_control 是啟用的，則在所有連線關閉後 WAL 檔案會被保留。

這個 WAL 檔案為資料庫狀態的一部分，在複製或移動資料庫時需要被一起移動。如果資料庫檔案與它的 WAL 檔案分離可能會導致之前的交易資料遺失或是產生有損毀的資料。唯一安全移除 WAL 檔案的方式是使用其中一個 sqlite3_open() 介面，再馬上使用 sqlite3_close() 關閉。

WAL 檔案格式有明確的規範且支援跨平台。

5. 唯讀資料庫

較舊的SQLite版本不能讀取 WAL 模式的唯獨資料庫。換句話說，在讀取 WAL 資料庫時需要寫入權限，這個限制在 SQLite 版本 3.22.0 (2018-01-22)後解除。

在較新版本的 SQlite ，在唯讀裝置或是缺少寫入權限的環境使用 WAL 模式的資料庫時，只要滿足一個或多個以下條件依舊能讀取：

  1. 有既存在且可讀取的 “-shm” 與 “-wal” 檔案

  2. 對於 “-shm” 與 “-wal” 檔案 有寫入權限或對資料夾有寫入權限可以建立 “-shm” 與 “-wal” 檔案。

  3. 資料庫連線使用 immutable_query_parameter 開啟。

儘管開啟一個唯獨 WAL 模式資料庫是可行的。但最好的方式是轉換成 PRAGMA journal_mode=DELETE 再燒錄到唯獨的裝置上。

6. 避免過大的 WAL 檔案

正常的情況來說，加入新的檔案直到 WAL 檔案到達 1000 頁時(約 4MB)自動 checkpoint 會自動執行，讓 WAL 檔案得以重複使用。Checkpoint 預設並不會減少 WAL 檔案的大小，他會讓 SQLite從開頭開始複寫 WAL 檔案，這個決定是因為正常情況複寫會比擴增檔案還快。當最後一個連線關閉時，會執行最後一次 checkpoint 然後刪除 WAL 檔案與相關連的共享記憶體檔案來清出空間。

在大多數的情況，應用程式完全不需要擔心 WAL 檔案，SQLite 會自動處理。但仍然有些原因可能造成 WAL 檔案無限制的成長，影響硬碟使用空間與減慢查詢速度。以下幾列出幾個可能原因與解決方式：

  • 自動 checkpoint 機制被關閉。SQLite 預設會在造成超過 1000 頁的交易啟動自動 checkpoint 。但有些編譯或是執行選項可以關閉或改變自動 checkpoint。如果自動 checkpoint 被關閉，將沒有辦法避免 WAL 檔案的持續增長。
  • Checkpoint starvation。Checkpoint 只能在沒有其他連線同時在使用 WAL 檔案時才能完全地走完 checkpoint 並重設 WAL 檔案。如果有其他讀取正在進行，checkpoint 不能重置 WAL 檔案，因為可能會刪除讀取正在使用的項目。這是 checkpoint 會在不引響其他讀取的情況下盡量進行，但不會走完全部流程。在下一個交易時，checkpoint 會再度嘗試處理上次剩下的部分，直到 checkpoint 流程完畢。

但是如果一個資料庫同時有太多不間斷的讀取，讓資料庫處於永遠至少都有一個連線在讀取的情況，會導致 checkpoint 永遠無法完成，讓 WAL 檔案沒有限制的成長。

這個情形可以藉由確保有 “reader gaps”來解決(確保有沒有讀取可讓 checkpoint 執行的間隔)。在這種有大量同時讀取的應用程式可以考慮執行手動 checkpoint (SQLITE_CHECKPOINT_RESTART 或 SQLITE_CHECKPOINT_TRUNCATE 完成前會確保 checkpoint 執行完畢)，手動執行的缺點是當 checkpoint 進行時讀取可能會被阻擋。

  • 超大的寫入交易。checkpoint 只能在沒有其他交易進行時完成，代表 WAL 檔案在交易時不能被重置，因此大型的交易可能會導致較大的 WAL 檔案。沒有其他讀取阻擋的話這個大的 checkpoint 會在會在交易完成後執行 checkpoint 清除，但在完成之前，這個檔案可以擴張的很大。

在 SQLite 版本 3.11.0 (2016-02-15) 後，單個交易的資料量會與交易大小成正比，交易中的頁數只會被寫入 WAL 檔案中一次。但是在舊的版本中，如果交易大於頁面暫存，相同的頁數可能會多次的被寫入 WAL 檔案。

7. 共享記憶體中的 WAL-Index 實現

wal-index 使用 mmap 過的檔案來實現，早期的版本(非正式版)的 WAL 模式將 wal-index 存在 volatile shard-memory(Linux 為建立在 /dev/shm 其他 Unix 系統則是 /tmp )，這個做法的問題是在有不同根目錄的 process 們會看到不同的檔案因此使用不同的記憶體位置，導致資料損毀。我們找不到通用於各個 Unix 環境中建立不具名共享記憶體區域的方式，也找不到在 Windows 上建立該區塊的方法。唯一可以確保所有讀取資料庫的 processes 都能使用相同共享記憶體的做法是在與資料庫相同的資料夾建立檔案再使用 mmap (memory mapping file) 建立共享記憶體。

使用檔案來提供共享記憶體的壞處是在將記憶體寫回檔案會造成不必要的 I/O 動作。但 wal-index 很少超過32Kb而且不需要同步到硬碟，所以開發者不需要太過擔心。而且在最後一次連線後 wal-index 檔案會被刪除，這消除了任何真正同步到硬碟的 I/O 機會。

應用程式在預設分享記憶體無法使用的環境可以使用自訂的 VFS，例如如果確定這個資料庫只會被同個 process 的 threads 取用的話，wal-index 可以使用 heap 記憶體取代共享的記憶體。

8. 不使用共享記憶體的 WAL 模式

從 SQLite 版本 3.7.4 (2010-12-07) 開始，就算在沒有共享記憶體的情況下，只要存取前將 locking_mode 被設為 EXCLUSIVE， WAL 資料庫就可以被建立、讀取、寫入。換句話說，如果確定只會有一個 process 會使用的話，可以在不需要共享記憶體的情況下操作 WAL 資料庫。這個功能允許使用舊版 VFS(虛擬文件系統)來建立、讀取和寫入 WAL 資料庫，即使這些 VFS 缺少在 sqlite3_io_methods 物件上的第二代共享記憶體方法： xShmMap、xShmLock、xShmBarrier 和 xShmUnmap。

如果 EXCLUSIVE locking mode 再開始存取前就被設定好的話，SQLite 則不會呼叫任何使用共享記憶體的方法，因此不會建立 wal-index。在這個情況下，只要資料庫還是 WAL 模式，資料庫連線都會維持在 EXCLUSIVE 模式。這時嘗試使用 PRAGMA locking_mode=NORMAL; 會失效。唯一離開 EXCLUSIVE locking 模式的方法是先離開 WAL 模式。

如果 NORMAL locking mode 再開始使用前被設定，就會產生共享記憶體的 wal-index，這代表環境的 VFS 必須要支援 第二代記憶體共享。如果 VFS 沒有支援，在開啟已經在 WAL 模式的資料庫，或是把資料庫切換到 WAL 模式都會失敗。只要在只有一個連線在使用共享記憶體的 wal-index 時，就可以在 NORMAL 與 EXCLUSIVE 模式間自由切換。只有在第一次 WAL-mode 資料庫存取之前locking mode 為 EXCLUSIVE，且共享記憶體的 wal-index 被省略時，locking mode 才會被固定為 EXCLUSIVE。

9. 有時候在 WAL 模式查詢回傳 SQLITE_BUSY

WAL 模式的其中一項優點是寫入與讀取不會互相阻擋，在大部分的情況沒錯，但在有一些極端的例子下 WAL 資料庫會回傳 SQLITE_BUSY，因此應用程式應該要有針對這項回應的應對。

在 WAL 模式造成回傳 SQLITE_BUSY 的原因如下：

  • 當另一個資料庫連線將 exclusive locking 模式，那所有的存取都會回傳 SQLITE_BUSY。舉例來說，Chrome 與 Firefox 都使用 exclusive locking 模式，因此在這兩個應用程式正在執行時試圖存取資料庫會出錯。
  • 在最後一個連線關閉時，連線會取得端時間的 exclusive lock 來清除 WAL 與共享記憶體檔案。如果這時候有另一個連線試圖在清除的過程中存取資料庫，則會得到 SQLITE_BUSY 錯誤。
  • 當最後一個連線結束的過程中崩潰，第一個新連線連接時將會開始進行回復。這個過程中會掛上一個 exclusive lock，如果有其他連線在這個回復過程試圖存取會得到 SQLITE_BUSY。

10. 向後相容性

在 WAL 模式中，資料庫的格式不會更改。但因為 WAL 檔案與 wal-index 是新的機制，所以舊版的 SQLite 會不知道如何從崩潰後回復。為了避免舊版本的SQLite(3.7.0之前 2010-07-22) 試圖錯誤的回復 WAL 模式資料庫，在 WAL 模式的的資料庫檔案格式版本(資料庫 header 的第18與19位元)從1增加到2。因此如果有舊的SQLite 版本試圖連接 WAL 模式的資料庫時會回報錯誤：”file is encrypted or is not a database”。

可以執行一下指令退出 WAL 模式

PRAGMA journal_mode=DELETE;

正確的從WAL 模式離開會將資料庫檔案規格版本改回 1 ，讓舊版的 SQLite 版本也可以再度正確的讀取。