SQLite 學習筆記之三 - 交易

一、ACID 概念

本節摘錄自：http://zh.wikipedia.org/wiki/ACID

• Atomic（原子性）
  一個交易（transaction）中的所有操作，要嘛全部完成，要嘛全部不完成，不會結束在中間某個環節。交易在執行過程中發生錯誤，會被回滾（Rollback）到交易開始前的狀態，就像這個交易從來沒有執行過一樣。
• Consistency（一致性）
  在交易開始之前和交易結束以後，資料庫的一致性限制沒有被破壞。
• Isolation（隔離性）
  當兩個或者多個交易並發訪問（此處訪問指查詢和修改的操作）資料庫的同一數據時所表現出的相互關係。交易隔離分為不同級別，包括讀未提交（Read uncommitted）、讀已提交（Read committed）、可重複讀（Repeatable read）和串列化（Serializable）。
• Durability（持久性）
  在交易完成以後，該交易對資料庫所作的更改便持久地保存在資料庫之中，並且是完全的。

目前主要有兩種方式實現 ACID：第一種是「Write-Ahead logging」，也就是日誌式的方式。第二種是「Shadow paging (Rollback journal)」。

二、Auto Commit

1. 交易（TRANSACTION）的目的是要確保 RDBMS 的 ACID。
2. SQLite 的「auto commit」預設是打開的。
1. 也就是說，每一個 SELECT/INSERT/UPDATE/DELETE 指令，都被 SQLite 視為單獨的一個 TRANSACTION。
2. 因此，如果你的程式裡要執行大量 INSERT/UPDATE/DELETE 指令，由於「交易開啟 + SQL 執行 + 交易關閉」的關係，開啟 auto commit 將會使程式變得非常緩慢！
3. 故此時可考慮將 auto commit 暫時關閉，待程式執行完畢再打開（程式的實際作法：C# 請參考這篇，Java 請參考這篇）。 但在執行過程中，若又有其他 INSERT/UPDATE/DELETE  指令進來，則可能遇到 Lock 問題。
3. 在 shell 模式下，若要手動關閉 auto commit，只要在每一個 TRANSATION 開始前先執行「BEGIN」即可，若要再度打開 auto commit，則只要執行「COMMIT」或「ROLLBACK」即可（請參考 SQLite 官網說明）。

三、交易隔離層級（Transaction Isolation Level）

各隔離層級對多個交易並行處理的影響，「YES」表示可能會發生問題：

Isolation level	Dirty reads	Non-repeatable reads	Phantom reads
Read Uncommitted	YES	YES	YES
Read Committed	-	YES	YES
Repeatable Read	-	-	YES
Serializable	-	-	-

Isolation Levels vs Locks：

Isolation level	Write lock	Read lock	Range lock
Read Uncommitted	-	-	-
Read Committed	X	S	-
Repeatable Read	X	X	-
Serializable	X	X	X

• X：即「排它鎖」（exclusive lock) ，意指某方法在執行某項操作前進行鎖定，並保持該 lock 直到包含該操作的交易結束為止。
• S：即「共享鎖」（shared locks），若某筆紀錄已被其他擁有「排它鎖」的交易改變了，但尚未提交（commit），則無法取得此類鎖；必須一直等到其他交易透過 commit 或 rollback 指令將 lock 釋放掉以後，才可進行讀取操作。讀取操作（如 SELECT） 在「Read Committed」層級可獲得 read（shared）lock ，但是在讀取完畢以後就會立即釋放。

但是 SQLite 只支援兩種交易隔離層級：「serializable」和「read uncommitted」。SQLite 允許開發者針對個別 DB connection 指定不同隔離層級。

