sqlite的事務和鎖，很透徹的講解 【轉】

標籤：提升   準則   介面   blank   共存   成功   並發   rom   語句   

原文：sqlite的事務和鎖 http://3y.uu456.com/bp-877d38906bec097sf46se240-1.html

事務

事務定義了一組SQL命令的邊界，這組命令或者作為一個整體被全部執行，或者都不執行。事務的典型執行個體是轉帳。 事務的範圍

事務由3個命令控制：BEGIN、COMMIT和ROLLBACK。BEGIN開始一個事務，之後的所有操作都可以取消。COMMIT使BEGIN後的所有命令得到確認；而ROLLBACK還原BEGIN之後的所有操作。如：

sqlite> BEGIN;

sqlite> DELETE FROM foods;

sqlite> ROLLBACK;

sqlite> SELECT COUNT(*) FROM foods;

COUNT(*)

412

上面開始了一個事務，先刪除了foods表的所有行，但是又用ROLLBACK進行了回卷。再執行SELECT時發現表中沒發生任何改變。

SQLite預設情況下，每條SQL語句自成事務(自動認可模式)。

衝突解決

如前所述，違反約束會導致事務的非法結束。大多數資料庫(管理系統)都是簡單地將前面所做的修改全部取消。 SQLite有其獨特的方法來處理約束違反(或說從約束違反中恢複)，被稱為衝突解決。

如：

sqlite> UPDATE foods SET id=800-id;

SQL error: PRIMARY KEY must be unique

SQLite提供5種衝突解決方案：REPLACE、IGNORE、FAIL、ABORT和ROLLBACK。

REPLACE: 當發違反了唯一完整性，SQLite將造成這種違反的記錄刪除，替代以新插入或修改的新記錄，SQL繼續執行，不報錯。

IGNORE

FAIL

ABORT

ROLLBACK

資料庫鎖

在SQLite中，鎖和事務是緊密聯絡的。為了有效地使用事務，需要瞭解一些關於如何加鎖的知識。

SQLite採用粗放型的鎖。當一個串連要寫資料庫，所有其它的串連被鎖住，直到寫串連結束了它的事務。SQLite有一個加鎖表，來協助不同的寫資料庫都能夠在最後一刻再加鎖，以保證最大的並發性。

SQLite使用鎖逐步上升機制，為了寫資料庫，串連需要逐級地獲得排它鎖。SQLite有5個不同的鎖狀態：未加鎖

(UNLOCKED)、共用 (SHARED)、保留(RESERVED)、未決(PENDING)和排它(EXCLUSIVE)。每個資料庫連接在同一時刻只能處於其中一個狀態。每 種狀態(未加鎖狀態除外)都有一種鎖與之對應。

最初的狀態是未加鎖狀態，在此狀態下，串連還沒有存取資料庫。當串連到了一個資料庫，甚至已經用BEGIN開始了一個事務時，串連都還處於未加鎖狀態。

未加鎖狀態的下一個狀態是共用狀態。為了能夠從資料庫中讀(不寫)資料，串連必須首先進入共用狀態，也就是說首先要獲得一個共用鎖定。多個串連可以 同時獲得並保持共用鎖定，也就是說多個串連可以同時從同一個資料庫中讀資料。但哪怕只有一個共用鎖定還沒有釋放，也不允許任何串連寫資料庫。

如果一個串連想要寫資料庫，它必須首先獲得一個保留鎖。一個資料庫上同時只能有一個保留鎖。保留鎖可以與共用鎖定

共存，保留鎖是寫資料庫的第1階段。保留鎖即不阻止其它擁有共用鎖定的串連繼續讀資料庫，也不阻止其它串連獲得新的共用鎖定。

一旦一個串連獲得了保留鎖，它就可以開始處理資料庫修改操作了，儘管這些修改只能在緩衝區中進行，而不是實際地寫到磁碟。對讀出內容所做的修改儲存在記憶體緩衝區中。

當串連想要提交修改(或事務)時，需要將保留鎖提升為排它鎖。為了得到排它鎖，還必須首先將保留鎖提升為未決鎖。獲得未決鎖之後，其它串連就不能 再獲得新的共用鎖定了，但已經擁有共用鎖定的串連仍然可以繼續正常讀資料庫。此時，擁有未決鎖的串連等待其它擁有共用鎖定的串連完成工作並釋放其共用鎖定。

一旦所有其它共用鎖定都被釋放，擁有未決鎖的串連就可以將其鎖提升至排它鎖，此時就可以自由地對資料庫進行修改了。所有以前對緩衝區所做的修改都會被寫到資料庫檔案。

死結

為什麼需要瞭解鎖的機制呢？為了避免死結。

考慮下面表4-7所假設的情況。兩個串連——A和B——同時但完全獨立地工作於同一個資料庫。A執行第1條命令，B執行第2、3條，等等。

表4-7 一個死結的假設情況

A串連 B串連

sqlite> BEGIN;

sqlite> BEGIN;

sqlite> INSERT INTO foo VALUES(‘x’);

sqlite> SELECT * FROM foo;

sqlite> COMMIT;

SQL error: database is locked

sqlite> INSERT INTO foo VALUES (‘x’);

