一、事務

1.1 事務的概念

　　事務是作為單個工作單元而執行的一系列操作，比如查詢和修改資料等。

　　事務是資料庫并發控制的基本機關，一條或者一組語句要麼全部成功，對資料庫中的某些資料成功修改; 要麼全部不成功，資料庫中的資料還原到這些語句執行之前的樣子。

比如網上訂火車票，要麼你定票成功，餘票顯示就減一張; 要麼你定票失敗擷取取消訂票，餘票的數量還是那麼多。不允許出現你訂票成功了，餘票沒有減少或者你取消訂票了，餘票顯示卻少了一張的這種情況。這種不被允許出現的情況就要求購票和餘票減少這兩個不同的操作必須放在一起，成為一個完整的邏輯鍊，這樣就構成了一個事務。

1.2 事務的ACID特性

　　原子性(Atomicity)：事務的原子性是指一個事務中包含的一條語句或者多條語句構成了一個完整的邏輯單元，這個邏輯單元具有不可再分的原子性。這個邏輯單元要麼一起送出執行全部成功，要麼一起送出執行全部失敗。

　　一緻性(Consistency)：可以了解為資料的完整性，事務的送出要確定在資料庫上的操作沒有破壞資料的完整性，比如說不要違背一些限制的資料插入或者修改行為。一旦破壞了資料的完整性，SQL Server 會復原這個事務來確定資料庫中的資料是一緻的。

　　隔離性(Isolation)：與資料庫中的事務隔離級别以及鎖相關，多個使用者可以對同一資料并發通路而又不破壞資料的正确性和完整性。但是，并行事務的修改必須與其它并行事務的修改互相獨立，隔離。但是在不同的隔離級别下，事務的讀取操作可能得到的結果是不同的。

　　持久性(Durability)：資料持久化，事務一旦對資料的操作完成并送出後，資料修改就已經完成，即使服務重新開機這些資料也不會改變。相反，如果在事務的執行過程中，系統服務崩潰或者重新開機，那麼事務所有的操作就會被復原，即回到事務操作之前的狀态。

在極端斷電或者系統崩潰的情況下，一個發生在事務未送出之前，資料庫應該記錄了這個事務的"ID"和部分已經在資料庫上更新的資料。供電恢複資料庫重新啟動之後，這時完成全部撤銷和復原操作。如果在事務送出之後的斷電，有可能更改的結果沒有正常寫入磁盤持久化，但是有可能丢失的資料會通過事務日志自動恢複并重新生成以寫入磁盤完成持久化。

1.3 如何定義事務　

　　（1）顯示定義：以BEGIN TRAN開始，送出的話則COMMIT送出事務，否則以ROLLBACK復原事務。

--定義事務
BEGIN TRAN;
  INSERT INTO dbo.T1(keycol, col1, col2) VALUES(4,101,'C');
  INSERT INTO dbo.T1(keycol, col1, col2) VALUES(4,201,'X');
COMMIT TRAN;

　　（2）隐式定義：SQL Server中預設把每個單獨的語句作為一個事務。

　　換句話說，SQL Server預設在執行完每個語句之後就自動送出事務。當然，我們可以通過IMPLICIT_TRANSACTIONS會話選項來改變SQL Server處理預設事務的方式，該選項預設情況下是OFF。如果将其設定為ON，那麼就不必用BEGIN TRAN語句來表明事務開始，但仍然需要以COMMIT或ROLLBACK來标明事務完成。　

二、鎖定和阻塞

2.1 鎖

　　（1）鎖是什麼鬼？

　　鎖是事務擷取的一種控制資源，用于保護資料資源，防止其他事務對資料進行沖突的或不相容的通路。

　　（2）鎖模式及其相容性

　　主要有兩種主要的鎖模式—排它鎖（Exclusive Lock）和共享鎖（Shared Lock）。

　　當試圖修改資料時，事務會為所依賴的資料資源請求排它鎖，一旦授予，事務将一直持有排它鎖，直至事務完成。在事務執行過程中，其他事務就不能再獲得該資源的任何類型的鎖。

　　當試圖讀取資料時，事務預設會為所依賴的資料資源請求共享鎖，讀操作一完成，就立即釋放共享鎖。在事務執行過程中，其他事務仍然能夠獲得該資源的共享鎖。

請求模式	已經授予排它鎖（X）	已經授予共享鎖（S）
授予請求的排它鎖？	否
授予請求的共享鎖？		是