項目	serializable	read uncommited
定義	SQLite 的預設值，這是最高（嚴格）的隔離層級，只要有資料不一致的疑慮，交易就會循序進行，也就是一個一個來，這是最安全、但效能也最差的隔離層級。 基本作法是：A交易讀取時，B交易若要更新，就必須循序，A交易更新時，B交易無論讀取或更新都必須循序執行。 可避免：更新遺失（lost update）、髒讀（dirty read）、無法重複讀取（unrepeatable read）、幻讀（phantom read）問題。	這是最低的交易隔離層級，此隔離層級至少保證：「A交易已更新但尚未確認的資料，B交易僅可作讀取動作」。這是最容易發生讀取資料不一致或錯誤、但效能最好的隔離層級。一般資料庫引擎不會採用這種隔離層級。 基本作法是：A交易在更新但未提交，B交易的更新會被延後至A提交之後。 可避免「更新遺失」的問題，但無法避免「髒讀」、「無法重複讀取」以及「幻讀」問題。
指令	PRAGMA read_uncommitted=0;	PRAGMA read_uncommitted=1;
差異	在此模式下，process 或 thread 針對某 table 的讀取、寫入操作均會要求一個 lock。但如果是使用 shared cache 的 DB 連線，SQLite 仍會使用「read-uncommited」層級。	在此模式下，process 或 thread 在對某 table 進行「讀取」操作時並不會要求一個 read-lock。若此時正有其他的connection 也正在修改資料，就有可能會發生讀取結果不一致的現象。
適用	適用於平行處理效能要求較低、記憶體資源較多、且寫入操作較多的狀況。	適用於平行處理效能要求較高、記憶體資源較少、且寫入操作較少的狀況。

 
四、交易模型（Transaction Model）

日誌（journal）的主要目的是為了要讓 SQLite 可以支援「交易」 (TRANSACTION)，當交易失敗或中斷時可用此檔案來還原。

而 SQLite 對於日誌的處理有兩種作法：

1. 回滾日誌檔 (Rollback Journal)：
• 先將原內容則備份至回滾日誌檔中，再將要異動的內容直接寫入 DB。當需要 rollback 時，再將原內容由日誌檔回寫 DB；若要 commit 變更時，則只要將該檔案刪除即可。
• 細部操作：reading、writing。
• 又可細分為 4 種 journal modes：DELETE (SQLite 預設值)、TRUNCATE、PERSIST、MEMORY（可透過「PRAGMA journal_mode」指令來設定）  
• 流程動畫：Initial State -> Backup Page 3 to Rollback Journal -> Change to Page 3
   
   
2. WAL (Write-Ahead Log)： 
• 作法與回滾日誌檔剛好相反。原內容仍保留在原 DB 之中，但新的異動則 append 至 WAL 檔。而當 COMMIT 發生時，僅代表某筆記錄已 append 進 WAL 檔了，但並不一定有寫入原 DB。如此可讓其他資料庫連結繼續對原 DB 內容進行讀取操作，而其他連結也可同時將異動 COMMIT 進 WAL 檔。
• 細部操作：reading、writing、checkpoint。
• 僅有一種 journal mode：WAL （可透過「PRAGMA journal_mode」指令來設定） 。
• 流程動畫：Initial State -> Change to Page 3 -> Checkpoint
   

小結如下：

1. 自 3.7.0 版以後，SQLite 開始支援 WAL (Write-Ahead Log)，但預設值仍是 rollback journal 的「DELETE」。
2. 對某 DB 設定「PRAGMA journal_mode=WAL」的效果是持續的：
• 亦即只要曾對某 DB 設定過此模式，則下次再使用該 DB 時，就會自動進入此模式（不同於 rollback journal 的所有模式，若先前是設定為TRUNCATE，則下次使用時便會恢復成預設的 DELETE）。
• 只要有一個 DB connection 將某 DB 設定為此模式，則之後所有存取該DB 的 connections 也都會變更為此模式。
3. 不可在唯讀的儲存媒體或磁區使用 DELETE、TRUNCATE、PERSIST、WAL 這些 journal mode。
4. 使用 WAL，在「寫入」操作上約有 4~8 倍的速度提升，在「讀取」操作上則會比 rollback journal 慢約 1~2%。因此，如果是 read 操作遠多於 write 操作，建議使用 rollback journal 即可。如果 write 操作多於 read 操作，且對 concurrency 的效能要求比較高，則建議採用「WAL」。
5. WAL DB 使用期間都會有兩個臨時檔案：「-wal」和「-shm」。
6. 進入 WAL 模式以後就無法再改變 DB 分頁大小了，除非先執行 VACUUM，然後再進入 rollback journal 模式才行。
7. WAL 適用於小型的交易。大於 100MB 的交易，rollback journal 會比 WAL 快。大於 1 GB 的交易，WAL 可能會發生 I/O 或 disk-full 錯誤。大於數十 MB 的交易，建議使用 rollback journal（DELETE、TRUNCATE、PERSIST 或 MEMORY）。
8. WAL 使用「wal-index」來提升讀取效能，但因為「wal-index」儲存在 shared-memory，所以讀取該資料庫的 process 必須都在同一台機器之中。

