sqlserver 並發處理中基礎–事物和鎖的使用

 

一. 事務（Transaction） 
事務主要是考慮到在異常情況下資料的安全效能和正確性。例如一個轉賬程式，有若干個語句，分別執行不同的功能，現在從第一個賬戶取出款項，正好此時因為其他原因導致程式中斷，這樣，第二個賬戶沒有收到款項，而第一個賬戶的錢也沒有了，這樣明顯是錯誤的。為瞭解決這種類似的情況，DBMS中提出了事務的概念。事務對上面的解決方式是：把上面的提取和轉入作為一個整體，形成一個操作集合，這個集合中的操作要麼都不執行，要麼都執行！因此事務具有“原子性”，事務作為一個整體是不可分割的。 

一般地，SQL會隱性地開始事務，當然你也可以顯式地開始事務。但是事務的結束必須是顯式的，有兩種方法可以結束事務： 

1. Commit(提交)。如果你認為所有的操作都完成了的話，可以結束事務，可以向系統對事物進行提交，提交之後，所有的修改都會生效了，在沒有提交之前，所有的修改都可以作廢的。 

2. Rollback(復原)。復原會結束當前事務，並且放棄自事務開始以來所有的操作，回到事務開始的狀態。需要注意的是，可以在事物之內設定一些保留點（Save Point），這樣可以不必放棄整個事務，可以根據需要Rollback到這個保留點。 

我們知道，Word和很多的軟體都有Undo功能，事務其實和Undo的功能很類似！那麼它的工作原理是什麼呢？原來，在DBMS中，事務開始的時候，從這個時候記錄你的每一個操作的資料、類型、對象，每一個操作對應一個相應的記錄，當事務成功完成的時候，清除這些記錄就可以了，如果出現異常，事務失敗，那麼可以倒過來，把我們儲存得記錄都作一次逆操作即可。如一個例子：有一個事務，其中包含 

(1) 刪除第一條記錄 

(2) 添加一個新的記錄 

(3) 修改了第5條記錄 

(4) ... 

三個步驟，那麼DBMS在事務開始時候，分別記錄每一個操作的資訊： 

(1) 刪除，第一條，記錄資料 

(2) 添加，記錄資料，假設得到一個記錄號8 

(3) 修改、修改前的資料，修改後的資料 

如果在事務執行的過程中出現錯誤，假設在第4句出現錯誤，這個時候事務需要復原，DBMS就會執行下面的動作： 

1. 修改第5條記錄，用修改前的資料代替修改後的資料 

2. 刪除第8條記錄 

3. 添加原來第一條記錄 

這樣，資料就能回到事務開始前的狀態，這也是Word等軟體Undo的原理。 

在SQL Server中，很多語句會自動隱性開始事務，那麼如何顯式開始事務呢？其命令格式如下： 

BEGIN TRAN [Tran_Name] 

前面說過可以給事務設定一個Save Point，其命令如下： 

SAVE TRAN SavePointName 

最後，事務可以提交或者復原，其格式分別如下： 

提交：COMMIT TRAN [Tran_Name] 

復原：ROLLBACK TRAN [Tran_Name | SavePointName] 

上面的Tran也可以寫成TRANSACTION。 

例：修改0000000學生的學號為0001156。前面我們學習過一個利用觸發起來防止學號不一致的題目，這次我們利用事務來進行處理。 

--本處的事務是為了保證資料（學號）的一致性 

BEGIN TRAN MyTran /* 開始一個事務 */ 

UPDATE Score /* 更新Score表 */ 

SET S_No='0001156' 

WHERE S_No='0000000' 

IF @@ERROR<>0 

BEGIN 

PRINT 'An Error occur During UPDATE the table [Score]' 

ROLLBACK TRAN 

RETURN 

END 

UPDATE IDInfo /* 更新IdInfo表 */ 

SET S_No='0001156' 

WHERE S_No='0000000' 

IF @@ERROR<>0 /* 檢測是否成功更新，@@ERROR返回上一個SQL語句狀態 */ 

BEGIN 

PRINT 'An Error occur During UPDATE the table [IdInfo]' 

ROLLBACK TRAN /* 復原 */ 

RETURN /* 退出本過程 */ 

END 

ELSE 

COMMIT TRAN MyTran /* 提交事務 */ 

二. 並發控制 
封鎖(Locking) 
並發控制主要是考慮到多個使用者同時存取資料庫中的同一資料的情況。例如：飛機票的兩個售票員A和B，現在系統裡面有10張機票，A和B此時查看資料庫裡面，都得到10張，此時A賣出去一張，寫回資料庫數量為9張，接著B也賣出去一張，因為他以前讀取的是10張，他因此他也寫回9，這樣就出現了錯誤，實際上只有8張票了！這個問題稱之為“寫覆蓋”。經過分析和研究，有以下幾個並發控制的情況（我們不考慮兩個事務都“讀”的情況，那樣是不需要考慮的）： 