　　（3）可鎖定資源的類型

　　SQL Server可以鎖定不同類型或粒度的資源，這些資源類型包括RID或KEY（行），PAGE（頁）、對象（例如：表）及資料庫等。

2.2 阻塞

　　（1）阻塞是個什麼鬼？

　　如果一個事務持有某一資料資源上的鎖，而另一事務請求相同資源上的不相容的鎖，則對新鎖的請求将被阻塞，送出請求的事務進入等待狀态。預設情況下，被阻塞的請求會一直等待，直到原來的事務釋放相關的鎖。

隻要能夠在合理的時間範圍内滿足請求，系統中的阻塞就是正常的。但是，如果一些請求等待了太長時間，可能就需要手工排除阻塞狀态，看看能采取什麼措施來防止這樣長時間的延遲。　　

　　（2）近距離觀測阻塞

　　Step1.打開兩個獨立的查詢視窗，這裡稱之為Connection A,Connection B

　　Step2.在Connection A中運作以下代碼（這裡productid=2的unitprice本來為19）

BEGIN TRAN;
  UPDATE Production.Products SET unitprice=unitprice+1.00
  WHERE productid=2;

　　為了更新這一行，會話必須先獲得一個排它鎖，如果更新成功，SQL Server會向會話授予這個鎖。

　　Step3.在Connection B中運作以下代碼

SELECT productid, unitprice
FROM Production.Products
WHERE productid=2;

　　預設情況下，該會話需要一個共享鎖，但因為共享鎖和排它鎖是不相容的，是以該會話被阻塞，進入等待狀态。

　　（3）如何檢測阻塞

　　假設我們的系統裡邊出現了阻塞，而且被阻塞了很長時間，如何去檢測和排除呢？

　　① 繼續上例，打開一個新的會話，稱之為Connection C，查詢動态管理視圖（DMV）sys.dm_tran_locks:

-- Lock info
SELECT -- use * to explore
  request_session_id            AS spid,
  resource_type                 AS restype,
  resource_database_id          AS dbid,
  DB_NAME(resource_database_id) AS dbname,
  resource_description          AS res,
  resource_associated_entity_id AS resid,
  request_mode                  AS mode,
  request_status                AS status
FROM sys.dm_tran_locks;

　　② 運作上面的代碼，可以得到以下輸出：

　　③ 每個會話都有唯一的伺服器程序辨別符（SPID），可以通過查詢@@SPID函數來檢視會話ID。另外，目前會話的SPID還可以在查詢視窗的标題欄中找到。

　　④ 在前面查詢的輸出中，可以觀察到程序53正在等待請求TSQLFundamental2008資料庫中一個行的共享鎖。但是，程序52持有同一個行上的排它鎖。沿着52和53的所層次結構向上檢查：（查詢sys.dm_exec_connections的動态管理視圖，篩選阻塞鍊中涉及到的那些SPID）

-- Connection info
SELECT -- use * to explore
  session_id AS spid,
  connect_time,
  last_read,
  last_write,
  most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id IN(52, 53);

　　查詢結果輸出如下：

　　⑤ 借助交叉聯接，和sys.dm_exec_sql_text表函數生成查詢結果：

-- SQL text
SELECT session_id, text 
FROM sys.dm_exec_connections
  CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST 
WHERE session_id IN(52, 53);

　　查詢結果如下，我們可以達到阻塞鍊中涉及到的每個聯接最後調用的批處理代碼：

　　以上就顯示了程序53正在等待的執行代碼，因為這是該程序最後執行的一個操作。對于阻塞程序來說，通過這個例子能夠看到是哪條語句導緻了問題。

　　（4）如何解除阻塞

　　① 設定逾時時間

　　首先取消掉原來Connection B中的查詢，然後執行以下代碼：這裡我們限制會話等待釋放鎖的時間為5秒

-- Session B
SET LOCK_TIMEOUT 5000;

SELECT productid, unitprice
FROM Production.Products
WHERE productid=2;

　　然後5秒之後我們可以看到以下執行結果：

　　注意：鎖定逾時不會引發事務復原。

　　② KILL掉引起阻塞的程序

　　在Connection C中執行以下語句，終止SPID=52中的更新事務而産生的效果，于是SPID=52中的事務的復原，同時釋放排它鎖。

--KILL SPID=52
KILL 52;

　　這時再在Connection B中執行查詢，便可以查到復原後的結果（仍然是19）：