五、日誌模式（Journal mode）

• PRAGMA journal_mode;
• PRAGMA database.journal_mode;
• PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
• PRAGMA database.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
• 本指令可用來查詢或設定目前 DB connection 的日誌模式，詳細說明請參考「SQLite官網說明」。

模式	說明	寫入速度
DELETE	每次交易結束時，回滾日誌檔（rollback journal）就會被刪除，刪除檔案的動作會使交易提交（commit）。※ 注意：此模式是 SQLite 的預設模式。	第 2 名
TRUNCATE	只會將回滾日誌檔內容清空（檔案大小為 0），而不是刪除它。在大部分的 OS 上，這會比 DELETE 模式速度快（因為不用刪除檔，所以所屬的目錄內容也不需要改變）。	第 6名
PERSIST	每次交易結束時，並不刪除回滾日誌檔，而只是在檔頭填充 0，這如此會阻止其他的資料庫連結使用此檔進行 rollback。此模式在某些平臺上是一種最佳解，特別是刪除（DELETE）或清空日誌檔（TRUNCATE）比以「0」覆寫檔案第一區塊代價高的時候。	第 5名
MEMORY	只將回滾日誌檔案儲存到記憶體中，節省了DISK I/O，但帶來的代價是穩定性和完整性上的損失。但如果交易過程 crash 掉了，資料庫就極有可能損壞。	第 3 名
WAL	也就是以「Write-Ahead Log」來取代「回滾日誌檔」（rollback journal）。此模式是持久化的，也就是在多個資料庫連結都會生效，且重新打開資料庫以後，仍然有效。該模式只在 3.7.0 以後才有效（過程會生成兩個檔：.shm 和 .wal）。	第 4 名
OFF	關閉日誌模式，但這樣就不能使用交易了。所以不可以使用 ROLLBACK 指令，而且如果 AP 突然 crash 掉，資料庫便極有可能毀損。	第 1 名

1. 在交易執行期間，是無法變更 journal mode 的。
2. 如果使用 In-Memory DB，則 journal mode 只有「MEMORY」和「OFF」兩種可以選擇。
3. DELETE、TRUNCATE、PERSIST、MEMORY、OFF 模式的「page size」和「auto-vacuum 模式」均可改變（改變前須先執行「VACUUM」指令）；但 WAL 模式 DB 的「page size」無法改變，只有「auto-vacuum 模式」 可以改變。
4. 因為 SQLite 在將寫入 DB 之前會先將欲變更的內容寫入記憶體，所以不建議把 DB 檔案放在 NAS 上面、並讓多台主機上的 AP 時寫入，除非該 DB 是唯讀的、而且不是WAL（因為 wal-index 是儲存在 shared-memory 之中）。

以下是日誌模式（Journal mode）的實際評測（benchmarks）：

測試方式：

• Autocommit=off
• locking_mode=EXCLUSIVE
• 測試用 SQL：
  DROP TABLE IF EXISTS MTTEST
  CREATE TABLE MTTEST (SEQ INTEGER, VAL INTEGER, CREATED TEXT)
  INSERT INTO MTTEST VALUES(?, ?, ?)
• 以上步驟重複跑 5 回合，且只用 1 個 thread

 測試環境：

環境	說明
OS	Windows 7 Pro 64-bit Service Pack1
SQLite	3.7.15
JDK	1.6.0_30
JDBC	sqlite-jdbc-3.7.15-SNAPSHOT.jar
CPU	Intel Core i7-2620M CPU @2.70GHz
RAM	8 GB
H.D.D.	Seagate ST320LT000-9VL142 320GB (2.5”, 7,200 rpm)

六、日誌檔案大小限制

 

（journal_size_limit）

• PRAGMA journal_size_limit;
• PRAGMA journal_size_limit = N ;
• 本指令可用來查詢或設定回滾日誌檔（rollback journal）或 WAL 檔的大小，詳細說明請參考「SQLite官網說明」。

