sqlite3 多線程和鎖 ，最佳化插入速度及效能最佳化

標籤：

一、 是否支援多線程？ SQLite官網上的“Is SQLite threadsafe?”這個問答。 簡單來說，從3.3.1版本開始，它就是安全執行緒的了。而iOS的SQLite版本沒有低於這個版本的，當然，你也可以自己編譯最新版本。
不過這個安全執行緒仍然是有限制的，在這篇《Is SQLite thread-safe?》裡有詳細的解釋。
另一篇重要的文檔就是《SQLite And Multiple Threads》。它指出SQLite支援3種線程模式：

1. 單線程：禁用所有的mutex鎖，並發使用時會出錯。當SQLite編譯時間加了SQLITE_THREADSAFE=0參數，或者在初始化SQLite前調用sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)時啟用。
2. 多線程：只要一個資料庫連接不被多個線程同時使用就是安全的。源碼中是啟用bCoreMutex，禁用bFullMutex。實際上就是禁用資料庫連接和prepared statement（準備好的語句）上的鎖，因此不能在多個線程中並發使用同一個資料庫連接或prepared statement。當SQLite編譯時間加了SQLITE_THREADSAFE=2參數時預設啟用。若SQLITE_THREADSAFE不為0，可以在初始化SQLite前，調用sqlite3_config(SQLITE_CONFIG_MULTITHREAD)啟用；或者在建立資料庫連接時，設定SQLITE_OPEN_NOMUTEX flag。
3. 串列：啟用所有的鎖，包括bCoreMutex和bFullMutex。因為資料庫連接和prepared statement都已加鎖，所以多線程使用這些對象時沒法並發，也就變成串列了。當SQLite編譯時間加了SQLITE_THREADSAFE=1參數時預設啟用。若SQLITE_THREADSAFE不為0，可以在初始化SQLite前，調用sqlite3_config(SQLITE_CONFIG_SERIALIZED)啟用；或者在建立資料庫連接時，設定SQLITE_OPEN_FULLMUTEX flag。

　　而這裡所說的初始化是指調用sqlite3_initialize()函數，這個函數在調用sqlite3_open()時會自動調用，且只有第一次調用是有效。
　　另一個要說明的是prepared statement，它是由資料庫連接（的pager）來管理的，使用它也可看成使用這個資料庫連接。因此在多線程模式下，並發對同一個資料庫連接調用sqlite3_prepare_v2()來建立prepared statement，或者對同一個資料庫連接的任何prepared statement並發調用sqlite3_bind_*()和sqlite3_step()等函數都會出錯（在iOS上，該線程會出現EXC_BAD_ACCESS而中止）。這種錯誤無關讀寫，就是唯讀也會出錯。文檔中給出的安全使用規則是：沒有事務正在等待執行，所有prepared statement都被finalized。
　　順帶一提，調用sqlite3_threadsafe()可以獲得編譯期的SQLITE_THREADSAFE參數。標準發行版是1，也就是串列模式；而iOS上是2，也就是多線程模式；Python的sqlite3模組也預設使用串列模式，可以用sqlite3.threadsafety來配置。　　但是預設情況下，一個線程只能使用當前線程開啟的資料庫連接，除非在串連時設定了check_same_thread=False參數。如果是用不同的資料庫連接，每個串連都不能讀取其他串連中未提交的資料，除非使用read-uncommitted模式。

現在3種模式都有所瞭解了，清楚SQLite並不是對多線程無能為力後，接下來就瞭解下事務吧。   二、事務
　　資料庫只有在事務中才能被更改。所有更改資料庫的命令（除SELECT以外的所有SQL命令）都會自動開啟一個新事務，並且當最後一個查詢完成時自動認可。
　　而BEGIN命令可以手動開始事務，並關閉自動認可。當下一條COMMIT命令執行時，自動認可再次開啟，事務中所做的更改也被寫入資料庫。當COMMIT失敗時，自動認可仍然關閉，以便讓使用者嘗試再次提交。若執行的是ROLLBACK命令，則也開啟自動認可，但不儲存事務中的更改。關閉資料庫或遇到錯誤時，也會自動復原事務。
　　　　經常有人抱怨SQLite的插入太慢，實際上它可以做到每秒插入幾萬次，但是每秒只能提交幾十次事務。因此在插入大批資料時，可以通過禁用自動認可來提速。　　還有一個很重要的知識點需要強調：事務是和資料庫連接相關的，每個資料庫連接（使用pager來）維護自己的事務，且同時只能有一個事務（但是可以用SAVEPOINT來實現內嵌事務）。也就是說，事務與線程無關，一個線程裡可以同時用多個資料庫連接來完成多個事務，而多個線程也可以同時（非並發）使用一個資料庫連接來共同完成一個事務。 而要實現事務，就不得不用到鎖。
一個SQLite資料庫檔案有5種鎖的狀態：