三、隔離級别

　　隔離級别用于決定如何控制并發使用者讀寫資料的操作。前面說到，讀操作預設使用共享鎖，寫操作需要使用排它鎖。對于操作獲得的鎖，以及鎖的持續時間來說，雖然不能控制寫操作的處理方式，但可以控制讀操作的處理方式。作為對讀操作的行為進行控制的一種結果，也會隐含地影響寫操作的行為方式。

　　為此，可以在會話級别上用會話選項來設定隔離級别，也可以在查詢級别上用表提示（Table Hint）來設定隔離級别。

　　在SQL Server中，可以設定的隔離級别有6個：READ UNCOMMITED（未送出讀）、READ COMMITED（已送出讀）、REPEATABLE READ（可重複讀）、SERIALIZEABLE（可序列化）、SNAPSHOT（快照）和READ COMMITED SNAPSHOT（已經送出讀隔離）。最後兩個SNAPSHOT和READ COMMITED SNAPSHOT是在SQL Server 2005中引入的。

　　要設定整個會話級别的隔離級别，可以使用以下語句：

SET TRANSACTION ISOLATION LEVEL <isolation name>;

　　也可以使用表提示來設定查詢級别的隔離級别：

SELECT ... FROM <table> WITH <isolation name>;

3.1 READ UNCOMMITED 未送出讀

　　未送出讀是最低的隔離級别，讀操作不會請求共享鎖。換句話說，在該級别下的讀操作正在讀取資料時，寫操作可以同時對這些資料進行修改。

　　同樣，使用兩個會話來模拟：

　　Step1.在Connection A中運作以下代碼，更新産品2的單價，為目前值（19.00）增加1.00，然後查詢該産品：

-- Connection A
BEGIN TRAN;

UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

　　Step2.在Connection B中運作以下代碼，首先設定隔離級别為未送出讀，再查詢産品2所在的記錄：

-- Connection B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

　　因為這個讀操作不用請求共享鎖，是以不會和其他事務發生沖突，該查詢傳回了如下圖所示的修改後的狀态，即使這一狀态還沒有被送出：

　　Step3.在Connection A中運作以下代碼復原事務：

ROLLBACK TRAN;

這個復原操作撤銷了對産品2的更新，這時它的價格被修改回了19.00，但是讀操作此前獲得的20.00再也不會被送出了。這就是髒讀的一個執行個體！

3.2 READ COMMITED 已送出讀

　　剛剛說到，未送出到會引起髒讀，能夠防止髒讀的最低隔離級别是已送出讀，這也是所有SQL Server版本預設使用的隔離級别。如其名稱所示，這個隔離級别隻允許讀取已經送出的修改，它要求讀操作必須獲得共享鎖才能操作，進而防止讀取未送出的修改。

　　繼續使用兩個會話來模拟：

　　Step1.在Connection A中運作以下代碼，更新産品2的價格，再查詢顯示價格：

BEGIN TRAN;

UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

　　Step2.再在Connection B中運作以下代碼，這段代碼将會話的隔離級别設定為已送出讀，再查詢産品2所在的行記錄：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

　　這時該會話語句會被阻塞，因為它需要擷取共享鎖才能進行讀操作，而它與會話A的寫操作持有的排它鎖相沖突。這裡因為我設定了預設會話阻塞逾時時間，是以出現了以下輸出：

　　Step3.在Connection A中運作以下代碼，送出事務：

COMMIT TRAN;

　　Step4.回到Connection B，此時會得到以下輸出：

在已送出讀級别下，不會讀取髒資料，隻能讀取已經送出過的修改。但是，該級别下，其他事務可以在兩個讀操作之間更改資料資源，讀操作因而可能每次得到不同的取值。這種現象被稱為不可重複讀。　　

3.3 REPEATABLE READ 可重複讀

　　如果想保證在事務内進行的兩個讀操作之間，其他任何事務都不能修改由目前事務讀取的資料，則需要将隔離級别更新為可重複讀。在該級别下，十五中的讀操作不但需要獲得共享鎖才能讀資料，而且獲得的共享鎖将一直保持到事務完成為止。換句話說，在事務完成之前，沒有其他事務能夠獲得排它鎖以修改這一資料資源，由此來保證實作可重複的讀取。

　　Step1.為了重新示範可重複讀的示例，首先需要将剛剛的測試資料清理掉，在Connection A和B中執行以下代碼：