1. 該設置會限制日誌檔的大小。預設值是 -1（無限制）。
2. 如果連接 SQLite 時，使用了「EXCLUSIVE」鎖定模式（PRAGMA locking_mode=EXCLUSIVE）或「PERSIST」日誌模式（PRAGMA journal_mode=PERSIST），在提交交易後，日誌檔仍然會留在檔案系統上，這會提升效能，但也會佔用磁碟空間。此外，一個大型的交易指令執行時，如  VACUUM，也會使用到大量的磁碟空間。
3. 同樣地，在「WAL 模式」（PRAGMA journal_mode=WAL），WAL 檔沒有被 checkpoint 縮小之前，由於 overwrite 比 append 快，SQLite 也會一直重複使用既有的檔案。

七、鎖定模式（locking mode）

• PRAGMA locking_mode;
• PRAGMA database.locking_mode;
• PRAGMA locking_mode = NORMAL | EXCLUSIVE;
• PRAGMA database.locking_mode  = NORMAL | EXCLUSIVE;
• 本指令可用來查詢或設定 DB connection 的鎖定模式，詳細請參考「SQLite官網說明」。

環境	說明
NORMAL	DB connection 在每次交易結束後，就馬上對 DB 檔案解鎖（※ 注意：這是 SQLite 的預設值）。
EXCLUSIVE	DB connection 存續期間，永遠不會對 DB 檔案解鎖。而且僅有取得EXCLUSIVE lock 的 DB connection 才可以對該 DB 進行寫入。 第一次讀取發生時，馬上就會取得一個 share lock。 第一次寫入發生時，馬上就會取得一個 exclusive lock。 若要使處於此模式的 DB connection 釋放 EXCLUSIVE lock，(1) 必須將 DB connection 關閉；(2) 或者使用「PRAGMA locking_mode=NORMAL」指令，將鎖定模式改回 NORMAL，然後再重新存取一次即可（讀或寫）。 通常基於下列三種原因，才需要將 locking_mode 設為 EXCLUSIVE： AP希望避免其他 process 同時存取該 DB 檔案。 減少檔案系統操作的 system calls，或許能增加些許效能。 在不使用到 shared memory 的狀況下，WAL DB 仍然可以在 EXCLUSIVE 模式存取。 temp 與 in-memory DB 都只能使用此鎖定模式，而且不能改變。

就單一進程（process）的觀點來看，一個資料庫檔案可能處於下列 5 種鎖定狀態：

環境	說明
UNLOCKED	代表該 process 尚未對該 DB 取得任何的 lock，而此時該 DB 也尚未被讀取或寫入。任何內部快取資料在被使用前，都需要先通過驗證。其他程序是否可以讀寫該 DB 則視他們自己的鎖定狀態是否允許而定（※ 注意：這是 SQLite 的預設值）。
SHARED	代表該 process 可以讀取該 DB，但不能寫入該 DB。多個 process 可在同一時間取得 SHARED lock，所以多個 process 可以同時讀取同一個 DB。只要有一個 SHARED lock 存在，則此時任何其他  thread 或 process 都不能夠寫入DB。
RESERVED	代表該 process 計畫在未來的某個時間點寫入DB，但它目前正在從該 DB 讀取資料。同一時間內，可以有多個 SHARED locks，但是只能有一個 RESERVED lock。與 PENDING lock 不同的是，RESERVED lock 允許新的 SHARED lock 產生；但是 PENDING 不允許。
PENDING	代表該 process 希望盡快取得 EXCLUSIVE lock 以寫入 DB，但由於目前仍有其他 SHARED locks 未結束，所以正處於等待狀態中。只要目前該 DB 存在一個 PENDING lock，則 SQLite 就不會再允許其他 process 再對此 DB 取得任何新的 SHARED lock。
EXCLUSVIE	任何 process 想寫入 DB 就必須先取得 EXCLUSIVE lock。而同一時間內，對於某一 DB 檔案來說，也只能存在一個 EXCLUSIVE lock（其他任何類型的 lock 都不允許與 EXCLUSIVE lock 共存）。為了使 concurrency 效能最大化，SQLite 盡可能縮短 EXCLUSIVE lock 存在的時間。

八、 交易模式（Transaction Mode）

用途：宣告取得 Lock 的時機。

 

