MSSQL事務開發指南

瞭解事務
 事務是作為單個邏輯工作單元執行的一系列操作。可以是一條SQL語句也可以是多條SQL語句。
 事務具有四個特性
  原子性：不可分隔、成則具成、敗則具敗。
  一致性：事務在完成時，必須使所有的資料都保持一致狀態
  隔離性：獨立的執行互不干擾。由並發事務所作的修改必須與任何其他並發事務所作的修改隔離。
  持久性：務完成之後，它對於系統的影響是永久性的。該修改即使出現系統故障也將一直保持。
 應用程式主要通過指定事務啟動和結束的時間來控制事務。
 啟動事務：使用 API 函數和 Transact-SQL 陳述式，可以按顯式、自動認可或隱式的方式來啟動事務。
 結束事務：您可以使用 COMMIT（成功） 或 ROLLBACK（失敗） 語句，或者通過 API 函數來結束事務。
 事務模式分為：顯示事務模式、隱含交易模式、自動事務模式。在SQL常用的是顯示模式。
 事務並發處理會產生的問題：
  1,丟失更新 
  當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，會發生丟失更新問題。 
  每個事務都不知道其它事務的存在。最後的更新將重寫由其它事務所做的更新，這將導致資料丟失。   
  2,髒讀 
  當第二個事務選擇其它事務正在更新的行時，會發生未確認的相關性問題。 
  第二個事務正在讀取的資料還沒有確認並且可能由更新此行的事務所更改。
  3,不可重複讀取 
  當第二個事務多次訪問同一行而且每次讀取不同的資料時，會發生不一致的分析問題。 
  不一致的分析與未確認的相關性類似，因為其它事務也是正在更改第二個事務正在讀取的資料。 
  然而，在不一致的分析中，第二個事務讀取的資料是由已進行了更改的事務提交的。而且，不一致的分析涉及多次（兩次或更多）讀取同一行，而且每次資訊都由其它事務更改；因而該行被非重複讀取。 
  4,幻像讀 
  當對某行執行插入或刪除操作，而該行屬於某個事務正在讀取的行的範圍時，會發生幻像讀問題。 
  事務第一次讀的行範圍顯示出其中一行已不複存在於第二次讀或後續讀中，因為該行已被其它事務刪除。同樣，由於其它事務的插入操作，事務的第二次或後續讀顯示有一行已不存在於原始讀中。 
  事務的隔離等級
  該隔離等級定義一個事務必須與其他事務所進行的資源或資料更改相隔離的程度。交易隔離等級控制：
  讀取資料時是否佔用鎖以及所請求的鎖類型。
  佔用讀取鎖的時間。
  引用其他事務修改的行的讀取操作是否：
   在該行上的獨佔鎖定被釋放之前阻塞其他事務。
   檢索在啟動語句或事務時存在的行的已提交版本。
   讀取未提交的資料修改。
 事務的隔離等級
  SQL語句可以使用SET TRANSACTION ISOLATION LEVEL來設定事務的隔離等級。
  1. Read Uncommitted：最低等級的事務隔離，僅僅保證了讀取過程中不會讀取到非法資料。上訴4種不確定情況均有可能發生。
  2. Read Committed：大多數主流資料庫的預設事務等級，保證了一個事務不會讀到另一個並行事務已修改但未提交的資料，避免了“髒讀取”。該層級適用於大多數系統。
  第一個查詢事務
  SET  TRANSACTION  ISOLATION  LEVEL   Read Committed
  begin  tran
   update Cate SET Sname=Sname+'b' where ID=1
   SELECT * FROM cate where ID=1
   waitfor  delay  '00:00:6'  
   rollback  tran  --復原事務
  select Getdate()
  SELECT * FROM cate where ID=1
  第二個查詢事務
  SET  TRANSACTION  ISOLATION  LEVEL  Read committed   --把committed換成Read uncommitted可看到“髒讀取”的樣本。
  SELECT * FROM cate where ID=1
  select Getdate()
  可以看到使用 Read Committed 成功的避免了“髒讀取”.
  3. Repeatable Read：保證了一個事務不會修改已經由另一個事務讀取但未提交（復原）的資料。避免了“髒讀取”和“不可重複讀取”的情況，但是帶來了更多的效能損失。
  第一個查詢事務
  SET  TRANSACTION  ISOLATION  LEVEL  Repeatable Read  --   把Repeatable Read換成Read committed可以看到“不可重複讀取”的樣本
  begin  tran
  SELECT * FROM cate where  ID=33 --第一次讀取資料
   waitfor  delay  '00:00:6'  
  SELECT * FROM cate where  ID=33 --第二次讀取資料，不可重複讀取
  commit
  第二個查詢事務
  SET  TRANSACTION  ISOLATION  LEVEL  Read committed
  update cate set Sname=Sname+'JD' where ID=33
  SELECT * FROM cate where ID>30
  4. Serializable：最高等級的事務隔離，上面3種不確定情況都將被規避。這個層級將類比事務的串列執行。
  在第一個查詢時段執行
  SET  TRANSACTION  ISOLATION  LEVEL  Serializable -- 把Serializable換成Repeatable Read 可看到“幻像讀”的樣本
  begin  tran
  SELECT * FROM cate where ID>30 --第一次讀取資料，“幻像讀”的樣本
   waitfor  delay  '00:00:6'   --延遲6秒讀取
  SELECT * FROM cate where ID>30  --第一次讀取資料
  commit
  第二個查詢事務
  SET  TRANSACTION  ISOLATION  LEVEL  Read committed  
  Delete from cate where ID>33
  SELECT * FROM cate where ID>30  
建立事務

 設定事務層級：SET  TRANSACTION  ISOLATION  LEVEL
 開始事務：begin  tran
 提交事務：COMMIT
 復原事務：ROLLBACK
 建立事務儲存點：SAVE TRANSACTION savepoint_name
 復原到事務點:ROLLBACK TRANSACTION savepoint_name

建立事務的原則：
儘可能使事務保持簡短很重要，當事務啟動後，資料庫管理系統 (DBMS) 必須在事務結束之前保留很多資源、以保證事務的正確安全執行。
特別是在大量並發的系統中， 保持事務簡短以減少並發 資源鎖定爭奪，將先得更為重要。
 1、交易處理，禁止與使用者互動，在事務開始前完成使用者輸入。
 2、在瀏覽資料時，盡量不要開啟事務
 3、儘可能使事務保持簡短。
 4、考慮為唯讀查詢使用快照隔離，以減少阻塞。
 5、靈活地使用更低的交易隔離等級。
 6、靈活地使用更低的遊標並發選項，例如開放式並發選項。
 7、在事務中盡量使訪問的資料量最小。