• UNLOCKED：表示資料庫此時並未被讀寫。
• SHARED：表示資料庫可以被讀取。SHARED鎖可以同時被多個線程擁有。一旦某個線程持有SHARED鎖，就沒有任何線程可以進行寫操作。
• RESERVED：表示準備寫入資料庫。RESERVED鎖最多隻能被一個線程擁有，此後它可以進入PENDING狀態。
• PENDING：表示即將寫入資料庫，正在等待其他讀線程釋放SHARED鎖。一旦某個線程持有PENDING鎖，其他線程就不能擷取SHARED鎖。這樣一來，只要等所有讀線程完成，釋放SHARED鎖後，它就可以進入EXCLUSIVE狀態了。
• EXCLUSIVE：表示它可以寫入資料庫了。進入這個狀態後，其他任何線程都不能訪問資料庫檔案。因此為了並發性，它的持有時間越短越好。

一個線程只有在擁有低層級的鎖的時候，才能擷取更高一級的鎖。SQLite就是靠這5種類型的鎖，巧妙地實現了讀寫線程的互斥。同時也可看出，寫操作必須進入EXCLUSIVE狀態，此時並發數被降到1，這也是SQLite被認為並發插入效能不好的原因。
另外，read-uncommitted和WAL模式會影響這個鎖的機制。在這2種模式下，讀線程不會被寫線程阻塞，即使寫線程持有PENDING或EXCLUSIVE鎖。

提到鎖就不得不說到死結的問題，而SQLite也可能出現死結。
下面舉個例子：

串連1：BEGIN （UNLOCKED）
串連1：SELECT ... （SHARED）
串連1：INSERT ... （RESERVED）
串連2：BEGIN （UNLOCKED）
串連2：SELECT ... （SHARED）
串連1：COMMIT （PENDING，嘗試擷取EXCLUSIVE鎖，但還有SHARED鎖未釋放，返回SQLITE_BUSY）
串連2：INSERT ... （嘗試擷取RESERVED鎖，但已有PENDING鎖未釋放，返回SQLITE_BUSY）

現在2個串連都在等待對方釋放鎖，於是就死結了。當然，實際情況並沒那麼糟糕，任何一方選擇不繼續等待，復原事務就行了。

不過要更好地解決這個問題，就必須更深入地瞭解事務了。
實際上BEGIN語句可以有3種起始狀態：

• DEFERRED：預設值，開始事務時不擷取任何鎖。進行第一次讀操作時擷取SHARED鎖，進行第一次寫操作時擷取RESERVED鎖。
• IMMEDIATE：開始事務時擷取RESERVED鎖。
• EXCLUSIVE：開始事務時擷取EXCLUSIVE鎖。

現在考慮2個事務在開始時都使用IMMEDIATE方式：

串連1：BEGIN IMMEDIATE （RESERVED）
串連1：SELECT ... （RESERVED）
串連1：INSERT ... （RESERVED）
串連2：BEGIN IMMEDIATE （嘗試擷取RESERVED鎖，但已有RESERVED鎖未釋放，因此事務開始失敗，返回SQLITE_BUSY，等待使用者重試）
串連1：COMMIT （EXCLUSIVE，寫入完成後釋放）
串連2：BEGIN IMMEDIATE （RESERVED）
串連2：SELECT ... （RESERVED）
串連2：INSERT ... （RESERVED）
串連2：COMMIT （EXCLUSIVE，寫入完成後釋放）

這樣死結就被避免了。

而EXCLUSIVE方式則更為嚴苛，即使其他串連以DEFERRED方式開啟事務也不會死結：

串連1：BEGIN EXCLUSIVE （EXCLUSIVE）
串連1：SELECT ... （EXCLUSIVE）
串連1：INSERT ... （EXCLUSIVE）
串連2：BEGIN （UNLOCKED）
串連2：SELECT ... （嘗試擷取SHARED鎖，但已有EXCLUSIVE鎖未釋放，返回SQLITE_BUSY，等待使用者重試）
串連1：COMMIT （EXCLUSIVE，寫入完成後釋放）
串連2：SELECT ... （SHARED）
串連2：INSERT ... （RESERVED）
串連2：COMMIT （EXCLUSIVE，寫入完成後釋放）