模式	說明
DEFERRED	SQLite 的預設值。代表在開始存取（讀寫） DB 以前，並不會要求任何的 lock。如果第一次存取動作是讀取（read），則會取得 SHARE lock；如果是寫入（write），則會取得 RESERVED lock。在這種模式下，真正需要用到 lock 以前，lock 都是會被延遲的，所以在 BEGIN 之後，可能又會有其他的 thread 或 process 對同一 DB 再建立了其他的交易。 ※ 此時其他 connection 仍可讀寫。
IMMEDIATE	代表該 DB connection 執行 BEGIN 指令後，隨即就會向所有 DB 要求 RESERVED lock，而不會等待到開始讀寫時才要求。而且除了該 connection 之外，其他的 connection 都不可再寫入 DB，而且也不能再執行「BEGIN IMMEDIATE」或「BEGIN EXCLUSIVE」，但是其他的 process 仍可繼續讀取該 DB。 ※ 此時其他 connection 可讀、但不能寫。
EXCLUSIVE	代表該 DB connection 執行  BEGIN 指令以後，隨即就會向所有 DB 要求 EXCLUSIVE lock。而且此時除了 read uncommitted 的 DB connection 還能讀取 DB 之外，其他的 connection 在交易未結束前都不能寫入該 DB。 ※ 此時只有 read uncommitted  的 connection 可讀，其餘均無法讀寫。

九、 同步模式（Synchronous）

模式	說明
0 或 OFF	如果採用此模式 SQLite 一旦將資料交給 OS 以後就會停止同步。如果使用 SQLite 的應用程式 crash，資料仍然會是安全的。但如果在資料真正寫入磁碟以前，發生了 OS crash 或電腦失去電力，那麼 DB 可能就會毀損。另一方面，在此模式中，某些操作會變快 50 倍或更多。
1 或 NORMAL	當採用此選項時，DB 會在比較關鍵的時刻進行同步（sync），但是頻率會少於FULL。在此模式下，因為電力發生中斷造成 DB 毀損的機率會比較小（但不為 0）。但事實上，您比較可能遇到磁碟錯或其他無法復原的硬體錯誤。
2 或 FULL	SQLite 在進行任一步驟之前，都會使用 VFS 的 xSync() 來確保所有內容都已經安全寫入磁碟之中。此選項會確保 OS 混損或者電力中斷時，不會造成資料庫毀損。此模式非常安全，但是也最慢。（※ 注意：這是 SQLite 的預設值）

1. 在 WAL 模式中，若同步模式為 NORMAL(1)：
1. WAL 檔案在每次 checkpoint 之前會做一次同步。
2. DB 檔案會在每次完整 checkpoint 之後做一次同步。
3. WAL 檔案的檔頭會在 checkpoint 之後、WAL 檔案開始被重複使用前，做一次同步。
4. 但是在大部分的交易（transaction）期間，都不會發生同步操作。
2. 在 WAL 模式中，若同步模式為 FULL(2)：
1. WAL 檔案會多做一次同步操作，亦即在每一次交易提交的時候。
2. 這多出來的一次同步操作主要目的是：在遇到電力中斷時，仍能確保交易的「持久性」（durable），並非「一致性」（consistency）。
3. 如果不想考慮「持久性」，在 WAL 模式中，使用「synchronous=NORMAL」就可以了。

十、 共享快取模式（Shared Cache Mode）

1. 預設是關閉的，可使用「ATTACH 'file:aux.db?cache=shared' AS aux;」指令開啟此模式（per-process basis）。
2. 在一個 process 或 thread 之中，可使用此模式讓同一 DB 的多個連線共享相同的 page cache，以減少 I/O 與記憶體用量，適用於 embedded server 或對平行處理能力（concurrency）要求較高的狀況。
3. 在 shared cache 模式的資料庫連線，可以有多個讀取程序，但僅能有一個寫入程序（EXCLUSIVE 模式）。
4. 此時，若要對某 table 進行讀取或寫入操作，仍須先取得 read 或 write 的 lock。
5. 承上，但若在此模式下使用「read_uncommitted」隔離層級，則對於 table 進行「讀取」操作前，不需先取得 read lock（但此時可能會發生 dirty read 狀況）。
6. SQLite 3.5 版以後，相同的 page cache 已可在整個 process 之間共用（而非僅有單一的 thread 而已），而且此類資料庫連線也可在不同的 threads 之間傳遞與共用 cache。
7. 在 SQLite 3.6.17 版以前，shared cache mode 不可與「virtual table」共用。
8. 自 SQLite 3.7.13 版以後，「In-Memory DB」也可使用 shared cache mode 了。