T1 
T2 
T1 
T2 
T1 
T2 

① 
Read A=10 

Read A=50 

Read B=100 

A+B=150 

Read A=100 

A=A*2 

Write A(200) 

② 

Read A=10 

Read B=100 

B=B*2 

Write B 

Read A=200 

③ 
A=A-1 

Write A(9) 

Read A=50 

Read B=200 

A+B=250 

驗算（不正確） 

Rollback Tran 

A=100 

④ 

A=A-1 

Write A(9) 

寫覆蓋（修改丟失） 
不能重複讀 
讀“髒資料” 

在資料庫中有一個特定的名詞“髒資料”，用來描述那些被某些事務變更但是還沒有提交的資料。 

那麼如何解決使用者同時訪問資料的問題呢？總不能因為並發而限制使用者的操作吧！並發控制的解決方案是“鎖（LOCKING）”和事務。事務是並發控制的基本單位。事務不等於程式，一個程式可以包含多個事務。下面我們來詳細討論封鎖機制。 

從資料庫的角度來看，鎖有兩種類型：排它鎖(Exclusive locks，簡稱X鎖)和共用鎖定（Share locks，簡稱S鎖）。X鎖只允許加鎖的事務進行操作，其他事務禁止加鎖和任何操作（讀、寫），其他事務必須等待解鎖才能繼續運行！S鎖可以允許多個事務同時對資料加鎖，如果事務T對資料R加了S鎖，那麼其它的事務就不能再對R加X鎖，但是可以加S鎖，這樣可以保證其他事務不能修改R。另外，還有一個加鎖的範圍需要考慮，我們可以進行行加鎖，也可以進行表加鎖，甚至可以進行資料庫加鎖，加鎖的範圍越大，那麼實現就越簡單，開銷就越小，資料的並發程式就越低！反之，如果加鎖範圍越小，那麼實現就越複雜，開銷就越大，資料的並發程式就越高。一般地考慮到加鎖的成本和效能，處理少量資料的事務應該儘可能減少加鎖的作用範圍，提高資料的並發程度，應該採用行鎖，防止則應該採用表鎖等等。另外，當一個事務操作完畢的時候，應儘可能快的解鎖。在SQL Server中，還有一種更新鎖定(U鎖)，這種鎖和S鎖是相容的，如果一個事務要更新資料可以採取U鎖，那麼在事務初期可能是讀取資料，此時是S鎖，到後面，資料進行了修改，這個時候S鎖自動升級為X鎖。在SQL Server中，是自動強制鎖定的，但是我們應該學習以便能夠有效設計應用程式。一般的情況下面，我們這樣考慮：讀取採用S鎖，寫入採取X鎖。 

如果從程式員的角度來看，可以把鎖分成兩種：樂觀鎖(optimistic Lock)和悲觀鎖（Pessimistic Lock）。樂觀鎖就是在處理資料的時候，完全由資料庫系統來自動實行加鎖的管理，從前面我們知道，SQL Server採取的是樂觀鎖：對於Update,Insert,Delete自動採用X鎖，對於Select，自動採用S鎖；悲觀鎖則需要程式員自己來控制加鎖、解鎖的動作。 

下面讓我們來看看，如何利用鎖來解決前面的衝突： 

T1 
T2 
T1 
T2 
T1 
T2 

① 
XLock A 

Read A=10 

SLock A 

SLock B 

Read A=50 

Read B=100 

A+B=150 

XLock A 

Read A=100 

A=A*2 

Write A(200) 

② 

Request 

XLock A 

Waiting... 

Request 

XLock B 

Waiting... 

Request 

SLock A 

Waiting... 

③ 
A=A-1 

Write A(9) 

Commit 

XUnlock A 
waiting... 
Read A=50 

Read B=100 

A+B=150 

驗算（OK） 

Commit 

SUnLock A 

SUnLock B 
Waiting... 
Rollback Tran 

A=100 

XUnlock A 
Waiting... 

④ 

XLock A 

Read A=9 

A=A-1 

Write A(8) 

Commit 

XUnlock A 
XLock B 

Read B=100 

B=B*2 

Write B=200 

Commit 

XUnLock B 

SLock A 

Read A=100 

Commit 

SUnLock A 

寫覆蓋（修改丟失） 
不能重複讀 
讀“髒資料” 

使用鎖的時候，請注意一定要遵守下面兩個事項：(1) 先鎖後操作；(2) 事務結束之後必須解鎖。最後總結一下如何利用鎖來解決上面的三個問題（三級封鎖協議）： 