-- Clear Test Data
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;

　　Step2.在Connection A中運作以下代碼，将會話的隔離級别設定為可重複讀，再查詢産品2所在的行記錄：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

　　這時該會話仍然持有産品2上的共享鎖，因為在該隔離級别下，共享鎖要一直保持到事務結束為止。

　　Step3.在Connection B中嘗試對産品2這一行進行修改：

UPDATE Production.Products
  SET unitprice = unitprice + 1.00
WHERE productid = 2;

　　這時該會話已被阻塞，因為修改操作鎖請求的排它鎖與前面會話授予的共享鎖有沖突。換句話說，如果讀操作是在未送出讀或已送出讀級别下運作的，那麼事務此時将不再持有共享鎖，Connection B嘗試修改改行的操作應該能夠成功。

　　同樣，由于我設定了逾時釋放時間，是以會有以下輸出：

　　Step4.回到Connection A，運作以下代碼，再次查詢茶品2所在的行，送出事務：

SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

　　這時的傳回結果仍然與第一次相同：

　　Step5.這時再執行Connection B中的更新語句，便能夠正常獲得排它鎖了，于是執行成功，價格變為了20.00。

可重複讀隔離級别不僅可以防止不可重複讀，另外還能防止丢失更新。丢失更新是指兩個事務讀取了同一個值，然後基于最初讀取的值進行計算，接着再更新該值，就會發生丢失更新的問題。這是因為在可重複讀隔離級别下，兩個事務在第一次讀操作之後都保留有共享鎖，是以其中一個都不能成功獲得為了更新資料而需要的排它鎖。但是，負面影響就是會導緻死鎖。

在可重複讀級别下運作的事務，讀操作獲得的共享鎖将一直保持到事務結束。是以可以保證在事務中第一次讀取某些行後，還可以重複讀取這些行。但是，事務隻鎖定查詢第一次運作時找到的那些行，而不會鎖定查詢結果範圍外的其他行。是以，在同一事務進行第二次讀取之前，如果其他事務插入了新行，而且新行也能滿足讀操作額查詢過濾條件，那麼這些新行也會出現在第二次讀操作傳回的結果中。這些新行稱之為幻影，這種讀操作也被稱為幻讀。

3.4 SERIALIZEABLE 可序列化

　　為了避免剛剛提到的幻讀，需要将隔離級别設定為可序列化。可序列化級别的處理方式與可重複讀類似：讀操作需要獲得共享鎖才能讀取資料并一直保留到事務結束，不同之處在于在可序列化級别下，讀操作不僅鎖定了滿足查詢條件的那些行，還鎖定了可能滿足查詢條件的行。換句話說，如果其他事務試圖增加能夠滿足操作的查詢條件的新行，目前事務就會阻塞這樣的操作。

　　同樣，繼續來模拟：

　　Step1.在Connection A中運作代碼，設定隔離級别為可序列化，再查詢産品分類等于1的所有産品：

-- Connection A
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

  SELECT productid, productname, categoryid, unitprice
  FROM Production.Products
  WHERE categoryid = 1;

　　Step2.在Connection B中運作代碼，嘗試插入一個分類等于1的新産品：

-- Connection B
INSERT INTO Production.Products
    (productname, supplierid, categoryid,
     unitprice, discontinued)
  VALUES('Product ABCDE', 1, 1, 20.00, 0);

　　這時，該操作會被阻塞。因為在可序列化級别下，前面的讀操作不僅鎖定了滿足查詢條件的那些行，還鎖定了可能滿足查詢條件的行。

　　Step3.回到Connection A，運作以下代碼，再次查詢分類1的産品，最後送出事務：

SELECT productid, productname, categoryid, unitprice
  FROM Production.Products
  WHERE categoryid = 1;

COMMIT TRAN;

　　Step4.回到Connection B，這時Connection B就已經獲得了等候已久的排它鎖，插入了新行。

INSERT INTO Production.Products
    (productname, supplierid, categoryid,
     unitprice, discontinued)
  VALUES('Product ABCDE', 1, 1, 20.00, 0);

SELECT productid, productname, categoryid, unitprice
FROM Production.Products
WHERE categoryid = 1;

　　Step5.為了後面的示範，運作以下代碼清理測試資料：

-- Cleanup
DELETE FROM Production.Products
WHERE productid > 77;

DBCC CHECKIDENT ('Production.Products', RESEED, 77);