SQL error: database is locked

兩個串連都在死結中結束。B首先嘗試寫資料庫，也就擁有了一個未決鎖。A再試圖寫，但當其INSERT語句試圖將共用鎖定提升為保留鎖時失敗。

為了討論的方便，假設串連A和B都一直等待資料庫可寫。那麼此時，其它的串連甚至都不能夠再讀資料庫了，因為B擁有未決鎖(它能阻止其它串連獲得共用鎖定)。那麼時此，不僅A和B不能工作，其它所有進程都不能再操作此資料庫了。 如果避免此情況呢？當然不能讓A和B通過談判解決，因為它們甚至不知道彼此的存在。答案是採用正確的事務類型來完成工作。

事務的種類

SQLite有三種不同的事務，使用不同的鎖狀態。事務可以開始於：DEFERRED、MMEDIATE或EXCLUSIVE。事務類型在BEGIN命令中指定：

BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION;

一個DEFERRED事務不擷取任何鎖(直到它需要鎖的時候)，BEGIN語句本身也不會做什麼事情——它開始於UNLOCK狀態。預設情況下就 是這樣的，如果僅僅用BEGIN開始一個事務，那麼事務就是DEFERRED的，同時它不會擷取任何鎖；當對資料庫進行第一次讀操作時，它會擷取 SHARED鎖；同樣，當進行第一次寫操作時，它會擷取RESERVED鎖。

由BEGIN開始的IMMEDIATE事務會嘗試擷取RESERVED鎖。如果成功，BEGIN IMMEDIATE保證沒有別的串連可以寫資料庫。但是，別的串連可以對資料庫進行讀操作；但是，RESERVED鎖會阻止其它串連的BEGIN IMMEDIATE或者BEGIN EXCLUSIVE命令，當其它串連執行上述命令時，會返回SQLITE_BUSY錯誤。這時你就可以對資料庫進行修改操作了，但是你還不能提交，當你 COMMIT時，會返回SQLITE_BUSY錯誤，這意味著還有其它的讀事務沒有完成，得等它們執行完後才能提交事務。

EXCLUSIVE事務會試著擷取對資料庫的EXCLUSIVE鎖。這與IMMEDIATE類似，但是一旦成功，EXCLUSIVE事務保證沒有其它的串連，所以就可對資料庫進行讀寫操作了。

上節那個例子的問題在於兩個串連最終都想寫資料庫，但是它們都沒有放棄各自原來的鎖，最終，SHARED鎖導致了問題的出現。如果兩個串連都以 BEGIN IMMEDIATE開始事務，那麼死結就不會發生。在這種情況下，在同一時刻只能有一個串連進入BEGIN IMMEDIATE，其它的串連就得等待。BEGIN IMMEDIATE和BEGIN EXCLUSIVE通常被寫事務使用。就像同步機制一樣，它防止了死結的產生。

基本的準則是：如果你正在使用的資料庫沒有其它的串連，用BEGIN就足夠了。但是，如果你使用的資料庫有其它的串連也會對資料庫進行寫操作，就得使用BEGIN IMMEDIATE或BEGIN EXCLUSIVE開始你的事務。

sqlite3使用交易處理[zz]

在對 sqlite3 insert into 等操作時速度比較慢。

原因：它以檔案的形式存在磁碟中，每次訪問時都要開啟一次檔案，如果對資料庫進行大量的操作，就很慢。

解決辦法：用事物的形式提交，因為開始事務後，進行的大量動作陳述式都儲存在記憶體中，當提交時才全部寫入資料庫，此時，資料庫檔案也只用開啟一次。如果操作錯誤，還可以復原事務。

介面：事務的操作沒有特別的介面函數，就是一個普通的 sql 語句而已，分別如下：

int ret ;

ret = sqlite3_exec ( db , “begin transaction” , 0 , 0 , & zErrorMsg ); // 開始一個事務

ret = sqlite3_exec ( db , “commit transaction” , 0 , 0 , & zErrorMsg ); // 提交事務

ret = sqlite3_exec ( db , “rollback transaction” , 0 , 0 , & zErrorMsg );

常式：在進行大量的操作前使用如下語句

ret = sqlite3_exec ( db , “begin transaction” , 0 , 0 ,& zErrorMsg );

for (…)

{

//insert into operate

// 如果操作錯誤

ret = sqlite3_exec ( db , “rollback transaction” , 0 , 0 , & zErrorMsg )

}

ret = sqlite3_exec ( db , “commit transaction” , 0 , 0 , & zErrorMsg );

開發過程遇到這樣的問題：

分別對兩個資料庫檔案的不同表進行操作，執行順序為： open db A->begin trasaction->open db B->select from db B->close db B->select from db A->rollbak or commit->close db A

測試發現， select from db B 這一步會出錯，錯誤資訊為library routine called out of sequence ，出錯後執行 rollback ，這一步也會報錯。

原來以為原因是：開始一個事務只能對一個資料庫進行操作。

測試發現，即使不開始事務，執行順序為： open db A->open db B->select from db B->close db B->select from db A->close db A ， 仍會出現ibrary routine called out of sequence 的錯誤。

sqlite的事務和鎖，很透徹的講解 【轉】