不過在並發很高的情況下，直接擷取EXCLUSIVE鎖的難度比較大；而且為了避免EXCLUSIVE狀態長期阻塞其他請求，最好的方式還是讓所有寫事務都以IMMEDIATE方式開始。
順帶一提，要實現重試的話，可以使用sqlite3_busy_timeout()或sqlite3_busy_handler()函數。

由此可見，要想保證安全執行緒的話，可以有這4種方式：

1. SQLite使用單線程模式，用一個專門的線程訪問資料庫。
2. SQLite使用單線程模式，用一個線程隊列來訪問資料庫，隊列一次只允許一個線程執行，隊列裡的線程共用一個資料庫連接。
3. SQLite使用多線程模式，每個線程建立自己的資料庫連接。
4. SQLite使用串列模式，所有線程共用全域的資料庫連接。

  三、sqlite3插入速度慢  1.像上述一樣顯示的給多個insert加上事務　　sqlite在沒有顯式使用事務的時候會為每條insert都使用事務操作，而sqlite資料庫是以檔案的形式存在磁碟中，就相當於每次訪問時都要開啟一次檔案，如果對資料進行大量的操作，時間都耗費在I/O操作上，所以很慢。解決方案是顯式使用事務的形式提交：因為我們開始事務後，進行的大量操作的語句都儲存在記憶體中，當提交時才全部寫入資料庫，此時，資料庫檔案也就只用開啟一次。  2.如果加上事務還是不行，可以嘗試修改同步模式　　初用sqlite3插入資料時，插入每條資料大概需要100ms左右。如果是大量匯入，可以引進事務提高速度。但是假設你的業務是每間隔幾秒插入幾條資料，顯然100ms是不能容許的。
解決辦法是，在調用sqlite3_open函數後添加下面一行代碼：    sqlite3_exec(db, "PRAGMA synchronous = OFF; ", 0,0,0);     上面的解決辦法貌似治標不治本，為什麼加上上面的程式碼，速度會提高那麼多？  磁碟同步 1.如何設定：PRAGMA synchronous = FULL; (2) PRAGMA synchronous = NORMAL; (1) PRAGMA synchronous = OFF; (0)
 2.參數含義：當synchronous設定為FULL (2), SQLite資料庫引擎在緊急時刻會暫停以確定資料已經寫入磁碟。這使系統崩潰或電源出問題時能確保資料庫在重起後不會損壞。FULL synchronous很安全但很慢。
 當synchronous設定為NORMAL(1), SQLite資料庫引擎在大部分緊急時刻會暫停，但不像FULL模式下那麼頻繁。 NORMAL模式下有很小的幾率(但不是不存在)發生電源故障導致資料庫損壞的情況。但實際上，在這種情況 下很可能你的硬碟已經不能使用，或者發生了其他的不可恢複的硬體錯誤。
 設定為synchronous OFF (0)時，SQLite在傳遞資料給系統以後直接繼續而不暫停。若運行SQLite的應用程式崩潰， 資料不會損傷，但在系統崩潰或寫入資料時意外斷電的情況下資料庫可能會損壞。另一方面，在synchronous OFF時 一些操作可能會快50倍甚至更多。在SQLite 2中，預設值為NORMAL.而在3中修改為FULL。  www.2cto.com  
 3.建議：如果有定期備份的機制，而且少量資料丟失可接受，用OFF。     注意上面紅色加粗的字樣。總結：如果你的資料對安全性完整性等要求不是太高，可以採用設定為0的方法，畢竟只是“資料庫可能會損壞”，至於損壞幾率為多大，筆者也暫不知曉。。。。。。還沒遇到過損壞，不知什麼時候才會發生。  四、效能最佳化

很多人直接就使用了，並未注意到SQLite也有配置參數，可以對效能進行調整。有時候，產生的結果會有很大影響。
主要通過pragma指令來實現。
比如： 空間釋放、磁碟同步、Cache大小等。
不要開啟auto_vacuum, Vacuum的效率非常低！

1 auto_vacuum
　　PRAGMA auto_vacuum; 
　　PRAGMA auto_vacuum = 0 | 1;
　　查詢或設定資料庫的auto-vacuum標記。
　　正常情況下，當提交一個從資料庫中刪除資料的事務時，資料庫檔案不改變大小。未使用的檔案頁被標記並在以後的添加操作中再次使用。這種情況下使用VACUUM命令釋放刪除得到的空間。
　　當開啟auto-vacuum，當提交一個從資料庫中刪除資料的事務時，資料庫檔案自動收縮， (VACUUM命令在auto-vacuum開啟的資料庫中不起作用)。資料庫會在內部儲存一些資訊以便支援這一功能，這使得資料庫檔案比不開啟該選項時稍微大一些。
　　只有在資料庫中未建任何錶時才能改變auto-vacuum標記。試圖在已有表的情況下修改不會導致報錯。

