Sqlite事務(wù)模型、性能優(yōu)化Tips、常見誤區(qū)
0.前言
本文主要介紹sqlite的事務(wù)模型，以及基于事務(wù)模型的一些性能優(yōu)化tips，包括事務(wù)封裝、WAL+讀寫分離、分庫分表、page size優(yōu)化等。并基于手淘sqlite的使用現(xiàn)狀總結(jié)了部分常見問題及誤區(qū)，主要集中在多線程的設(shè)置、多線程下性能優(yōu)化的誤區(qū)等。本文先提出以下幾個問題(作者在進(jìn)行統(tǒng)一存儲的關(guān)系存儲框架優(yōu)化過程中一直困惑的問題，同時也是客戶端開發(fā)者經(jīng)常搞錯的問題)并在正文中進(jìn)行解答：
- 1，sqlite的多進(jìn)程安全是怎么實現(xiàn)的?性能如何?
- 2，sqlite的數(shù)據(jù)庫連接是什么?
- 3，言sqlite必提的讀寫分離，具體指什么?能不能提升數(shù)據(jù)讀寫的性能?為什么
- 4，sqlite提供的WAL特性解決了什么問題?
- 5，sqlite的多線程設(shè)置是為了解決什么問題?與讀寫分離有什么關(guān)系?
- 6，什么情況下數(shù)據(jù)庫會發(fā)生死鎖?
- 7，有哪些性能優(yōu)化的方案?
1，sqlite主要數(shù)據(jù)結(jié)構(gòu)
在深入了解sqlite之前，最好先對sqlite的主要數(shù)據(jù)結(jié)構(gòu)有個概要的理解，sqlite是一個非常完備的關(guān)系數(shù)據(jù)庫系統(tǒng)，由很多部分組成(parser,tokenize,virtual machine等等)，同時sqlite的事務(wù)模型相對簡化，是入門學(xué)習(xí)關(guān)系數(shù)據(jù)庫方法論的一個不錯的選擇;下文對事務(wù)模型的分析也基于這些核心數(shù)據(jù)結(jié)構(gòu)。下面這張圖比較準(zhǔn)確的描述了sqlite的幾個核心數(shù)據(jù)結(jié)構(gòu)：
1.1 Connection
connection通過sqlite3_open函數(shù)打開，代表一個獨立的事務(wù)環(huán)境(這里及下文提到的事務(wù)，包括顯式聲明的事務(wù)，也包括隱式的事務(wù)，即每條獨立的sql語句)。
1.2 B-Tree
B-Tree負(fù)責(zé)請求pager從disk讀取數(shù)據(jù)，然后把頁面(page)加載到頁面緩沖區(qū)(page cache)。
1.3 Pager
Pager負(fù)責(zé)讀寫數(shù)據(jù)庫，管理內(nèi)存緩存和頁面(即下文提到的page caches)，以及管理事務(wù)，鎖和崩潰恢復(fù)。
2，sqlite事務(wù)模型及鎖
2.1 sqlite多進(jìn)程安全及Linux & windows文件鎖
- 關(guān)于建議鎖(advisory lock)和強(qiáng)制鎖(mandatory lock)
建議鎖并不由內(nèi)核強(qiáng)制實行，如果有進(jìn)程不檢查目標(biāo)文件是否已經(jīng)由別的進(jìn)程加了鎖就往其中寫入數(shù)據(jù)，內(nèi)核也不會加以阻攔。因此，建議鎖并不能阻止進(jìn)程對文件的訪問，而是需要進(jìn)程事先對鎖的狀態(tài)做一個約定，并根據(jù)鎖的當(dāng)前狀態(tài)和相互關(guān)系來確定其他進(jìn)程是否能對文件執(zhí)行指定的操作。
強(qiáng)制鎖是由內(nèi)核強(qiáng)制采用的文件鎖——由于內(nèi)核對每個read()和write()操作都會檢查相應(yīng)的鎖，會降低系統(tǒng)性能。
- 典型的建議鎖
鎖文件;鎖文件是最簡單的對文件加鎖的方法，每個需要加鎖的數(shù)據(jù)文件都有一個鎖文件(lock file)。但這種方式存在比較大的問題是無法強(qiáng)制保護(hù)需要加鎖的文件，并且當(dāng)加鎖進(jìn)程非正常退出之后，會造成其他進(jìn)程的死鎖。
記錄鎖;System V和BSD4.3引入了記錄鎖，相應(yīng)的系統(tǒng)調(diào)用為lockf()和flock()。而POSIX對于記錄鎖提供了另外一種機(jī)制，其系統(tǒng)調(diào)用為fcntl()。記錄鎖和鎖文件有兩個很重要的區(qū)別：1)記錄鎖可以對文件的任何一部分加鎖，這對DBMS有極大的幫助，2)記錄鎖的另一個優(yōu)點就是它由進(jìn)程持有，而不是文件系統(tǒng)持有，當(dāng)進(jìn)程結(jié)束時，所有的鎖也隨之釋放。對于一個進(jìn)程本身而言，多個鎖絕不會沖突。(Windows中的鎖都是強(qiáng)制鎖，具體不是很熟，只知道在由于windows上文鎖的限制，sqlite多進(jìn)程下的并發(fā)性會受影響)。
2.1.1 結(jié)論
sqlite的文件鎖在linux/posix上基于記錄鎖實現(xiàn)，也就是說sqlite在文件鎖上會有以下幾個特點：
- 多進(jìn)程使用安全，且不會因為進(jìn)程異常退出引發(fā)死鎖
- 單進(jìn)程使用性能幾乎不會受損，多進(jìn)程使用的性能損耗會受一定的影響
2.2 事務(wù)模型(Without WAL)
sqlite對每個連接設(shè)計了五鐘鎖的狀態(tài)(UNLOCKED, PENDING, SHARED, RESERVED, EXCLUSIVE), sqlite的事務(wù)模型中通過鎖的狀態(tài)保證讀寫事務(wù)(包括顯式的事務(wù)和隱式的事務(wù))的一致性和讀寫安全。sqlite官方提供的事務(wù)生命周期如下圖所示，我在這里稍微加了一些個人的理解：
這里有幾點需要注意：
UNLOCKED、PENDING、SHARED、RESERVED狀態(tài)是非獨占的，也就是說同一個連接中多個線程并發(fā)只讀不會被阻塞。
寫操作的數(shù)據(jù)修改會先寫入page cache，內(nèi)容包括journal日志、b-tree的修改等;正是由于page cache的存在，很多耗時的“重”操作都可以不干擾其他連接和當(dāng)前連接的讀操作，真正意義上保證了sqlite可以同時處理一個寫連接和多個讀連接。
連接由RESERVED狀態(tài)進(jìn)入EXCLUSIVE狀態(tài)，需要等待讀線程釋放SHARED鎖，也即寫操作會被讀操作阻塞
連接由RESERVED狀態(tài)進(jìn)入EXCLUSIVE狀態(tài)后(顯式或隱式的調(diào)用commit)，數(shù)據(jù)庫進(jìn)入獨占狀態(tài)，其他任何連接都無法由UNLOCK狀態(tài)進(jìn)入SHARED狀態(tài);也即寫操作會阻塞所有連接的讀操作(不包括已經(jīng)進(jìn)入SHARED狀態(tài)的操作)，直到page caches寫入數(shù)據(jù)庫文件(成功或失敗)。
數(shù)據(jù)庫獨占狀態(tài)越久，其他操作的等待時間越久，即SQLITE_BUSY產(chǎn)生的一個原因。
2.2.1 結(jié)論
對于常規(guī)的事務(wù)模型(without WAL)，讀寫(連接)分離，不同連接或同一個連接上的讀和寫操作仍互相阻塞，對性能提升沒有明顯幫助。
寫事務(wù)在拿到reserve鎖之前在page cache里的操作不會影響其他連接的讀寫，所以使用事務(wù)進(jìn)行批量數(shù)據(jù)的更新操作有非常大的性能優(yōu)勢。
事務(wù)模型存在死鎖的場景，如下圖所示：
2.3 WAL對事務(wù)模型的影響
按照官方文檔，WAL的原理如下：
對數(shù)據(jù)庫修改是是寫入到WAL文件里的，這些寫是可以并發(fā)的(WAL文件鎖)。所以并不會阻塞其語句讀原始的數(shù)據(jù)庫文件。當(dāng)WAL文件到達(dá)一定的量級時(CheckPoint)，自動把WAL文件的內(nèi)容寫入到數(shù)據(jù)庫文件中。當(dāng)一個連接嘗試讀數(shù)據(jù)庫的時候，首先記錄下來當(dāng)前WAL文件的末尾 end mark，然后，先嘗試在WAL文件里查找對應(yīng)的Page，通過WAL-Index來對查找加速(放在共享內(nèi)存里，.shm文件)，如果找不到再查找數(shù)據(jù)庫文件。
這里結(jié)合源碼，有下面幾個理解：
- 數(shù)據(jù)的寫操作寫入WAL的過程不再需要SHARED鎖、EXCLUSIVE鎖，而是需要WAL文件鎖。
- 數(shù)據(jù)的寫操作不會被讀操作阻塞(寫操作不再需要SHARED鎖)。
- 數(shù)據(jù)的讀操作不會被寫操作阻塞(寫操作不再需要獨占數(shù)據(jù)庫)。
- WAL文件寫入數(shù)據(jù)庫文件的過程，依然會被讀操作阻塞，也會阻塞讀操作。
- WAL文件的大小設(shè)置很關(guān)鍵，過大的WAL文件，會讓查找操作從B-Tree查找退化成線性查找(WAL中page連續(xù)存儲);但大的WAL文件對寫操作較友好。
2.3.1 結(jié)論
- 只有開了WAL，再使用讀寫(連接)分離才能有較大的性能提升。
- WAL本質(zhì)上是將部分隨機(jī)寫操作(數(shù)據(jù)庫文件和journal日志)變成了串行寫WAL文件，并進(jìn)行了鎖分離。
- WAL文件的大小設(shè)置很關(guān)鍵，過大的WAL文件，會讓查找操作從B-Tree查找退化成線性查找(WAL中page連續(xù)存儲);但大的WAL文件對寫操作較友好。
2.4 多線程設(shè)置
- 多線程是sqlite使用過程中比較容易誤解的一個概念，帶來的問題要么是產(chǎn)生各種線程安全問題，要么是無法充分發(fā)掘sqlite的性能，這里結(jié)合代碼我們簡單分析一下并給出幾個重要結(jié)論。
- 線程安全設(shè)置主要在設(shè)置bCoreMutex和bFullMutex，啟用bFullMutex之后數(shù)據(jù)庫連接和prepared statement都已加鎖(社區(qū)各種文檔都到此為止);但還是感覺不夠清晰：這兩個鎖會對我們使用sqlite有怎樣的影響?best practice又是什么?
- // 多線程的設(shè)置的實現(xiàn)：設(shè)置bCoreMutex和bFullMutex
- #if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-54466-46756 */
- case SQLITE_CONFIG_SINGLETHREAD: {
- /* EVIDENCE-OF: R-02748-19096 This option sets the threading mode to
- ** Single-thread. */
- sqlite3GlobalConfig.bCoreMutex = 0; /* Disable mutex on core */
- sqlite3GlobalConfig.bFullMutex = 0; /* Disable mutex on connections */
- break;
- }
- #endif
- #if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-20520-54086 */
- case SQLITE_CONFIG_MULTITHREAD: {
- /* EVIDENCE-OF: R-14374-42468 This option sets the threading mode to
- ** Multi-thread. */
- sqlite3GlobalConfig.bCoreMutex = 1; /* Enable mutex on core */
- sqlite3GlobalConfig.bFullMutex = 0; /* Disable mutex on connections */
- break;
- }
- #endif
- #if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-59593-21810 */
- case SQLITE_CONFIG_SERIALIZED: {
- /* EVIDENCE-OF: R-41220-51800 This option sets the threading mode to
- ** Serialized. */
- sqlite3GlobalConfig.bCoreMutex = 1; /* Enable mutex on core */
- sqlite3GlobalConfig.bFullMutex = 1; /* Enable mutex on connections */
- break;
- }
- #endif
- 如果FullMutex打開，則每個數(shù)據(jù)庫連接會初始化一個互斥量成員(db->mutex)，也就是社區(qū)各種文檔上所說的“bFullMutex是對連接的線程保護(hù)”。
- if( isThreadsafe ){ // bFullMutex = 1
- db->mutex = sqlite3MutexAlloc(SQLITE_MUTEX_RECURSIVE); // 每個數(shù)據(jù)庫連接會初始化一個成員鎖
- if( db->mutex==0 ){
- sqlite3_free(db);
- db = 0;
- goto opendb_out;
- }
- }
如果CoreMutex打開，則會設(shè)置全局的鎖控制函數(shù)。
- /* If the xMutexAlloc method has not been set, then the user did not
- ** install a mutex implementation via sqlite3_config() prior to
- ** sqlite3_initialize() being called. This block copies pointers to
- ** the default implementation into the sqlite3GlobalConfig structure.
- */
- sqlite3_mutex_methods const *pFrom;
- sqlite3_mutex_methods *pTo = &sqlite3GlobalConfig.mutex;
- if( sqlite3GlobalConfig.bCoreMutex ){
- pFrom = sqlite3DefaultMutex();
- }else{
- pFrom = sqlite3NoopMutex();
- }
- pTo->xMutexInit = pFrom->xMutexInit;
- pTo->xMutexEnd = pFrom->xMutexEnd;
- pTo->xMutexFree = pFrom->xMutexFree;
- pTo->xMutexEnter = pFrom->xMutexEnter;
- pTo->xMutexTry = pFrom->xMutexTry;
- pTo->xMutexLeave = pFrom->xMutexLeave;
- pTo->xMutexHeld = pFrom->xMutexHeld;
- pTo->xMutexNotheld = pFrom->xMutexNotheld;
- sqlite3MemoryBarrier();
- pTo->xMutexAlloc = pFrom->xMutexAlloc;
- 而CoreMutext未打開的話，sqlite3NoopMutex()的實現(xiàn)如下(CoreMutext未打開的話，對應(yīng)使用的鎖函數(shù)均為空實現(xiàn))：
- sqlite3_mutex_methods const *sqlite3NoopMutex(void){
- static const sqlite3_mutex_methods sMutex = {
- noopMutexInit,
- noopMutexEnd,
- noopMutexAlloc,
- noopMutexFree,
- noopMutexEnter,
- noopMutexTry,
- noopMutexLeave,
- 0,
- 0,
- };
- return &sMutex;
- }
- // CoreMutext未打開的話，對應(yīng)使用的鎖函數(shù)均為空實現(xiàn)
- static int noopMutexInit(void){ return SQLITE_OK; }
- static int noopMutexEnd(void){ return SQLITE_OK; }
- static sqlite3_mutex *noopMutexAlloc(int id){
- UNUSED_PARAMETER(id);
- return (sqlite3_mutex*)8;
- }
- static void noopMutexFree(sqlite3_mutex *p){ UNUSED_PARAMETER(p); return; }
- static void noopMutexEnter(sqlite3_mutex *p){ UNUSED_PARAMETER(p); return; }
- static int noopMutexTry(sqlite3_mutex *p){
- UNUSED_PARAMETER(p);
- return SQLITE_OK;
- }
- static void noopMutexLeave(sqlite3_mutex *p){ UNUSED_PARAMETER(p); return; }
- FullMutex保護(hù)了什么?
粗略看了一下，通過db->mutex(sqlite3_mutex_enter(db->mutex);)保護(hù)的邏輯塊和函數(shù)主要如下列表：
- sqlite3_db_status、sqlite3_finalize、sqlite3_reset、sqlite3_step、sqlite3_exec、
- sqlite3_preppare_v2、column_name、blob操作、sqlite3Close、sqlite3_errmsg...
基本覆蓋了所有的讀、寫、DDL、DML，也包括prepared statement操作;也就是說，在未打開FullMutex的情況下，在一個連接上的所有DB操作必須嚴(yán)格串行執(zhí)行，包括只讀操作。
- CoreMutex保護(hù)了什么?
sqlite3中的mutex操作函數(shù)，除了用于操作db->mutex這個成員之外，還主要用于以下邏輯塊(主要是影響數(shù)據(jù)庫所有連接的邏輯)：
shm操作(index for wal)、內(nèi)存池操作、內(nèi)存緩存操作等。
2.4.1 結(jié)論
- 多線程設(shè)置是決定DDL、DML、WAL(包括SHM)操作是否線程安全的設(shè)置。
- 多線程設(shè)置與讀寫(連接)分離沒有任何關(guān)系，并不是實現(xiàn)讀寫(連接)分離的必要條件(很多人對這一點有誤解)。
3，性能優(yōu)化tips
3.1 合理使用事務(wù)
由#2.2的分析可知，寫操作會在RESERVED狀態(tài)下將數(shù)據(jù)更改、b-tree的更改、日志等寫入page cache，并最終flush到數(shù)據(jù)庫文件中;使用事務(wù)的話，只需要一次對DB文件的flush操作，同時也不會對其他連接的讀寫操作阻塞;對比以下兩種數(shù)據(jù)寫入方式(這里以統(tǒng)一存儲提供的API為例)，實測耗時有十幾倍的差距(當(dāng)然對于頻繁的讀操作，使用事務(wù)可以減事務(wù)狀態(tài)的切換，也會有一點點性能提升)：
- // batch insert in transaction with 1000000 records
- //
- AliDBExecResult* execResult = NULL;
- _database->InTransaction([&]() -> bool { // in transaction
- auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)");
- for (auto record : records) { // bind 1000000 records
- // bind record
- ...
- ...
- statement->AddBatch();
- }
- auto result = statement->ExecuteUpdate();
- return result->is_success_;
- });
- // batch insert with 1000000 records, no transaction
- //
- auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)");
- for (auto record : records) { // bind 1000000 records
- // bind record
- ...
- ...
- statement->ExecuteUpdate();
- }
3.2 啟用WAL + 讀寫(連接)分離
啟用WAL之后，數(shù)據(jù)庫大部分寫操作變成了串行寫(對WAL文件的串行操作)，對寫入性能提升有非常大的幫助;同時讀寫操作可以互相完全不阻塞(如#2.3所述)。上述兩點比較好的解釋了啟用WAL帶來的提升;同時推薦一個寫連接 + 多個讀連接的模型，如下圖所示：
3.2.1 讀寫連接分離的細(xì)節(jié)
- 讀操作使用不同的連接并發(fā)執(zhí)行，可以完全避免由于顯式事務(wù)、寫操作之間的鎖競爭帶來的死鎖。
- 所有的寫操作、顯式事務(wù)操作都使用同一個連接，且所有的寫操作、顯式事務(wù)操作都串行執(zhí)行。
可以完全避免由于顯式事務(wù)、寫操作之間的鎖競爭帶來的死鎖，如#2.2.1提到的死鎖的例子。
并發(fā)寫并不能有效的提高寫入效率，參考如下偽代碼，哪段執(zhí)行更快?
- // two transactions:
- void Transaction_1() {
- connection_->Exec("BEGIN");
- connection_->Exec("insert into table(value) values('xxxx')");
- connection_->Exec("COMMIT");
- }
- void Transaction_2() {
- connection_->Exec("BEGIN");
- connection_->Exec("insert into table(value) values('xxxx')");
- connection_->Exec("COMMIT");
- }
- // code fragment 1: concurrent transaction
- thread1.RunBlock([]() -> void {
- for (int i=0; i< 100000; i++) {
- Transaction_1();
- }
- });
- thread2.RunBlock([]() -> void {
- for (int i=0; i< 100000; i++) {
- Transaction_2();
- }
- });
- thread1.Join(); thread2.join();
- // code fragment 2: serial transaction
- for (int i=0; i< 100000; i++) {
- Transaction_1();
- }
- for (int i=0; i< 100000; i++) {
- Transaction_2();
- }
3.3 針對具體業(yè)務(wù)場景，設(shè)置合適的WAL SIZE
如#2.3提到，過大的WAL文件，會讓查找操作從B-Tree查找退化成線性查找(WAL中page連續(xù)存儲);但大的WAL文件對寫操作較友好。對于大記錄的寫入操作，較大的wal size會有效提高寫入效率，同時不會影響查詢效率。
3.4 針對業(yè)務(wù)場景分庫分表
分庫分表可以有效提高數(shù)據(jù)操作的并發(fā)度;但同時過多的表會影響數(shù)據(jù)庫文件的加載速度?，F(xiàn)在數(shù)據(jù)庫方向的很多研究包括Auto sharding, paxos consensus, 存儲和計算的分離等;Auto
application-awared optimization，Auto hardware-awared optimization，machine
learning based optimization也是不錯的方向。
3.5 其他
包括WAL checkpoint策略、WAL size優(yōu)化、page size優(yōu)化等，均需要根據(jù)具體的業(yè)務(wù)場景設(shè)置。
4，常見問題 & 誤區(qū)
4.1 線程安全設(shè)置及誤區(qū)
sqlites configuration options: https://sqlite.org/c3ref/c_config_getmalloc.html
按照sqlite文檔，sqlite線程安全模式有以下三種：
SQLITE_CONFIG_SINGLETHREAD(單線程模式)
This option sets the threading mode to Single-thread. In other words, it disables all mutexing and puts SQLite into a mode where it can only be used by a single thread.
SQLITE_CONFIG_MULTITHREAD(多線程模式)
This option sets the threading mode to Multi-thread. In other words, it disables mutexing on database connection and prepared statement objects. The application is responsible for serializing access to database connections and prepared statements. But other mutexes are enabled so that SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time.
SQLITE_CONFIG_SERIALIZED(串行模式)
This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time.
4.1.1 誤區(qū)一：多線程模式是線程安全的
產(chǎn)生這個誤區(qū)主的主要原因是官方文檔里的最后一句話：
SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time.
但大家往往忽略了前面的一句話：
it disables mutexing on database connection and prepared statement objects
即對于單個連接的讀、寫操作，包括創(chuàng)建出來的prepared statement操作，都沒有線程安全的保護(hù)。也即在多線程模式下，對單個連接的操作，仍需要在業(yè)務(wù)層進(jìn)行鎖保護(hù)。
4.1.2 誤區(qū)二：多線程模式下，并發(fā)讀操作是安全的
關(guān)于這一點，#2.4給出了具體的解釋;多線程模式下(SQLITE_CONFIG_MULTITHREAD)對prepared statement、connection的操作都不是線程安全的
4.1.3 誤區(qū)三：串行模式下，所有數(shù)據(jù)庫操作都是串行執(zhí)行
這個問題比較籠統(tǒng);即使在串行模式下，所有的數(shù)據(jù)庫操作仍需遵循事務(wù)模型;而事務(wù)模型已經(jīng)將數(shù)據(jù)庫操作的鎖進(jìn)行了非常細(xì)粒度的分離，串行模式的鎖也是在上層保證了事務(wù)模型的完整性。
4.1.4 誤區(qū)四：多線程模式性能最好，串行模式性能差
多線程模式下，仍需要業(yè)務(wù)上層進(jìn)行鎖保護(hù)，串行模式則是在sqlite內(nèi)部進(jìn)行了鎖保護(hù);認(rèn)為多線程模式性能好的兄弟哪來的自信認(rèn)為業(yè)務(wù)層的鎖實現(xiàn)比sqlite內(nèi)部鎖實現(xiàn)性能更高?