l 1級封鎖協議—---對事務T要修改的資料R加X鎖，直到事務結束，防止“寫覆蓋”並且保證T是可以恢複的。 

l 2級封鎖協議----1級封鎖協議加上對T要讀取的資料R加S鎖，防止讀“髒資料” 

l 3級封鎖協議----1級封鎖協議加上對T要讀取的資料R加S鎖，直到事務結束，可以解決重複讀的問題。 

SQL Server是自動實現鎖的，但是有的時候需要手動調整鎖的層級，那麼如何做呢？在SQL Server和Delphi中，都是採用隔離等級(Isolation Level)來實現的。在SQL Server 中有以下四種隔離級： 

1. READ COMMITTED 
和S鎖類似，在此隔離級下SELECT 命令不會返回尚未提交的資料，也不能返回髒資料，它是SQL Server 預設的隔離級； 

2. READ UNCOMMITTED 
與READ COMMITTED 隔離級相反，它允許讀取已經被其它使用者修改但尚未提交確定的資料，限制層級最小； 

3. REPEATABLE READ 
在此隔離級下用SELECT 命令讀取的資料在整個命令執行過程中不會被更改，其他事務不能執行Update和Delete，但是可以Insert。此選項會影響系統的效能，非必要情況最好不用此隔離級； 

4. SERIALIZABLE 
這是最大的限制，和X鎖類似，不允許其他事務進行任何寫訪問。如非必要，不要使用這個選項。 

隔離級需要使用SET 命令來設定，其文法如下： 

SET TRANSACTION ISOLATION LEVEL 
{READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE} 

在事務的開始使用這個命令即可，該隔離等級一直對該SQL Server串連（不是本事務）有效，直到下一次使用本命令設定了新的隔離等級為止。 

活鎖和死結 
下面我們來討論關於鎖的特殊情況： 

假設T1要更新R1和R2，首先它Lock了R1，而另外一個事務T2也要更新R2和R1，他首先鎖定了R2，這個時候，T1要鎖定R2必須等待，而T2也要鎖定R1，這個時候也必須等待，這樣T1和T2互相等待對方解鎖，造成了死迴圈，這個就稱之為“死結”。 

再來看另外一個情況：T1鎖定R，T2請求封鎖R，這個時候必須等待，T3也請求封鎖R，T1解鎖之後，批准了T3的請求，於是T2必須等待，然後T3請求封鎖R，T3解鎖之後，批准了T4的請求，於是T2繼續等待，...這樣可能導致T2無限等待，這個就稱之為“活鎖”，活鎖的解決比較簡單，採取先來先服務策略即可。 

死結一般可以採取如下的策略： 

1. 一次性對事務鎖有請求的資料進行加鎖，否則事務不能運行，缺點：降低了並發度； 

2. 預先規定一個封鎖順序，所有事務按照一定的順序進行加鎖； 

3. 不採取任何措施進行預防，而是檢測是否有死結和拆除死結的方法。 

關於死結在作業系統的課程中有詳細的討論。我們應該儘可能降低死結的可能性：事務儘可能簡短；避免在事務中和使用者互動；盡量使用低層級的隔離等級等等。 

SQL Server中，對於死結採取檢測和拆除的方式：如果系統檢測到有死結，會選擇一個事務作為犧牲者，該事務會自動終止並復原，並且系統返回一個1025的錯誤給應用程式，任意一個程式有可能被系統作為犧牲品，因此，任意一個程式都應該處理1025錯誤，否則有可能你的應用程式不能正常運行。Oracle採取同樣的策略。一般地，SQL Server會選擇撤銷花費代價最少的事務作為犧牲品。如果在SQL Server中要指定死結時本事務的層級，可以使用如下的命令： 

SET DEADLOCK_PRIORITY {LOW | NORMAL} 

Low 表示如果發生死結，那麼當前事務作為首選的犧牲品，Normal表死按照正常的處理方式進行處理。前面討論過，如果請求鎖定，不能滿足請求的時候，事務會進行等待，等待是不能無限期的，我們可以設定一個等待的時間，等待超過指定的時間之後，我們就認為可能發生了死結，事物就自動復原，鎖定逾時可以採用下面的命令來設定： 

SET LOCK_TIMEOUT {–1 | 0 | n} 

-1表示無限期等待，預設；0表示根本不等待；n則表示等待n毫秒，如果等待n毫秒之後還不能鎖定成功，則返回鎖定錯誤。 

另外@@LOCK_TIMEOUT表示返回當前的鎖逾時設定。例如我們可以用SELECT @@Lock_Timeout來查看。