3.5 SNAPSHOT 快照

　　首先解釋一下什麼是快照？事務已經送出的行的上一個版本存在tempdb資料庫中，這是SQL Server引入的一個新功能。

　　以這種行版本控制技術為基礎，SQL Server增加了兩個新的隔離級别：SNAPSHOT和READ COMMITED SNAPSHOT。如果啟用任何一種基于快照的隔離級别，DELETE和UPDATE語句在做出修改前都會把行的目前版本複制到tempdb資料庫中；INSERT語句則不會，因為這時還沒有行的舊版本。

　　在SNAPSHOPT（快照）隔離級别下，當讀取資料時，可以保證讀操作讀取的行是事務開始時**可用的最後送出的**版本。

　　下面來模拟一下該隔離級别下的場景：

　　Step1.還是打開兩個會話視窗，在其中一個執行以下代碼，設定隔離級别為SNAPSHOT：

-- Allow SNAPSHOT isolation in the database
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION ON;

　　Step2.在Connection A中運作以下代碼，更新産品2的價格，然後再查詢該産品的價格：

-- Connection A
BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

　　Step3.在Connection B中運作以下代碼，設定隔離級别為SNAPSHOT，并查詢産品2的價格：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

　這時的傳回結果如下所示，可以看到這個結果是在該事務啟動時可用的最後送出的版本。

　　Step4.回到Connection A送出這一修改的行：

COMMIT TRAN;

　　Step5.在Connection B中運作以下代碼，再次讀取資料，然後送出事務：

SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;
  
COMMIT TRAN;

　　然後我們會得到跟之前一樣的結果，奇了個怪了：

　　但是如果我們再次在Connection B中運作以下完整語句：

BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;
  
COMMIT TRAN;

　　這時結果便會同步，這個事務開始時可用的上一個送出的版本是價格=20.00

　　為什麼兩個事務得到結果會不同？這是因為快照清理線程每隔一分鐘運作一次，現在由于沒有事務需要為價格=20.00的那個行版本了，是以清理線程下一次運作時會将這個行版本從tempdb資料庫中删除掉。

　　最後，為了下一次示範，清理測試資料：

-- Clear Test Data
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;

這一隔離級别使用的不是共享鎖，而是行版本控制。如前所述，不論修改操作（主要是更新和删除資料）是否在某種基于快照的隔離級别下的會話執行，快照隔離級别都會帶來性能上的開銷。

　　另外，在SNAP快照級别下，可以通過檢查的行版本，檢測出更新沖突。它能判斷出在快照事務的一次讀操作和一次寫操作之間是否有其他事務修改過資料。如果SQL Server檢測到在讀取和寫入操作之間有另一個事務修改了資料，則會讓事務因失敗而終止，并傳回以下錯誤資訊：

　　沖突檢測完整執行個體如下：

---------------------------------------------------------------------
-- Conflict Detection 沖突檢測執行個體
---------------------------------------------------------------------

-- Connection A, Step 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection A, Step 2
  UPDATE Production.Products
    SET unitprice = 20.00
  WHERE productid = 2;
  
COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

-- Connection A, Step 1
BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection B, Step 1
UPDATE Production.Products
  SET unitprice = 25.00
WHERE productid = 2;

-- Connection A, Step 2
  UPDATE Production.Products
    SET unitprice = 20.00
  WHERE productid = 2;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

-- Close all connections

3.6 READ COMMITED SNAPSHOT 已經送出讀隔離

　　已送出讀隔離也是基于行版本控制，但與快照不同之處在于：在已送出讀級别下，讀操作讀取的資料行不是食物啟動之前最後送出的版本，而是語句啟動前最後送出的版本。

　　此外，該級别不會像快照隔離級别一樣進行更新沖突檢測。這樣一來，它就跟SQL Server預設的READ COMMITED級别非常類似了，隻不過讀操作不用獲得共享鎖，當請求的資源被其他事務的排它鎖鎖定時，也不用等待。

　　下面繼續通過案例來模拟：

　　Step1.運作以下代碼，設定隔離級别：

-- Turn on READ_COMMITTED_SNAPSHOT
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT ON;

　　執行該查詢需要一定的時間，并且要注意：要成功運作，目前連接配接必須是指定資料庫的唯一連接配接，請關掉其他連接配接，隻保留一個會話來執行。

