# 第5章 設計和概念
本章為后面的3章打下基礎，這幾章專注于SQLite編程。這幾章專注于作為程序員，在編碼時你所應該了解的有關SQLite的東西。無論你是用C語言對SQLite進行編程，還是用其它的編程語言，這些內容都是重要的。它不僅幫助你了解API，還包括部分有關SQLite的體系結構和實現方法的內容。具備了這些知識，你就可以編出更好的代碼，這些代碼執行得更快，且不會產生死鎖、不可預知錯誤等問題。你會看到SQLite如何處理你的代碼，你還會變得更加自信，因為你知道自己正前進在解決問題的正確方向上。
你不需要從頭到尾地讀內部代碼才能理解這些內容，你也不必是一個C程序員。SQLite的設計和概念都是非常直觀和容易理解的。只有一小部分內容你需要知道，本章就介紹這些內容。
1．明顯地，你需要知道API是如何工作的。于是本章從一個對API概念性的介紹開始，圖示了主要的數據結構，API的一般設計和它主要的函數。還可以看到SQLite的一些主要的子系統，這些子系統在查詢的處理過程中起著重要作用。
2．除了知道什么函數做什么，你還需要從比API高的角度來看問題，看看所有這些函數在事務(transactions)中是如何操作的。SQLite的所有的工作都是在事務中完成的。于是，你需要知道在API之下，事務如何按照鎖的約束來工作。如果你不知道鎖是如何操作的，這些鎖就會導致問題。當對鎖有所了解之后，你不僅可以避免潛在的并發問題，還可以通過編程控制它們來優化你的查詢。
3．最后，你還必須理解如何將這些內容應用于編碼。本章的最后部分會將3個主題結合在一起——API、事務和鎖，并且看一看好代碼與壞代碼的區別。
空注：本章看得還是比較仔細的，翻譯的也比較全。
## API
從功能的角度來區分，SQLite的API可分為兩類：核心API的擴充API。核心API由所有完成基本數據庫操作的函數構成，包括：連接數據庫、執行SQL和遍歷結果集。它還包括一些功能函數，用來完成字符串格式化、操作控制、調試和錯誤處理等任務。擴充API提供不同的方法來擴展SQLite，它使你能夠創建自定義的SQL擴展，并與SQLite本身的SQL相集成。
## SQLite版本3的新特性
在開始之前，我們先討論一下SQLite版本3的新特色：
一、首先，SQLite的API被徹底重新設計了，并具有了許多新特性。由第二版的15個函數增加到88個函數。這些函數包括支持UTF-8和UTF-16編碼的功能函數。SQLite3有一個更方便的查詢模式，使查詢的預處理更容易并且支持新的參數綁定方法。SQLite3還增加了用戶定義的排序序列、CHECK約束、64位的鍵值和新的查詢優化。
二、在后端大大地改進了并發性能。加鎖子系統引進了一種新的鎖升級模型，解決了第二版中的寫進程餓死的問題。這種模型保證寫進程按照先來先服務的算法得到排它鎖(Exclusive Lock)。甚至，寫進程通過把結果寫入臨時緩沖區(Temporary Buffer)，可以在得到排它鎖之前就開始工作。這對于寫要求較高的應用，性能可提高400%。
三、SQLite 3包含一個改進了的B-tree模型。現在對庫表使用B+tree，大大提高查詢效率，存儲大數據字段更有效，并可以從磁盤上刪除不用了的字段。其結果是數據庫文件的體積減小了25–35%并改善了全面性能。B+tree將在第9章介紹。
四、SQLite 3最重要的改變是它的存儲模型。由第二版只支持文本模型，擴展到支持5種本地數據類型，如第4章所介紹的，還增強了弱類型和類型親和性的概念。每種類型都被優化，以得到更高的查詢性能并戰用更少的存儲空間。例如，整數和浮點數以二進制的形式存儲，而不再是以ASCII形式存儲，這樣，就不必再對WHERE子句中的值進行轉換(像第2版那樣)。弱類型使你能夠在定義一個字段時選擇是否預聲明類型。親和性確定一個值存儲于字段的格式——基于值的表示法和列的親和性。類型親和性與弱類型緊密關聯——列的親和性由其類型的聲明確定。
在很多方面，SQLite 3是一個與SQLite 2完全不同的數據庫，并且提供了很多在適應性、特色和性能方面的改進。
## 主要的數據結構
在第1章你看到了很多SQLite組件——分詞器、分析器和虛擬機等等。但是從程序員的角度，最需要知道的是：connection、statements、B-tree和pager。它們之間的關系如圖5-1所示。這些對象構成了編寫優秀代碼所必須知道的3個首要內容：API、事務和鎖。
圖5-1 SQLite C API對象模型
從技術上來說，B-tree和pager不是API的一部分，但是它們卻在事務和鎖上起著關鍵作用。這里只介紹關聯的內容，詳細內容將在“事務”一節介紹。
### 連接(Connection)和語句(Statement)
連接(Connection)和語句(Statement)是執行SQL命令涉及的兩個主要數據結構，幾乎所有通過API進行的操作都要用到它們。連接代表在一個獨立的事務環境下的一個單獨的數據庫連接。每個語句都和一個連接關聯，通常表示一個編譯過的SQL語句。在內部，它以VDBE字節碼表示。語句包括執行一個命令所需要一切，包括保存VDBE程序執行狀態所需的資源，指向硬盤記錄的B-tree游標，以及參數等等。
### B-tree和Pager
一個連接可以有多個database對象——一個主數據庫和附加的數據庫。每一個數據庫對象有一個B-tree對象，一個B-tree有一個pager對象(這里的對象不是面向對象的“對象”，只是為了說清楚問題)。
語句最終都是通過連接的B-tree和pager從數據庫讀或者寫數據，通過B-tree的游標(cursor)遍歷存儲在頁(page)中的記錄。在游標訪問頁之前，頁必須從磁盤加載到內存，而這就是pager的任務。任何時候，如果B-tree需要頁，它都會請求pager從磁盤讀取數據，pager把頁加載到頁緩沖區(page cache)。之后，B-tree和與之關聯的游標就可以訪問位于頁中的記錄了。
如果游標改變了頁，為了防止事務回滾，pager必須采取特殊的方式保存原來的頁。總的來說，pager負責讀寫數據庫，管理內存緩存和頁，以及管理事務、鎖和崩潰恢復(這些在“事務”一節會詳細介紹)。
總之，關于連接和事務，你必須知道兩件事：(1)對數據庫的任何操作，一個連接存在于一個事務之下。(2)一個連接絕不會同時存在于多個事務之下。無論何時，一個連接在對數據庫做任何操作時，都總是在恰好一個事務之下，不會多，也不會少。
## 核心API
核心API主要與執行SQL命令有關。有兩種方法執行SQL語句：預編譯查詢和封裝查詢。預編譯查詢由三個階段構成：準備(preparation)、執行(execution)和定案(finalization)。其實封閉裝查詢只是對預編譯查詢的三個過程進行了包裝而已，最終也會轉化為預編譯查詢來執行。
### 連接的生命周期(The Connection Lifecycle)
和大多數據庫連接相同，其生命周期由三個階段構成：
1\. 連接數據庫(Connect to the database)。
2\. 處理事務(Perform transactions)：如你所知，任何命令都在事務下執行。默認情況下，事務自動提交，也就是每一個SQL語句都在一個獨立的事務下運行。當然也可以通過使用BEGIN..COMMIT手動提交事務。
3\. 斷開連接(Disconnect from the database)：關閉數據庫文件。還要關閉所有附加的數據庫文件。
在查詢的處理過程中還包括其它一些行為，如處理錯誤、“忙”句柄和schema改變等，所有這些都將在utility functions一節中介紹。
連接數據庫(Connect to the database)：
連接數據庫不只是打開一個文件。每個SQLite數據庫都存儲在單獨的操作系統文件中——數據庫與文件一一對應。連接、打開數據庫的C API為sqlite3_open()，它只是一個簡單的系統調用，來打開一個文件，它的實現位于main.c文件中。
SQLite還可以創建內存數據庫。如果你使用:memory:或一個空字符串做數據庫名，數據庫將在RAM中創建。內存數據庫將只能被創建它的連接所存取，不能與其它連接共享。另外，內存數據庫只能存活于連接期間，一旦連接關閉，數據庫就將從內存中被刪除。
當連接一個位于磁盤上的數據庫時，如果數據庫文件存在，則打開該文件；如果不存在，SQLite會假定你想創建一個新的數據庫。在這種情況下，SQLite不會立即在磁盤上創建一個文件，只有當你向數據庫寫入數據時才會創建文件，比如：創建表、視圖或者其它數據庫對象。如果你打開一個數據庫，不做任何事，然后關閉它，SQLite會創建一個文件，但只是一個長度為0的空文件而已。
另外一個不立即創建新文件的原因是，一些數據庫的參數，比如：編碼，頁大小等，只能在數據庫創建之前設置。默認情況下，頁大小為1024字節，但是你可以選擇512-32768字節之間為 2冪數的數字。有些時候，較大的頁能更有效地處理大量的數據。你可以使用page_size pragma來設置數據庫頁大小。
字符編碼是數據庫的另一個永久設置。你可以使用encoding pragma來設置字符編碼，其值可以是UTF-8、UTF-16、UTF-16le (little endian)和UTF-16be (big endian)。
### 執行預處理查詢
前面提到，預處理查詢(Prepared Query)是SQLite執行所有SQL命令的方式，包括以下三個步聚：
(1) 準備(preparation)：
分詞器(tokenizer) 、分析器(parser)和代碼生成器(code generator)把SQL語句編譯成VDBE字節碼，編譯器會創建一個語句句柄(sqlite3_stmt)，它包括字節碼以及其它執行命令和遍歷結果集所需的全部資源。相應的C API為sqlite3_prepare()，位于prepare.c文件中。
(2) 執行(execution)：
虛擬機執行字節碼，執行過程是一個步進(stepwise)的過程，每一步(step)由sqlite3_step()啟動，并由VDBE執行一段字節碼。當第一次調用sqlite3_step()時，一般會獲得一種鎖，鎖的種類由命令要做什么(讀或寫)決定。對于SELECT語句，每次調用sqlite3_step()使用語句句柄的游標移到結果集的下一行。對于結果集中的每一行，它返回SQLITE_ROW，當到達結果末尾時，返回SQLITE_DONE。對于其它SQL語句(INSERT、UPDATE、DELETE等)，第一次調用sqlite3_step()就導致VDBE執行整個命令。
(3) 定案(finalization)：
VDBE關閉語句，釋放資源。相應的C API為sqlite3_finalize()，它導致VDBE結束程序運行并關閉語句句柄。如果事務是由人工控制開始的，它必須由人工控制進行提交或回卷，否則sqlite3_finalize()會返回一個錯誤。當sqlite3_finalize()執行成功，所有與語句對象關聯的資源都將被釋放。在自動提交模式下，還會釋放關聯的數據庫鎖。
每一步(preparation、execution和finalization)都關聯于語句句柄的一種狀態(prepared、active和finalized)。Pepared表示所有資源都已分配，語句已經可以執行，但還沒有執行。現在還沒有申請鎖，一直到調用sqlite3_step()時才會申請鎖。Active狀態開始于對sqlite3_step()的調用，此時語句正在被執行并擁有某種鎖。Finalized意味著語句已經被關閉且所有相關資源已經被釋放。通過圖5-2可以更容易地理解該過程：
圖5-2 語句處理
下面代碼例示了在SQLite上執行一個query的一般過程。
```
#include<stdio.h>
#include<stdlib.h>
#include"sqlite3.h"
#include<string.h>
#pragma comment(lib, "sqlite3.lib")
int main(int argc,char **argv)
{
?????? int rc,i,ncols;
?????? sqlite3 *db;
?????? sqlite3_stmt *stmt;
?????? char *sql;
?????? const char*tail;
?????? //打開數據
?????? rc=sqlite3_open("foods.db",&db);
?????? if(rc){
????????????? fprintf(stderr,"Can'topendatabase:%sn",sqlite3_errmsg(db));
????????????? sqlite3_close(db);
????????????? exit(1);
?????? }
?????? sql="select * from episodes";
?????? //預處理
?????? rc=sqlite3_prepare(db,sql,(int)strlen(sql),&stmt,&tail);
?????? if(rc!=SQLITE_OK){
????????????? fprintf(stderr,"SQLerror:%sn",sqlite3_errmsg(db));
?????? }
?????? rc=sqlite3_step(stmt);
?????? ncols=sqlite3_column_count(stmt);
?????? while(rc==SQLITE_ROW){
????????????? for(i=0;i<ncols;i++){
???????????????????? fprintf(stderr,"'%s'",sqlite3_column_text(stmt,i));
????????????? }
????????????? fprintf(stderr,"\n");
????????????? rc=sqlite3_step(stmt);
?????? }
?????? //釋放statement
?????? sqlite3_finalize(stmt);
?????? //關閉數據庫
?????? sqlite3_close(db);
?????? printf("\n");
?????? return(0);
}
```
空注：
上述代碼在VC6++下調試通過，其步驟為：
將上述代碼做成一個.cpp文件并為它創建工作空間。
將sqlite3.def和sqlite3.dll文件復制到工作空間所在目錄。(這兩個文件可由sqlitedll-3_6_18.zip文件解壓而得)
進入DOS命令行狀態，進入工作空間所在目錄，執行如下3條命令：
```
PATH = D:\Program Files\Microsoft Visual Studio 9.0\VC\bin;%PATH%
PATH = D:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE;%PATH%
LIB /DEF:sqlite3.def /machine:IX86
```
注：上述命令用于制作.lib文件，用于項目的鏈接。如果PATH已經設好，前兩條命令可能不需要執行；如果執行，可能需要根據VC的安裝情況有所改動。
臨時存儲器：
臨時存儲器是查詢處理的重要組成部分。SQLite有時需要存儲命令執行過程中產生的中間結果——如結果集由ORDER BY子句指定需要排序，或進行多表交叉查詢時。中間結果存儲在臨時存儲器中。臨時存儲器或者在內存，或者在文件中。
### 使用參數化的SQL
SQL語句可以包含參數。參數是SQL命令中的占位符，它們的值會在編譯之后提供(稱為“綁定”)。下面是帶參數的SQL示例語句：
```
INSERT INTO foods (id, name) VALUES (?,?);
INSERT INTO episodes (id, name) (:id, :name);
```
上述語句表現了參數的兩種綁定方式：按位置和按名稱。第1條命令采用了位置參數，第2條命令采用了名稱參數。
Positional parameters are defined by the position of the question mark in the statement. The first question mark has position 1, the second 2, and so on. Named parameters use actual variable names, which are prefixed with a colon. When sqlite3_prepare() compiles a statement with parameters, it allocates placeholders for the parameters in the resulting statement handle. It then expects values to be provided for these parameters before the statement is
executed. 如果你沒有為參數綁定值，在語句執行時，SQLite 默認為各參數賦NULL值。
使用參數綁定的好處是你可以多次執行相同的語句而不必重新編譯它們。You just reset the statement, bind a new set of values, and reexecute. This is where resetting rather than finalizing a statement comes in handy: it avoids the overhead of SQL compilation. By resetting a statement, you are reusing the compiled SQL code. You completely avoid the tokenizing, parsing, and code generation overhead. Resetting a statement is implemented in the API by the sqlite3_reset() function.
使用參數的另一個好處是：SQLite可以對你綁定到參數的字符串值做一定的處理。例如，有一個參數值'Kenny's Chicken'，參數綁定過程會自動地將其轉化為'Kenny''s Chicken'。下面的偽代碼說明了綁定參數的基本方法：
```
db = open('foods.db')
stmt = db.prepare('INSERT INTO episodes (id, name) VALUES (:id, :name)')
stmt.bind('id', '1')
stmt.bind('name', 'Soup Nazi')
stmt.step()
# Reset and use again
stmt.reset()
stmt.bind('id', '2')
stmt.bind('name', 'The Junior Mint')
# Done
stmt.finalize()
db.close()
```
### 執行封裝的Query
如前文所述，有兩個很有用的函數可以封裝查詢的預編譯過程，允許你在單一的函數調用中執行SQL命令。一個函數是sqlite3_exec()，特別適合執行不需要返回數據的查詢。另一個是sqlite3_get_table()，返回一個表格化的結果集。In many language extensions you will see analogs to both functions. Most extensions refer to the first method simply as exec(), and the second as just get_table().
sqlite3_get_table()的函數名起得不太合適，聽起來好象是要返回一個表的全部數據。其實它的命名只是表示將會返回一個表格化的結果集。
```
db = open('foods.db')
table = db.get_table("SELECT * FROM episodes LIMIT 10")
for i=0; i < table.rows; i++
??for j=0; j < table.cols; j++
????print table[i][j]
??end
end
db.close()
```
### 錯誤處理
前面的例子都是極度簡化了的，只關注查詢的執行。而在實際情況下，你總得關注出錯的可能性。你前面所看到的幾乎每個函數在某些情況下都可能引發錯誤。通常你需要對錯誤代碼SQLITE_ERROR、SQLITE_BUSY和SQLITE_SCHEMA進行處理。SQLITE_BUSY在當前連接不能夠獲得一個鎖時觸發，SQLITE_SCHEMA在語句的編譯與執行之間schema發生了改變時觸發。“忙”狀態將在本章的事務一節中介紹。Schema錯誤將在第6章介紹。
很多語言擴展難于處理schema錯誤。有些透明地報告處于忙狀態，有些直接返回實際的錯誤代碼。無論如何，如果你遇到了schema錯誤，表示有其它的連接在你的讀與寫之間改變了數據庫，你的語句已不再合法。你需要重新編譯語句，以便能夠重新執行它。Schema錯誤只會發生在對prepare()的調用和第1次對step()的調用之間。如果你的第1次step()調用成功，那你就不必再擔心在后面調用step()時會引發schema錯誤了，因為你的連接已經鎖住了數據庫，其它的連接不可能在此期間修改數據庫。
對于一般性錯誤，API提供了sqlite3_errcode()來獲取最后一次調用API函數時的返回碼。你可以使用sqlite3_errmsg()函數得到更具體的錯誤信息，該函數提供了對最后錯誤的文字描述，大多數語言擴展都支持這個函數。
有了這個觀念，前面例子中的每個調用都可以用類似下面的代碼來檢查錯誤：
```
# Check and report errors
if db.errcode() != SQLITE_OK
??print db.errmsg(stmt)
end
```
一般情況下，錯誤處理并不困難。處理錯誤的方法由你確切地想要做什么決定。
### 格式化SQL語句
另一個方便的函數是sqlite3_mprintf()，它是標準C庫函數sprintf()的一個變體。它有很獨特的替換符，特別方便對SQL進行處理。它的替換符是%q和%Q。%q的工作原理像%s，從參數列表中取得一個以NULL結束的字符串。它會將單引號反斜杠都雙寫，使你更容易防范SQL注入式攻擊(參本節下文的“SQL注入式攻擊”一段)。例如：
```
char* before = "Hey, at least %q no pig-man.";
char* after = sqlite3_mprintf(before, "\he's\");
```
上述程序執行后after的值為'Hey, at least \\he''s\\ no pig-man'。The single quote in he’s is doubled along with the backslashes around it, making it acceptable as a string literal in a SQL statement. The %Q formatting does everything %qdoes, but it additionally encloses the resulting string in single quotes. Furthermore, if the argument for %Q is a NULL pointer (in C), it produces the string NULL without single quotes. For more information, see the sqlite3_mprintf() documentation in the C API reference in Appendix B.
SQL注入式攻擊：
如果你的應用程序依賴用戶的輸入來構造SQL語句，那么它將很容易受到SQL注入攻擊。如果你沒有精心地過濾用戶輸入，有人可能會輸入別有用心的內容，注入到你的SQL中，并在其后面構成一個新的SQL語句。例如，你的程序用用戶輸入來填充下面SQL語句：
```
SELECT * FROM foods WHERE name='%s';
```
如果無論用戶輸入什么都直接來替換%s，如果用戶對你的數據庫有一定了解，他可以輸入如下內容：
```
nothing' LIMIT 0; SELECT name FROM sqlite_master WHERE name='%
```
將用戶輸入替換進原有的SQL語句之后，變成了兩個新的語句：
```
SELECT * FROM foods WHERE name='nothing' LIMIT 0; SELECT name FROM
sqlite_master WHERE name='%';
```
第1個語句什么都不返回，但第2個將返回表中所有的記錄。Granted, the odds of this happening require quite a bit of knowledge on the attacker’s part, but it is nevertheless possible. Some major (commercial) web applications have been known to keep SQL statements embedded in their JavaScript, which can provide plenty of hints about the database being used. In the previous example, all a malicious user has to do now is insert DROP TABLE statements for every table found in sqlite_master and you could find yourself fumbling through backups.
## 操作控制
API中包含幾個命令來監視、控制，或者說限制數據庫操作。SQLite使用過濾(或稱回叫)函數來完成此功能，你可以注冊它們由特定的事件來調用。有3個“hook”函數：sqlite3_commit_hook()，它監視事務的提交；sqlite3_rollback_hook()，它監視事務的回卷；sqlite3_update_hook()，它監視INSERT、UPDATE和DELETE操作。這些函數在運行時被調用——即當命令執行時被調用。Each hook allows you to register a callback function on a connection-by-connection basis, and lets you provide some kind of application-specific data to be passed to the callback as well. The general use of operational control functions is as follows:
```
def commit_hook(cnx)
?????? log('Attempted commit on connection %x', cnx)
?????? return -1
end
db = open('foods.db')
db.set_commit_hook(rollback_hook, cnx)
db.exec("BEGIN; DELETE from episodes; ROLLBACK")
db.close()
```
### 使用線程
SQLite有幾個可以在多線程環境下使用的函數。在3.3.1版中，SQLite引入了一種稱為共享緩沖區模式的獨特的操作模式，就是為多線程的內嵌式服務器設計的。這個模式提供了一種用單線程來處理多連接的方法，可以共享相同的頁緩沖區，從而降低了整個服務器的內存需求。這個模式包括多個函數來管理內存和服務器。詳見第6章“共享緩沖區模式”一節。
## 擴充API
SQLite的擴充API用來支持用戶定義的函數、聚合和排序法。用戶定義函數是一個SQL函數，它對應于你用C語言或其它語言實現的函數的句柄。使用C API時，這些句柄用C/C++實現。
用戶定義的擴展必須注冊到一個由連接到連接的基礎(connection-by-connection basis)之上，存儲在程序內存中。也就是說，它們不是存儲在數據庫中(就像大型數據庫的存儲過程一樣)，而是存儲在你的程序中。
### 創建用戶自定義函數
實現一個用戶自定義的函數分為兩步。首先，寫句柄。句柄實現一些你想通過SQL完成的功能。然后，注冊句柄，為它提供SQL名稱、參數的數量和一個指向句柄的指針。
例如，你想創建一個叫做hello_newman()的SQL函數，它返回文本'Hello Jerry'。在SQLite C API中，先創建一個C函數來實現此功能，如：
```
void hello_newman(sqlite3_context* ctx, int nargs, sqlite3_value** values)
{
??? /* Create Newman's reply */
??? const char *msg = "Hello Jerry";
??? /* Set the return value. Have sqlite clean up msg w/ sqlite_free(). */
??? sqlite3_result_text(ctx, msg, strlen(msg), sqlite3_free);
}
```
不了解C和C API也沒關系。這個句柄僅返回'Hello Jerry'。下面是實際使用它。使用sqlite3_create_function()函數注冊這個句柄：
```
??? sqlite3_create_function(db, "hello_newman", 0, hello_newman);
```
第1個參數(db)是數據庫連接。第2個參數是函數的名稱，這個名稱將出現在SQL中。第3個參數表示這個函數有0個參數(如果該參數值為-1，表示該函數接受可變數量的參數)。最后一個參數是C函數hello_newman()的指針，當SQL函數被調用時，通過這個指針來調用實際的函數。
一旦進行了注冊，SQLite就知道了當遇到SQL函數調用hello_newman()時，它需要調用C函數hello_newman()來得到結果。現在，你可以在程序中執行SELECT hello_newman()語句，它將返回單行單列的文本'Hello Jerry'。
如前所述，很多語言擴展允許用各自的語言來實現用戶自定義的函數。例如，Java、Perl等。不同的語言擴展用不同的方法注冊函數，有些使用其本身語言的函數來完成此項工作，例如，在Ruby中使用block—one。
### 創建用戶自定義聚合
所謂聚合函數，就是那些在結果集中應用于全部記錄，并從中計算一些聚合值的函數。SUM()、COUNT()和AVG()都是SQLite標準聚合函數的例子。
創建用戶自定義聚合需要三步：注冊聚合、實現步進函數(對結果集中的每條記錄調用)、實現定案函數(在所有記錄處理完后調用)。在定案函數中計算最終的聚合值，并做一些必要的清理工作。
### 創建用戶自定義排序法
## 事務
現在，你應該已經對API有了一個較好的了解。你知道了執行SQL命令的不同方法和一些有用的功能函數。但是，只知道API還不夠，事務和鎖與查詢的處理過程是緊密關聯的。查詢永遠只能在事務中完成，事務包含鎖，而如果不清楚自己到底在做什么，鎖則可能會導致問題。根據你是如何使用SQL及如何編碼，你可以控制鎖的類型和持續時間。
第4章圖示了一個特殊的假想：兩個獨立的連接同時執行時導致了死鎖。作為程序員，你還可以從代碼的角度來看待問題，代碼中可能包括處于多種狀態的多個連接，帶有多個語句句柄，而你的代碼可能在你不知情的情況下就持有了EXCLUSIVE鎖，從而使其它連接不能做任何事情。
這就是為什么掌握下面知識很重要：事務和鎖如何工作，它們如何與API結合來處理查詢。理想的目標是，你應該能夠看著你寫的代碼并說出事務處于什么狀態，或者至少能夠發現潛在的問題。本節將介紹事務和鎖背后的運行機制，下一節將介紹如何實際地編碼。
## 事務的生命周期
有一些關于代碼和事務的問題需要關注。首先需要知道哪個對象運行在哪個事務之下。另一個問題是持續時間——一個事務何時開始，何時結束，從哪一點開始影響其它連接？第一個問題與API直接關聯，第二個與SQL的一般概念及SQLite的特殊實現關聯。
一個連接(connection)可以包含多個語句(statement)，而且每個連接有一個與數據庫關聯的B-tree和一個pager。Pager在連接中起著很重要的作用，因為它管理事務、鎖、內存緩沖以及負責崩潰恢復(crash recovery)。當你進行數據庫寫操作時，記住最重要的一件事：在任何時候，只在一個事務下執行一個連接。這回答了第一個問題。
關于第二個問題，一般來說，一個事務的生命周期和語句差不多，你也可以手動結束它。默認情況下，事務自動提交，當然你也可以通過BEGIN..COMMIT手動提交。接下來的問題是事務如何與鎖關聯。
## 鎖的狀態
大多數情況下，鎖的生命周期在事務的生命周期之中。它們不一定同時開始，但總時同時結束。當你結束一個事務時，也會釋放它相關的鎖。或者說，鎖直到事務結束或系統崩潰時才會釋放。如果系統在事務沒有結束的情況下崩潰，那么下一個訪問數據庫的連接會處理這種情況，詳見“鎖與崩潰恢復”一節。
在SQLite中有5種不同的鎖狀態，連接(connection)任何時候都處于其中的一個狀態。圖5-3顯示了鎖的狀態以及狀態的轉換。
圖5-3 SQLite鎖轉換
關于這個圖有以下幾點值得注意：
(1)一個事務可以在UNLOCKED、RESERVED或EXCLUSIVE三種狀態下開始。默認情況下在UNLOCKED時開始。
(2)白色框中的UNLOCKED、PENDING、SHARED和 RESERVED可以在一個數據庫的同一時刻存在。
(3)從灰色的PENDING開始，事情就變得嚴格起來，意味著事務想得到排它鎖(EXCLUSIVE)(注意與白色框中的區別)。
雖然鎖有這么多狀態，但是從體質上來說，只有兩種情況：讀事務和寫事務。
## 讀事務
我們先來看看SELECT語句執行時鎖的狀態變化過程，非常簡單：一個連接執行SELECT語句，觸發一個事務，從UNLOCKED到SHARED，當事務COMMIT時，又回到UNLOCKED，就這么簡單。
那么，當你運行兩個語句時會發生什么呢？這時如何加鎖呢？這依賴于你是否運行在自動提交狀態。考慮下面的例子(為了簡單，這里用了偽碼)：
```
db = open('foods.db')
db.exec('BEGIN')
db.exec('SELECT * FROM episodes')
db.exec('SELECT * FROM episodes')
db.exec('COMMIT')
db.close()
```
由于顯式地使用了BEGIN和COMMIT，兩個SELECT命令在一個事務下執行。第一個exec()執行時，連接處于SHARED，然后第二個exec()執行。當事務提交時，連接又從SHARED回到UNLOCKED狀態，狀態變化如下：
```
UNLOCKED→PENDING→SHARED→UNLOCKED
```
如果沒有BEGIN和COMMIT兩行，兩個SELECT都運行于自動提交狀態，狀態變化如下：
```
UNLOCKED→PENDING→SHARED→UNLOCKED→PENDING→SHARED→UNLOCKED
```
僅僅是讀數據，但在自動提交模式下，卻會經歷兩個加解鎖的循環，太麻煩。而且，一個寫進程可能插到兩個SELECT中間對數據庫進行修改，這樣，你就不能保證第二次能夠讀到同樣的數據了，而使用BEGIN..COMMIT就可以有此保證。
## 寫事務
下面我們來考慮寫數據庫，比如UPDATE。和讀事務一樣，它也會經歷UNLOCKED→PENDING→SHARED的變化過程，但接下來就會看到PENDING鎖是如何起到關口作用的了。
### 保留(RESERVED)狀態
當一個連接(connection)要向數據庫寫數據時，從SHARED狀態變為RESERVED狀態。如果它得到RESERVED鎖，也就意味著它已經準備好進行寫操作了。即使它沒有把修改寫入數據庫，也可以把修改保存到位于pager的緩沖區中(page cache)了。
當一個連接進入RESERVED狀態，pager就開始初始化回卷日志(rollback journal)。回卷日志是一個文件，用于回卷和崩潰恢復，見圖5-1。在RESERVED狀態下，pager管理著三種頁：
(1)已修改的頁：包含被B-tree修改的記錄，位于page cache中。
(2)未修改的頁：包含沒有被B-tree修改的記錄。
(3)日志頁：這是修改頁以前的版本，日志頁并不存儲在page cache中，而是在B-tree修改頁之前寫入日志。
Page cache非常重要，正是因為它的存在，一個處于RESERVED狀態的連接可以真正的開始工作，而不會干擾其它的(讀)連接。所以，SQLite可以高效地處理在同一時刻的多個讀連接和一個寫連接。
### 未決(PENDING)狀態
當一個連接完成修改，需要真正開始提交事務時，執行該過程的pager進入EXCLUSIVE狀態。從RESERVED狀態開始，pager試著獲取PENDING鎖，一旦得到，就獨占它，不允許任何其它連接獲得PENDING鎖。既然寫操作持有PENDING鎖，其它任何連接都不能從UNLOCKED狀態進入SHARED狀態，即不會再有新的讀進程，也不會再有新的寫進程。只有那些已經處于SHARED狀態的連接可以繼續工作。而處于PENDING狀態的寫進程會一直等到所有這些連接釋放它們的鎖，然后對數據庫加EXCUSIVE鎖，進入EXCLUSIVE狀態，獨占數據庫。
### 排它狀態
在EXCLUSIVE狀態下，主要的工作是把修改的頁從page cache寫入數據庫文件，這是真正進行寫操作的地方。
在pager將修改頁寫到文件之前，還必須先處理日志。它檢查是否所有的日志都寫入了磁盤，因為它們可能還位于操作系統的緩沖區中。所以pager得告訴OS把所有的文件寫入磁盤，這與synchronous pragma所做的工作相同，如第4章所述。
日志是數據庫進行恢復的惟一方法，所以日志對于DBMS非常重要。如果日志頁沒有完全寫入磁盤而發生崩潰，數據庫就不能恢復到它原來的狀態，此時數據庫就處于不一致狀態。日志寫盤完成后，pager就把所有的修改頁寫入數據庫文件。接下來做什么取決于事務提交的模式，如果是自動提交，那么pager清理日志、page cache，然后由EXCLUSIVE進入UNLOCKED。如果是手動提交，那么pager繼續持有EXCLUSIVE鎖和回卷日志，直至遇到COMMIT或者ROLLBACK。
總之，出于性能方面的考慮，進程占有排它鎖的時間應該盡可能的短，所以DBMS通常都是在真正寫文件時才會占有排它鎖，這樣能大大提高并發性能。
### 自動提交與效率
## 調整頁緩沖區
回到前面的例子，事務從BEGIN開始，跟著UPDATE。如果在寫盤之前，修改操作將緩沖區用完了(也就是說修改操作需要比預設的更多的緩沖區)，這時會發生什么呢？
### 轉換為排它
真正的問題是：到底在哪個(精確的)時刻，到底為什么，pager 從RESERVED轉換為EXCLUSIVE。這會發生在兩種情況下：當連接到達提交點主動進入排它狀態；或頁緩沖區已滿不得不進入排它狀態。
前面我們僅看到了第1種情況，那么，在第2種情況下會發生什么呢？此時pager已不能再存儲更多的已修改頁，也就不能再做任何修改操作。它必須轉換為排它狀態，以使工作能夠繼續進行。實際上也不完全是這樣，實際上有軟限制和硬限制的區別。
### 調整頁緩沖區的大小
如何決定需要多大的緩沖區尺寸呢？這由你想做什么而定。假設你想修改episodes表的所有記錄，那么該表的所有頁都會被修改，因此，你就可以計算出episodes表總共需要多少個頁并對緩沖區做出調整。可以用sqlite_analyzer到得所有關于episodes表的需要的信息。對每一個表，它都可以做出完備的統計，包括總頁數。例如，對于foods數據庫，可以得到關于episodes表的如下信息：
```
*** Table EPISODES ***************************************************
Percentage of total database.......... 20.0%
Number of entries..................... 181
Bytes of storage consumed............. 5120
Bytes of payload...................... 3229 63.1%
Average payload per entry............. 17.84
Average unused bytes per entry........ 5.79
Average fanout........................ 4.00
Maximum payload per entry............. 38
Entries that use overflow............. 0 0.0%
Index pages used...................... 1
Primary pages used.................... 4
Overflow pages used................... 0
Total pages used...................... 5
Unused bytes on index pages........... 990 96.7%
Unused bytes on primary pages......... 58 1.4%
Unused bytes on overflow pages........ 0
Unused bytes on all pages............. 1048 20.5%
```
總頁數是5，但實際上表只用了4頁，還有1頁是索引。因為默認的緩沖區大小是2000個頁，所以你沒有必要擔心。在episodes表中有400條記錄，也就是說每頁可存放約100條記錄。所以，在修改所有記錄之前你不需要考慮調整頁緩沖區，除非episodes中至少有了196000條記錄。還要記住，你只需要在有其它連接并發使用數據庫的情況下才需要考慮這些，如果只有你自己使用數據庫，這些就都不需要考慮了。
## 等待加鎖
我們前面談到過pager等待從PENDING狀態進入EXCLUSIVE狀態，那么在這個期間到底發生了什么呢？首先，任何exec()或step()的調用都可能進入等待。當SQLite遇到不能獲得鎖的情況時，它的默認表現總是向函數返回一個SQLITE_BUSY并使函數繼續尋求鎖。無論你執行什么命令，都有可能遇到SQLITE_BUSY，包括SELECT命令，都有可能因為有其它的寫進程處于未決狀態而遇到SQLITE_BUSY。當遇到SQLITE_BUSY時，最簡單的選擇是重試。但是，下面我們就會看到這并不一定是最好的選擇。
### 使用“忙”句柄
你可以使用一個忙句柄，而不是一遍遍地重試。忙句柄是一個函數，你創建它用來消磨時間或做其它任何事情——如給岳母發一封郵件(？)。它僅在SQLite不能獲得鎖時被調用。忙句柄必須做的唯一的事是返回一個值，告訴SQLite下一步該做什么。如果它返回TRUE，SQLite將會繼續嘗試獲得鎖；如果它返回FALSE，SQLite將向申請鎖的函數返回SQLITE_BUSY。看下面的例子：
```
counter = 1
def busy()
??? counter = counter + 1
??? if counter == 2
??? ????return 0
??? end
??? spam_mother_in_law(100)
??? return 1
end
db.busy_handler(busy)
stmt = db.prepare('SELECT * FROM episodes;')
stmt.step()
stmt.finalize()
```
spam_mother_in_law()完成一個發郵件功能。
step()函數必須獲得一個SHARED鎖以完成SELECT操作。如果此時有一個寫進程活動，正常情況下step()會返回SQLITE_BUSY。但是，在上面程序中卻不是這樣，而是由pager調用busy()函數，因為它已經被注冊隊忙句柄。busy()函數增加計數，給你岳母發一封郵件，并且返回1，在pager中會被翻譯成true——繼續申請鎖。Pager再次申請獲得SHARED鎖，但數據庫仍然被鎖著，于是pager再次調用busy()函數。只有此時，busy()函數返回0，在pager中會被翻譯成false——返回SQLITE_BUSY。
### 使用正確的事務
## 編碼
現在，你對API、事務和鎖已經有了很好的了解了。最后，我們把這3個內容在代碼中結合到一起。
## 使用多個連接
如果你曾經為其它的關系型數據庫編寫過程序，你就會發現有些適用于那些數據庫的方法不一定適用于SQLite。使用其它數據庫時，經常會在同一個代碼塊中打開多個連接，典型的例子就是在一個連接中返復遍歷一個表而在另一個連接中修改它的記錄。
在SQLite中，在同一個代碼塊中使用多個連接會引起問題，必須小心地對待這種情況。請看下面代碼：
```
c1 = open('foods.db')
c2 = open('foods.db')
stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
??? print stmt.column('name')
??? c2.exec('UPDATE episodes SET …)
end
stmt.finalize()
c1.close()
c2.close()
```
問題很明顯，當c2試圖執行UPDATE時，c1擁有一個SHARED鎖，這個鎖只有等stmt.finalize()之后才會釋放。所以，是不可能成功寫數據庫的。最好的辦法是在一個連接中完成工作，并且在同一個BEGIN IMMEDIATE事務中完成。新程序如下：
```
c1 = open('foods.db')
# Keep trying until we get it
while c1.exec('BEGIN IMMEDIATE') != SQLITE_OK
end
stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
??? print stmt.column('name')
??? c1.exec('UPDATE episodes SET …)
end
stmt.finalize()
c1.exec('COMMIT')
c1.close()
```
在這種情況下，你應該在單獨的連接中使用語句(statement)來完成讀和寫，這樣，你就不必擔心數據庫鎖會引發問題了。但是，這個特別的示例仍然不能工作。如果你在一個語句(statement)中返復遍歷一個表而在另一個語句中修改它的記錄，還有一個附加的鎖問題你需要了解，我們將在下面介紹。
## 表鎖
即使只使用一個連接，在有些邊界情況下也會出現問題。不要認為一個連接中的兩個語句(statements)就能協調工作，至少有一個重要的例外。
當在一個表上執行了SELECT命令，語句對象會在表上創建一個B-tree游標。如果表上有一個活動的B-tree游標，即使是本連接中的其它語句也不能夠再修改這個表。如果做這種嘗試，將會得到SQLITE_BUSY。看下面的例子：
```
c = sqlite.open("foods.db")
stmt1 = c.compile('SELECT * FROM episodes LIMIT 10')
while stmt1.step() do
??? # Try to update the row
??? row = stm1.row()
??? stmt2 = c.compile('UPDATE episodes SET …')
??? # Uh oh: ain't gonna happen
??? stmt2.step()
end
stmt1.finalize()
stmt2.finalize ()
c.close()
```
這里我們只使用了一個連接。但當調用stmt2.step()則不會工作，因為stmt1擁有episodes表的一個游標。在這種情況下，stmt2.step()有可能成功地將鎖升級到EXCLUSIVE，但仍會返回SQLITE_BUSY，因為episodes的游標會阻止它修改表。完成這種操作有兩種方法：
+ 遍歷一個語句的結果集，在內存中保存需要的信息。定案這個讀語句，然后執行修改操作。
+ 將SELECT的結果存到一個臨時表中并用讀游標打開它。這時同時有一個讀語句和一個寫語句，但它們在不同的表上，所以不會影響主表上的寫操作。寫完成后，刪掉臨時表就是了。
當表上打開了一個語句，它的B-tree游標在兩種情況下會被移除：
+ 到達了語句結果集的尾部。這時step()會自動地關閉語句的游標。從VDBE的角度，當到達結果集的尾部時，CDBE遇到Close命令，這將導致所有相關游標的關閉。
+ 程序顯式地調用了finalize()，所有相關游標將關閉。
在很多編程語言擴展中，statement對象的close()函數會自動調用sqlite3_finalize()。
## 有趣的臨時表
臨時表使你可以做到不違反規則。如果你確實需要在一個代碼塊中使用兩個連接，或者使用兩個語句(statement)操作同一個表，你可以安全地在臨時表上如此做。當一個連接創建了一個臨時表，不需要得到RESERVED鎖，因為臨時表存在于數據庫文件之外。有兩種方法可以做到這一點，看你想如何管理并發。請看如下代碼：
```
c1 = open('foods.db')
c2 = open('foods.db')
c2.exec('CREATE TEMPORARY TABLE temp_epsidodes as SELECT * from episodes')
stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
??? print stmt.column('name')
??? c2.exec('UPDATE temp_episodes SET …')
end
stmt.finalize()
c2.exec('BEGIN IMMEDIATE')
c2.exec('DELETE FROM episodes')
c2.exec('INSERT INTO episodes SELECT * FROM temp_episodes')
c2.exec('COMMIT')
c1.close()
c2.close()
```
上面的例子可以完成功能，但不好。episodes表中的數據要全部刪除并重建，這將丟失episodes表中的所有完整性約束和索引。下面的方法比較好：
```
c1 = open('foods.db')
c2 = open('foods.db')
c1.exec('CREATE TEMPORARY TABLE temp_episodes as SELECT * from episodes')
stmt = c1.prepare('SELECT * FROM temp_episodes')
while stmt.step()
??? print stmt.column('name')
??? c2.exec('UPDATE episodes SET …') # What about SQLITE_BUSY?
end
stmt.finalize()
c1.exec('DROP TABLE temp_episodes')
c1.close()
c2.close()
```
## 定案的重要性
使用SELECT語句必須要意識到，其SHARED鎖(大多數時候)直到finalize()被調用后才會釋放，請看下面代碼：
```
stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
??? print stmt.column('name')
end
c2.exec('BEGIN IMMEDIATE; UPDATE episodes SET …; COMMIT;')
stmt.finalize()
```
如果你用C API寫了與上例等價的程序，它實際上是能夠工作的。盡管沒有調用finalize()，但第二個連接仍然能夠修改數據庫。在告訴你為什么之前，先來看第二個例子：
```
c1 = open('foods.db')
c2 = open('foods.db')
stmt = c1.prepare('SELECT * FROM episodes')
stmt.step()
stmt.step()
stmt.step()
c2.exec('BEGIN IMMEDIATE; UPDATE episodes SET …; COMMIT;')
stmt.finalize()
```
假設episodes中有100條記錄，程序僅僅訪問了其中的3條，這時會發生什么情況呢？第2個連接會得到SQLITE_BUSY。
在第1個例子中，當到達語句結果集尾部時，會釋放SHARED鎖，盡管還沒有調用finalize()。在第2個例子中，沒有到達語句結果集尾部，SHARED鎖沒有釋放。所以，c2不能執行UPDATE操作。
這個故事的中心思想是：不要這么做，盡管有時這么做是可以的。在用另一個連接進行寫操作之前，永遠要先調用finalize()。
## 共享緩沖區模式
現在你對并發規則已經很清楚了，但我還要找些事來擾亂你。SQLite提供一種可選的并發模式，稱為共享緩沖區模式，它允許在單一的線程中操作多個連接。
在共享緩沖區模式中，一個線程可以創建多個連接來共享相同的頁緩沖區。進而，這組連接可以有多個“讀”和一個“寫”同時工作于相同的數據庫。緩沖區不能在線程間共享，它被嚴格地限制在創建它的線程中。因此，“讀”和“寫”就需要準備處理與表鎖有關的一些特殊情況。
當 readers讀表時， SQLite自動在這些表上加鎖，writer就不能再改這些表了。如果writer試圖修改一個有讀鎖的表，會得到SQLITE_LOCKED。如果readers運行在read-uncommitted模式(通過read_uncommitted pragma來設置)，則當readers讀表時，writer也可以寫表。在這種情況下，SQLite不為readers所讀的表加讀鎖，結果就是readers和writer互不干擾。也因此，當一個writer修改表時，這些readers可能得到不一致的結果。
