概述
不少人對於事務的使用侷限於begin transaction：開始事務、commit transaction：提交事務、rollback transaction：回滾事務的初步運用。
並且知道使用事務後， 事務中所有操作命令必須作為一個整體提交或回滾，如果事務中任何操作命令失敗，則整個事務將因失敗而回滾。
除了這個概念性的東西后，其他就不怎麼知道了，比如事務的隔離性，具體怎麼隔離、有幾種隔離方式、執行順序是什麼。
我們今天來聊一聊這一塊的內容（主要是事務的隔離性）。
什麼是事務（定義）
事務（Transaction），一般是指要做的或所做的事情。在計算機術語中是指訪問並可能更新資料庫中各種資料項的一個程式執行單元(unit)。
事務通常由高階資料庫操縱語言或程式語言（如SQL，C++或Java）書寫的使用者程式的執行所引起，並用形如begin transaction和end transaction語句（或函式呼叫）來界定。
事務由事務開始(begin transaction)和事務結束(end transaction)之間執行的全體操作組成。
事務是作為單個邏輯單元執行的一系列操作，它是一個不可分割的工作邏輯單元。它包含了一組資料庫操作命令，這組命令要麼全部執行，要麼全部不執行。
事務的4個特性（特性）
原子性（atomicity）
一致性（consistency）
比如轉賬，假設張三和李四兩個人的錢加起來一共是2000，那麼不管張三和李四之間怎麼轉賬，轉幾次賬，事務結束後兩個人的錢加起來還得是2000，這就是事務的一致性。
隔離性（isolation）
對於任意兩個併發的事務T1和T2，在事務T1看來，T2要麼在T1開始之前就已經結束，要麼在T1結束之後才開始，這樣每個事務都感覺不到有其他事務在併發地執行。
永續性（durability）
在提交事務方法後，提示使用者事務操作完成，當我們程式執行完成直到看到提示後，就可以認定事務以及正確提交，
即使這時候資料庫出現了問題，也必須要將我們的事務完全執行完成，否則就會造成我們看到提示事務處理完畢，但是資料庫因為故障而沒有執行事務的重大錯誤。
事務的分類 （分類）
事務分為三類：顯式事務、隱式事務、自動提交事務
(1) 顯式事務：用 begin transaction 明確指定事務的開始，由 commit transaction 提交事務、rollback transaction 回滾事務到事務結束。
(2) 隱式事務：通過設定 set implicit_transactions on 語句，將隱式事務模式設定為開啟。
當以隱式事務模式操作時，不必使用 begin transaction 開啟事務，當一個事務結束後，這個模式會自動啟用下一個事務，
只需使用 commit transaction 提交事務或 Rollback Transaction 回滾事務即可。
(3) 自動提交事務： 這是 SQL Server 的預設模式，它將每條單獨的 T-SQL 語句視為一個事務。如果成功執行，則自動提交。如果錯誤，則自動回滾。
簡單事務應用示例
我們先看一個簡單的應用。
--這裡指定不指定事務名稱均可 BEGIN TRANSACTION tran_UpdateTable --開啟事務(tran_UpdateTable：事務名稱) DECLARE @tran_error int;--定義錯誤變數 SET @tran_error=0; --錯誤變數的初始值為0 --使用 try catch進行錯誤捕捉 BEGIN TRY UPDATE a_Students SET Name='孫悟空' WHERE Number='100001'; UPDATE a_StudentsScore SET C# ='我是分數,應該是數字' WHERE Number='100001'; END TRY BEGIN CATCH set @tran_error=@tran_error+1; --（計算捕捉到的錯誤數）加分號或不加都能正常執行 END CATCH --判斷是否有執行錯誤 IF(@tran_error>0) BEGIN ROLLBACK TRANSACTION tran_UpdateTable --執行出錯，回滾事務（tran_UpdateTable：指定事務名稱） PRINT '有【'+CONVERT(VARCHAR(50),@tran_error)+'】條執行失敗，進行回滾:'; END ELSE BEGIN COMMIT TRANSACTION tran_UpdateTable --沒有異常，提交事務(tran_UpdateTable：指定事務名稱) --事務執行成功後，檢視修改後的資料 SELECT s.Number , s.Name , sc.ClassName , ss.C# , ss.SqlDB , ss.Java , ss.Python FROM a_Students s INNER JOIN a_StudentClass sc ON s.ClassId = sc.ClassId INNER JOIN a_StudentsScore ss ON s.Number = ss.Number WHERE s.Number='100001' END
上面事務表示：根據學生編號修改學生姓名和學生學科C#的成績，如果有任何一條執行失敗則全部返回不執行，否則執行修改成功。
事務不隔離導致的問題
以上介紹完事務的四大特性(簡稱ACID)，現在重點來說明下事務的隔離性，
當多個執行緒都開啟事務運算元據庫中的資料時，資料庫系統要能進行隔離操作，以保證各個執行緒獲取資料的準確性，
在介紹資料庫提供的各種隔離級別之前，我們先看看如果不考慮事務的隔離性，會發生的幾種問題：
更新丟失（Lost update）、髒讀（Dirty Reads）、不可重複讀（Non-repeatable Reads）
1、更新丟失（Lost update）
兩個事務都同時更新一行資料，但是第二個事務卻中途失敗退出，導致對資料的兩個修改都失效了。這是因為系統沒有執行任何的鎖操作，因此併發事務並沒有被隔離開來。
2、髒讀（Dirty Reads）
一個事務開始讀取了某行資料，但是另外一個事務已經更新了此資料但沒有能夠及時提交。這是相當危險的，因為很可能所有的操作都被回滾。
當一個事務正在多次修改某個資料，而在這個事務中這多次的修改都還未提交，這時一個併發的事務來訪問該資料，就會造成兩個事務得到的資料不一致。
例如：使用者A向使用者B轉賬1000元，對應SQL命令如下：
--使用者A向使用者B轉賬1000元，B的錢增加 update account set money=money+1000 where name='B'; --此時A通知B我給你轉錢了，A的錢減少 update account set money=money - 1000 where name='A';
當只執行第一條SQL時，A通知B檢視賬戶，B發現確實錢已到賬（此時即發生了髒讀），
而之後無論第二條SQL是否執行，只要該事務不提交，則所有操作都將回滾，那麼當B以後再次檢視賬戶時就會發現錢其實並沒有轉。
3、不可重複讀（Non-repeatable Reads）
不可重複讀是指在對於資料庫中的某個資料，一個事務範圍內多次查詢卻返回了不同的資料值，這是由於在查詢間隔，被另一個事務修改並提交了。
（1） 虛讀：事務T1讀取某一資料後，事務T2對其做了修改，當事務T1再次讀該資料時得到與前一次不同的值。
（2） 幻讀：事務在操作過程中進行兩次查詢，第二次查詢的結果包含了第一次查詢中未出現的資料或者缺少了第一次查詢中出現的資料（這裡並不要求兩次查詢的SQL語句相同）。
這是因為在兩次查詢過程中有另外一個事務插入資料造成的。
事務的隔離級別
為了避免上面出現的幾種情況，在標準SQL規範中，定義了4+2個事務隔離級別，不同的隔離級別對事務的處理不同
1、未提交讀取（相當於with(nolock)）：第一級別
也稱為未授權讀取：允許髒讀取，但不允許更新丟失。
如果一個事務已經開始寫資料，則另外一個事務則不允許同時進行寫操作，但允許其他事務讀此行資料。
該隔離級別可以通過“排他寫鎖”實現。
缺點：會產生髒讀、不可重複讀、幻讀。
案例解讀：以前交易所炒股的時候，股民老王購買了5000股，操作員操作錄入（此時開啟事務），操作時手誤，多輸入了一個0，資料儲存但是未提交。
此時老王查詢自己的持股資訊，發現自己居然有50000股，瞬間血壓升高，昏倒在地。
然後操作員發現自己錄入錯誤，修改成正確的資訊，錄入完成（事務結束）。
老王被救醒後，哆嗦這查詢自己的持股，發現只有5000，沒有增減，他之前看到的就是髒讀資料。
解決方案：採用更高階的隔離機制，如提交讀。
2、提交讀取（Oracle和SQLServer預設的）：第二級別
這是大多數資料庫系統的預設隔離級別（Oracle和SQLServer預設的）。
也稱為授權讀取：允許不可重複讀取，但不允許髒讀取。
這可以通過“瞬間共享讀鎖”和“排他寫鎖”實現。讀取資料的事務允許其他事務繼續訪問該行資料，但是未提交的寫事務將會禁止其他事務訪問該行。
缺點：會產生不可重複讀、幻讀。
案例解讀：股市升高後，老王檢視自己持有5000股，就想賣掉4000股，在老王賣股票的時候，老王的老婆看股市太高，就登入老王的賬號，賣掉3000股。
當老王想賣股票時，發現自己只有2000股，不是之前看到的5000股，這就是不可重複讀問題。
解決方案：採用更高階的隔離機制，如可重複讀。
3、可重複讀取（相當於(HOLDLOCK)）：第三級別
MySQL的預設事務隔離級別。
可重複讀取（Repeatable Read）：禁止不可重複讀取和髒讀取，但是有時可能出現幻讀資料。
這可以通過“共享讀鎖”和“排他寫鎖”實現。讀取資料的事務將會禁止寫事務（但允許讀事務），寫事務則禁止任何其他事務。
缺點：會產生幻讀。
問題解讀：股市忽漲忽跌，老王焦慮不安，按捺不住，想把持有的多種股票全部拋掉。與此同時，老王老婆聽信專家所言，使用老王的賬號買了某隻神股。
老王拋掉所有股票後，檢視自己的持股，猛然發現自己居然還持有一隻股票，瞬間覺得一臉懵逼，這就是幻讀導致。
解決方案：採用更高階的隔離機制，序列化。
4、序列化（這是最高的隔離級別）：第四級別
序列化（Serializable）：提供嚴格的事務隔離。
它要求事務序列化執行，事務只能一個接著一個地執行，不能併發執行。
僅僅通過“行級鎖”是無法實現事務序列化的，必須通過其他機制保證新插入的資料不會被剛執行查詢操作的事務訪問到。
缺點：可以解決併發事務的所有問題。但是效率地下，消耗資料庫效能，一般不使用。
隔離級別越高，越能保證資料的完整性和一致性，但是對併發效能的影響也越大。
對於多數應用程式，可以優先考慮把資料庫系統的隔離級別設為Read Committed。它能夠避免髒讀取，而且具有較好的併發效能。
儘管它會導致不可重複讀、幻讀和第二類丟失更新這些併發問題，在可能出現這類問題的個別場合，可以由應用程式採用悲觀鎖或樂觀鎖來控制。
5、快照
(1)SNAPSHOT 在SNAPSHOT隔離級別下,當讀取資料時可以保證操作讀取的行是事務開始時可用的最後提交版本。
(2)同時SNAPSHOT隔離級別也滿足前面的已提交讀,可重複讀,不幻讀;該隔離級別實用的不是共享鎖,而是行版本控制。
6、已提交讀快照
READ COMMITTED SNAPSHOT也是基於行版本控制，但是READ COMMITTED SNAPSHOT的隔離級別是讀操作之前的最後已提交版本，
而不是事務前的已提交版本，有點類似前面的READ COMMITTED能保證已提交讀，但是不能保證可重複讀，不能避免幻讀，
但是又比 READ COMMITTED隔離級別多出了不需要獲取共享鎖就可以讀取資料。
事務的隔離級別設定示例
SQL Server通過在鎖資源上使用不同型別的鎖來隔離事務。
為了開發安全的事務，定義事務內容以及應在何種情況下回滾至關重要，定義如何以及在多長時間內在事務中保持鎖定也同等重要。
這由隔離級別決定，應用不同的隔離級別，SQL Server賦予開發者一種能力，讓他們為每一個單獨事務定義與其他事務的隔離程度。事務隔離級別的定義如下：
獲取事務隔離級別(isolation level)
DBCC USEROPTIONS
1、未提交讀取
新建回話並將學生編號為100001的成績+1；
BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' SELECT * FROM a_StudentsScore WHERE Number='100001'
然後執行錯誤的回話，進行回滾
BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+'' WHERE Number='100001'
然後在查詢資料
--首先不新增隔離級別,預設是READ COMMITTED,由於資料之前的更新操作使用了排他鎖，所以查詢一直在等待鎖釋放*/ SELECT * FROM a_StudentsScore WHERE Number='100001' ---將查詢的隔離級別設定為READ UNCOMMITTED允許未提交讀，讀操作之前不請求共享鎖。 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM a_StudentsScore WHERE Number='100001' --當然也可以使用表隔離,效果是一樣的 SELECT * FROM a_StudentsScore WITH (NOLOCK) WHERE Number='100001'
我們發現執行兩個事務回話，第一個執行成了，但是第二個執行失敗了進行回滾，最後查詢的資料是第一個執行前的資料，沒有任何改變。
2、提交讀取
新建回話1並將學生編號為100001的成績+1，此時回話的排他鎖鎖住了學生編號為100001的成績
BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' SELECT * FROM a_StudentsScore WHERE Number='100001'
在回話2中執行查詢，將隔離級別設定為READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM a_StudentsScore WHERE Number='100001' --由於READ COMMITTED需要申請獲得共享鎖，而鎖與回話1的排他鎖衝突，回話被堵塞， --在回話1中執行事務提交 COMMIT TRANSACTION --由於回話1事務提交，釋放了學生100001的排他鎖，此時回話2申請共享鎖成功查到學生100001的C#成績為修改後的成績81，READ COMMITTED由於是已提交讀隔離級別，所以不會讀髒資料.
重置資料
UPDATE a_StudentsScore SET C#=80 WHERE Number='100001'
注意：但是由於READ COMMITTED讀操作一完成就立即釋放共享鎖,讀操作不會在一個事務過程中保持共享鎖,
也就是說在一個事務的的兩個查詢過程之間有另一個回話對資料資源進行了更改,會導致一個事務的兩次查詢得到的結果不一致,這種現象稱之為不可重複讀.
3、可重複讀取
在回話1中查詢學生編號為100001的成績，,將回話級別設定為REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001'
新建回話2修改學生編號為100001的成績
UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' ---由於回話1的隔離級別REPEATABLE READ申請的共享鎖一直要保持到事務結束，所以回話2無法獲取排他鎖，處於等待狀態
在回話1中執行下面語句，然後提交事務
SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION
回話1的兩次查詢得到的結果一致，前面的兩個隔離級別無法得到一致的資料，此時事務已提交同時釋放共享鎖，回話2申請排他鎖成功，對行執行更新
REPEATABLE READ隔離級別保證一個事務中的兩次查詢到的結果一致，同時保證了丟失更新
丟失更新：兩個事務同時讀取了同一個值然後基於最初的值進行計算，接著再更新，就會導致兩個事務的更新相互覆蓋。
例如酒店訂房例子，兩個人同時預定同一酒店的房間，首先兩個人同時查詢到還有一間房間可以預定，然後兩個人同時提交預定操作，事務1執行number=1-0，同時事務2也執行number=1-0最後修改number=0,這就導致兩個人其中一個人的操作被另一個人所覆蓋，REPEATABLE READ隔離級別就能避免這種丟失更新的現象，當事務1查詢房間時事務就一直保持共享鎖直到事務提交，而不是像前面的幾個隔離級別查詢完就是否共享鎖，就能避免其他事務獲取排他鎖。
4、序列化
SERIALIZABLE(可序列化)，對於前面的REPEATABLE READ能保證事務可重複讀，但是事務只鎖定查詢第一次執行時獲取的資料資源（資料行），
而不能鎖定查詢結果之外的行，就是原本不存在於資料表中的資料。因此在一個事務中當第一個查詢和第二個查詢過程之間，有其他事務執行插入操作且插入資料滿足第一次查詢讀取過濾的條件時，
那麼在第二次查詢的結果中就會存在這些新插入的資料，使兩次查詢結果不一致，這種讀操作稱之為幻讀。
為了避免幻讀需要將隔離級別設定為SERIALIZABLE。
在回話1中執行查詢操作,並將事務隔離級別設定為REPEATABLE READ(先測試一下前面更低階別的隔離)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001'
在回話2中執行修改操作
UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001'
返回回話1重新執行查詢操作並提交事務
SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION
結果回話1中第二次查詢到的資料包含了回話2新修改的資料，兩次查詢結果不一致（驗證之前的隔離級別不能保證幻讀）
接下來將回話級別設定為SERIALIZABLE,在回話1中執行查詢操作,並將事務隔離級別設定為SERIALIZABLE。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001'
在回話2中執行修改操作
UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001'
返回回話1重新執行查詢操作並提交事務
SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION
兩次執行的查詢結果相同
重置所有開啟回話的預設隔離級別
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
5、快照
SNAPSHOT 在SNAPSHOT隔離級別下,當讀取資料時可以保證操作讀取的行是事務開始時可用的最後提交版本
同時SNAPSHOT隔離級別也滿足前面的已提交讀,可重複讀,不幻讀;該隔離級別實用的不是共享鎖,而是行版本控制
使用SNAPSHOT隔離級別首先需要在資料庫級別上設定相關選項
在開啟的所有查詢視窗中執行以下操作
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;
--在回話1中開啟事務,將學生100001的成績加1,並查詢跟新後的成績 BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' SELECT * FROM a_StudentsScore WHERE Number='100001' ---查詢到更新後的成績為81 ---在回話2中將隔離級別設定為SNAPSHOT,並開啟事務(此時查詢也不會因為回話1的排他鎖而等待,依然可以查詢到資料) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001' ---查詢到的結果還是回話1修改前的成績,由於回話1在預設的READ COMMITTED隔離級別下執行,SQL SERVER必須在更新前把行的一個副本複製到TEMPDB資料庫中 --在SNAPSHOT級別啟動事務會請求行版本 ---現在在回話1中執行提交事務,此時學生100001的成績為81 COMMIT TRANSACTION ---再次在回話2中查詢學生100001的成績並提交事務,結果還是80,因為事務要保證兩次查詢的結果相同 SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION ---此時如果在回話2中重新開啟一個事務,查詢到的學生100001的成績為81 BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION --SNAPSHOT隔離級別保證操作讀取的行是事務開始時可用的最後已提交版本,由於回話1的事務未提交,所以學生100001的最後提交版本還是修改前的成績80, --所以回話2讀取到的成績是回話2事務開始前的已提交版本成績80,當回話1提交事務後,回話2重新新建一個事務此時事務開啟前的成績已經是81了， --所以查詢到的成績是81，同時SNAPSHOT隔離級別還能保證SERIALIZABLE的隔離級別
6、已提交讀快照
READ COMMITTED SNAPSHOT也是基於行版本控制,但是READ COMMITTED SNAPSHOT的隔離級別是讀操作之前的最後已提交版本,而不是事務前的已提交版本，
有點類似前面的READ COMMITTED能保證已提交讀,但是不能保證可重複讀,不能避免幻讀,但是又比 READ COMMITTED隔離級別多出了不需要獲取共享鎖就可以讀取資料
要啟用READ COMMITTED SNAPSHOT隔離級別同樣需要修改資料庫選項，
在回話1，回話2中執行以下操作(執行下面的操作當前連線必須是資料庫的唯一連線，可以通過查詢已連線當前資料庫的程式,然後KILL掉那些程式,然後再執行該操作,否則可能無法執行成功)。
--在回話1中開啟事務,將學生100001的成績加1,並查詢跟新後的成績,並保持事務一直處於開啟狀態 UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' --查詢到的成績是81 SELECT * FROM a_StudentsScore WHERE Number='100001' --在回話2中開啟事務查詢學生100001並一直保持事務處於開啟狀態(此時由於回話1還未提交事務,所以回話2中查詢到的還是回話1執行事務之前儲存的行版本) BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001' --查詢到的成績還是80 --在回話1中提交事務 COMMIT TRANSACTION --在回話2中再次執行查詢學生100001的成績,並提交事務 SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION --此時的成績為回話1修改後的成績81,而不是事務之前已提交版本的成績,也就是READ COMMITTED SNAPSHOT隔離級別在同一事務中兩次查詢的結果不一致.
關閉所有連線,然後開啟一個新的連線,禁用之前設定的資料庫快照隔離級別選項。
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF; ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;
參考文獻1：百度百科：事務
參考文獻2：百度百科：事務隔離級別
|
歡迎關注訂閱微信公眾號【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂 公眾號：熊澤有話說 出處： https://www.cnblogs.com/xiongze520/p/14821536.html 創作不易，任何人或團體、機構全部轉載或者部分轉載、摘錄，請在文章明顯位置註明作者和原文連結。
|