　　可以看到它跟我們之前設定隔離級别所使用的的語句不同，這個選項其實就是把預設的READ COMMITED的寒意變成了READ COMMITED SNAPSHOT。意味着打開這個選項時，除非顯式地修改會話的隔離級别，否則READ COMMITED SNAPSHOT将成為預設的隔離級别。

　　Step2.在Connection A中運作以下代碼，更新産品2所在的行記錄，再讀取這一行記錄，并且一直保持事務打開：

-- Connection A
USE TSQLFundamentals2008;

BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

　　Step3.在Connection B中讀取産品2所在的行記錄，并一直保持事務打開：

-- Connection B
BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

　　得到的結果是語句啟動之前最後送出的版本（19.00）：

　　Step4.回到Connection A，送出事務：

COMMIT TRAN;

　　Step5.回到Connection B，再次讀取産品2所在的行，并送出事務：

SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

　　這時結果如下，可以看到跟SNAPSHOT不同，這次的結果是在語句執行之前最後送出的版本而不是事務執行之前最後送出的版本，是以得到了20.00：

回想一下，這種現象是不是我們常聽見的不可重複讀？也就是說，該級别下，無法防止不可重複讀問題。

　　最後，按照國際慣例，清理測試資料：

-- Clear Test Data
UPDATE Production.Products
SET unitprice = 19.00
WHERE productid = 2;

　　然後，關閉所有連接配接，然後在一個新的連接配接下運作以下代碼，以禁用指定資料庫的基于快照的隔離級别：（執行ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT OFF;這一句時可能需要花費一點時間，請耐心等候；）

-- Make sure you're back in default mode
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Change database options to default
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT OFF;

3.7 隔離級别總結

　　下表總結了每種隔離級别能夠解決各種邏輯一緻性的問題，以及隔離級别是否會檢測更新沖突，是否使用了航班本控制。

　　這時再回顧以下各個問題的描述及結果，我們來看另一個表：

并發事務引起的問題
問題	描述	結果	解決
丢失更新	A讀—B讀—A改—B改	A更改丢失	READ UNCOMMITTED
髒讀	A改—B讀—A復原	B讀無效值	READ COMMITTED
不可重讀	A讀—B改—A讀	A讀不一緻	REPEATABLE READ
			SNAPSHOT
幻讀	A讀—B增删—A讀	A讀或多或少	SERIALIZABLE

四、死鎖

4.1 死鎖是個什麼鬼?

　　死鎖是指一種程序之間互相永久阻塞的狀态，可能涉及到兩個或者多個程序。兩個程序發生死鎖的例子是：程序A阻塞了程序B，程序B又阻塞了程序A。在任何一種情況下，SQL Server都可以檢測到死鎖，并選擇終止其中一個事務以幹預死鎖狀态。如果SQL Server不幹預，那麼死鎖涉及到的程序将會永遠保持死鎖狀态。

　　預設情況下，SQL Server會選擇終止做過的操作最少的事務，因為這樣可以讓復原開銷降低到最低。當然，在SQL Server 2005及之後的版本中，可以通過将會話選項DEADLOCK_PRIORITY設定為範圍（-10到10）之間的任一整數值。

4.2 死鎖執行個體

　　仍然打開三個會話：Connection A、B和C：

　　Step1.在Connection A中更新Products表中産品2的行記錄，并保持事務一直打開：

-- Connection A
USE TSQLFundamentals2008;

BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

　　這時Connection A對産品表的産品2請求了排它鎖。

　　Step2.在Connection B中更新OrderDetails表中産品2的訂單明細，并保持事務一直打開：

-- Connection 2
BEGIN TRAN;

  UPDATE Sales.OrderDetails
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

　　這時Connection A對訂單明細表的産品2請求了排它鎖。

　　Step3.回到Connection A中，執行以下語句，請求查詢産品2的訂單明細記錄：

-- Connection A

  SELECT orderid, productid, unitprice
  FROM Sales.OrderDetails
  WHERE productid = 2;

COMMIT TRAN;

　　由于此時實在預設的READ COMMITED隔離級别下運作的，是以Connection A中的事務需要一個共享鎖才能讀資料，是以這裡會一直阻塞住。但是，此時并沒有發生死鎖，而隻是發生了阻塞。

　　Step4.回到Connection B中，執行以下語句，嘗試在Products表查詢産品2的記錄：

-- Connection 2

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