2 cache_size
建議改為8000
　　PRAGMA cache_size; 
　　PRAGMA cache_size = Number-of-pages;
　　查詢或修改SQLite一次儲存在記憶體中的資料庫檔案頁數。每頁使用約1.5K記憶體，預設的緩衝大小是2000. 若需要使用改變大量多行的UPDATE或DELETE命令，並且不介意SQLite使用更多的記憶體的話，可以增大緩衝以提高效能。
　　當使用cache_size pragma改變緩衝大小時，改變僅對目前的交談有效，當資料庫關閉重新開啟時緩衝大小恢複到預設大小。 要想永久改變緩衝大小，使用default_cache_size pragma.

3 case_sensitive_like
開啟。不然搜尋中文字串會出錯。
　　PRAGMA case_sensitive_like; 
　　PRAGMA case_sensitive_like = 0 | 1;
　　LIKE運算子的預設行為是忽略latin1字元的大小寫。因此在預設情況下‘a‘ LIKE ‘A‘的值為真。可以通過開啟case_sensitive_like pragma來改變這一預設行為。當啟用case_sensitive_like，‘a‘ LIKE ‘A‘為假而 ‘a‘ LIKE ‘a‘依然為真。

4 count_changes
開啟。便於調試
　　PRAGMA count_changes; 
　　PRAGMA count_changes = 0 | 1;
　　查詢或更改count-changes標記。正常情況下INSERT, UPDATE和DELETE語句不返回資料。 當開啟count-changes，以上語句返回一行含一個整數值的資料——該語句插入，修改或刪除的行數。 返回的行數不包括由觸發器產生的插入，修改或刪除等改變的行數。

5 page_size
　　PRAGMA page_size; 
　　PRAGMA page_size = bytes;
　　查詢或設定page-size值。只有在未建立資料庫時才能設定page-size。頁面大小必須是2的整數倍且大於等於512小於等於8192。 上限可以通過在編譯時間修改宏定義SQLITE_MAX_PAGE_SIZE的值來改變。上限的上限是32768.

6 synchronous
如果有定期備份的機制，而且少量資料丟失可接受，用OFF
　　PRAGMA synchronous; 
　　PRAGMA synchronous = FULL; (2) 
　　PRAGMA synchronous = NORMAL; (1) 
　　PRAGMA synchronous = OFF; (0)
　　查詢或更改"synchronous"標記的設定。第一種形式(查詢)返回整數值。 當synchronous設定為FULL (2), SQLite資料庫引擎在緊急時刻會暫停以確定資料已經寫入磁碟。 這使系統崩潰或電源出問題時能確保資料庫在重起後不會損壞。FULL synchronous很安全但很慢。 當synchronous設定為NORMAL, SQLite資料庫引擎在大部分緊急時刻會暫停，但不像FULL模式下那麼頻繁。 NORMAL模式下有很小的幾率(但不是不存在)發生電源故障導致資料庫損壞的情況。但實際上，在這種情況下很可能你的硬碟已經不能使用，或者發生了其他的不可恢複的硬體錯誤。 設定為synchronous OFF (0)時，SQLite在傳遞資料給系統以後直接繼續而不暫停。若運行SQLite的應用程式崩潰， 資料不會損傷，但在系統崩潰或寫入資料時意外斷電的情況下資料庫可能會損壞。另一方面，在synchronous OFF時 一些操作可能會快50倍甚至更多。
　　在SQLite 2中，預設值為NORMAL.而在3中修改為FULL.

7 temp_store
使用2，記憶體模式。
　　PRAGMA temp_store; 
　　PRAGMA temp_store = DEFAULT; (0) 
　　PRAGMA temp_store = FILE; (1) 
　　PRAGMA temp_store = MEMORY; (2)
　　查詢或更改"temp_store"參數的設定。當temp_store設定為DEFAULT (0),使用編譯時間的C預先處理宏 TEMP_STORE來定義儲存暫存資料表和臨時索引的位置。當設定為MEMORY (2)暫存資料表和索引存放於記憶體中。 當設定為FILE (1)則存放於檔案中。temp_store_directorypragma 可用於指定存放該檔案的目錄。當改變temp_store設定，所有已存在的暫存資料表，索引，觸發器及視圖將被立即刪除。
　　經測試，在類BBS應用上，通過以上調整，效率可以提高2倍以上。

sqlite3 多線程和鎖 ，最佳化插入速度及效能最佳化