　　這裡由于這個請求和Connection A中的事務在同一個資源上持有的排它鎖發生了沖突，于是互相阻塞發生了死鎖。SQL Server通常會在幾秒鐘之内檢測到死鎖，并從這兩個程序中選擇一個作為犧牲品，終止其事務。是以我們還是得到了以下結果：

　　Step5.剛剛提到了SQL Server會選擇一個作為犧牲品，我們回到Connection A會看到以下的錯誤資訊提示：

　　在這個例子中，由于兩個事務進行的工作量差不多一樣，是以任何一個事務都有可能被終止。（前面提到，如果沒有手動設定優先級，那麼SQL Server會選擇工作量較小的一個事務作為犧牲品）另外，解除死鎖需要一定的系統開銷，因為這個過程會涉及撤銷已經執行過的處理。

顯然，事務處理的時間越長，持有鎖的時間也就越長，死鎖的可能性也就越大。應該盡量保持事務簡短，把邏輯上可以屬于同一工作單元的操作移到事務之外。

4.3 避免死鎖

（1）改變通路資源的順序可以避免死鎖

　　繼續上面的例子，Connection A先通路Products表中的行，然後通路OrderDetails表中的行；Connection B先通路OrderDetails表中的行，然後通路Products表中的行。

　　這時如果我們改變一下通路順序：兩個事務按照同樣的順序來通路資源，則不會發生這種類型的死鎖。

通過交換其中一個事務的操作順序，就可以避免發生這種類型的死鎖（假設交換順序不必改變程式的邏輯）。　

（2）良好的索引設計也可以避免死鎖

　　如果查詢篩選條件缺少良好的索引支援，也會造成死鎖。例如，假設Connection B中的事務有兩條語句要對産品5進行篩選，Connection A中的事務要對産品2進行處理，那麼他們就不應該有任何沖突。但是，如果在表的productid列上如果沒有索引來支援查詢篩選，那麼SQL Server就必須掃描（并鎖定）表中的所有行，這樣當然會導緻死鎖。

總之，良好的索引設計将有助于減少這種沒有真正的邏輯沖突的死鎖。

　　最後，按照國際慣例清理掉測試資料：

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

UPDATE Sales.OrderDetails
  SET unitprice = 19.00
WHERE productid = 2
  AND orderid >= 10500;

UPDATE Sales.OrderDetails
  SET unitprice = 15.20
WHERE productid = 2
  AND orderid < 10500;

五、小結

　　本篇介紹了事務和并發，重點解釋了事務是個什麼鬼，以及在SQL Server中如何管理事務。示範了在SQL Server中如何把一個事務通路的資料和其他事務的不一緻性使用進行隔離，以及如何處理死鎖的情況。相信随着這些内容的了解，我們對事務和并發的認知不再停留在資料庫基礎的教材裡邊，也希望對大家有所幫助。最後推薦各位使用MS SQL Server的程式員都閱讀一下《MS SQL Server 2008技術内幕：T-SQL語言基礎》這本書，真的是值得閱讀的一本。

參考資料

（1）美 Itzik Ben-Gan 著，成保棟譯，《Microsoft SQL Server 2008技術内幕：T-SQL語言基礎》

考慮到很多人買了這本書，卻下載下傳不了這本書的配套源代碼和示例資料庫，特意上傳到了百度雲盤中，

點此下載下傳

（2）BIWORK，《

SQL Server 中的事務與事務隔離級别以及如何了解髒讀, 未送出讀,不可重複讀和幻讀産生的過程和原因

》

（3）Jackson，《

30分鐘全面解析-SQL事務+隔離級别+阻塞+死鎖

30分鐘學習MS SQL Server的事務與并發一、事務二、鎖定和阻塞三、隔離級别四、死鎖五、小結參考資料

一、事務

1.1 事務的概念

1.2 事務的ACID特性

1.3 如何定義事務

二、鎖定和阻塞

2.1 鎖

2.2 阻塞

三、隔離級别

3.1 READ UNCOMMITED 未送出讀

3.2 READ COMMITED 已送出讀

3.3 REPEATABLE READ 可重複讀

3.4 SERIALIZEABLE 可序列化

3.5 SNAPSHOT 快照

3.6 READ COMMITED SNAPSHOT 已經送出讀隔離

3.7 隔離級别總結

四、死鎖

4.1 死鎖是個什麼鬼?

4.2 死鎖執行個體

4.3 避免死鎖

五、小結

參考資料

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

面試題解析：你接口測試是怎麼做的？

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述