碼迷,www.tparu.icu
吉利平特名人堂 > 數據庫 > 詳細

014期平特复式四连肖:SQL Server 中的事務與事務隔離級別以及如何理解臟讀, 未提交讀,不可重復讀和幻讀產生的過程和原因

時間:2019-05-23 18:18:27      閱讀:28      評論:0      收藏:0      [點我收藏+]

吉利平特名人堂 www.tparu.icu 標簽:調整   總結   primary   dir   bsp   運行   更新   序列化   mamicode   

本文轉自:https://www.cnblogs.com/Leo_wl/p/3339274.html

 

原本打算寫有關 SSIS Package 中的事務控制過程的,但是發現很多基本的概念還是需要有 SQL Server 事務和事務的隔離級別做基礎鋪墊。所以花了點時間,把 SQL Server 數據庫中的事務概念,ACID 原則,事務中常見的問題,問題造成的原因和事務隔離級別等這些方面的知識好好的整理了一下。

其實有關 SQL Server 中的事務,說實話因為內容太多, 話題太廣,稍微力度控制不好就超過了我目前知識能力范圍,就不是三言兩語能夠講清楚的。所以希望大家能夠指出其中總結的不足之處,對我來說多了提高的機會,更可以幫助大家加深對事務的理解。


本文涉及到的知識點:

  • SQL Server 數據庫中事務的概念
  • ACID 原則 (加了一部分內容專門解釋原子性,提到了顯示事務以及 XACT_ABORT 機制來確保事務的原子性)
  • 列出事務中常見的問題以及原因:臟讀,未提交讀,不可重復讀,幻讀 
  • SQL Server中 事務的隔離級別以及它們如何做到避免臟讀,未提交讀,不可重復讀和幻讀 (用代碼描述了這些問題,并且使用時間序來解釋產生的原因)

SQL Server 數據庫中事務的概念

數據庫中的事務是數據庫并發控制的基本單位,一條或者一組語句要么全部成功,對數據庫中的某些數據成功修改; 要么全部不成功,數據庫中的數據還原到這些語句執行

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


數據庫中事務的 ACID 原則

原子性 (Atomictiy):事務的原子性是指一個事務中包含的一條語句或者多條語句構成了一個完整的邏輯單元,這個邏輯單元具有不可再分的原子性。這個邏輯單元要么一起提交執行全部成功,要么一起提交執行全部失敗。

一致性 (Consistency):可以理解為數據的完整性,事務的提交要確保在數據庫上的操作沒有破壞數據的完整性,比如說不要違背一些約束的數據插入或者修改行為。一旦破壞了數據的完整性,SQL Server 會回滾這個事務來確保數據庫中的數據是一致的。

隔離性(Isolation):與數據庫中的事務隔離級別以及鎖相關,多個用戶可以對同一數據并發訪問而又不破壞數據的正確性和完整性。但是,并行事務的修改必須與其它并行事務的修改相互獨立,隔離。 但是在不同的隔離級別下,事務的讀取操作可能得到的結果是不同的。

持久性(Durability):數據持久化,事務一旦對數據的操作完成并提交后,數據修改就已經完成,即使服務重啟這些數據也不會改變。相反,如果在事務的執行過程中,系統服務崩?;蛘咧仄?,那么事務所有的操作就會被回滾,即回到事務操作之前的狀態。

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

原子性的進一步理解

關于原子性,有必要在這里多補充一下,因為我們描述的概念是指在事務中的原子性。一條 SQL 語句和多條 SQL 語句在處理原子性上是有一些區別的,下面演示了這些區別。

先運行這些代碼,創建一個非常簡單的測試表,這張表只簡單模擬了一個賬戶的 ID 和賬戶余額。

技術圖片
技術圖片
USE BIWORK_SSIS
GO

IF OBJECT_ID(‘dbo.Account‘) IS NOT NULL
DROP TABLE dbo.Account
GO

CREATE TABLE dbo.Account
(
  ID INT PRIMARY KEY,
  AccountBalance MONEY CHECK(AccountBalance >= 0)
)
技術圖片
技術圖片

單條 SQL 語句的原子性

插入一條測試語句,然后再查詢一下結果。

技術圖片

這里提到了自動提交事務,這時 T-SQL 默認的事務方式,它是一種能夠自動執行并能夠自動回滾事務的處理方式。SQL Server 除了自動提交事務之外,還有顯示事務和隱式事務,暫時不在這篇文章中討論它們的區別了。

上面的兩個自動提交事務中,每一個自動提交事務只包含一條 SQL 語句,不能再分,要么成功,要么失敗。

再比如,在一條 SQL 語句中插入多條數據時,其中一條數據是符合約束的。但因為另外一條數據違反了檢查約束,這樣也會導致整個 Insert 語句失敗,因此沒有一條數據能夠插入到數據表中。

技術圖片

多條 SQL 語句形成的一個整體的原子性

假設下面的這兩條 Insert 語句構成一個具備原子性特征的邏輯單元,是一個整體需要形成一個事務,那么應該如何處理。

INSERT INTO dbo.Account VALUES(1004,-1)
INSERT INTO dbo.Account VALUES(1005,500)

很顯然如果直接這么執行的話,1004 插入失敗,1005 可以插入成功,這樣就是兩個不同的事務了。SQL Server 提供了兩種方式來確保這種包含多組 SQL 語句的邏輯塊具備原子性特征。

方式一 - 使用顯示事務組合多條 SQL 語句構成一個整體以實現事務的原子性

第一種就是非常常見的顯示事務,通過顯示的使用 BEGIN TRANSACTION, COMMIT TRANSACTION 以及 ROLLBACK TRANSACTION 命令將一組 SQL 語句形成一個完整的事務來提交,提交要么成功,要么失敗。

技術圖片
技術圖片
-- 開始一個事務
BEGIN TRANSACTION

-- TRY CATCH 語句
BEGIN TRY

 -- 這一條會違反檢查約束,插入失敗
    INSERT INTO dbo.Account VALUES(1004,-1)
 -- 這一條會插入成功,但此時事務還未真正提交
    INSERT INTO dbo.Account VALUES(1005,500)

END TRY
BEGIN CATCH
 -- 發生錯誤,事務回滾
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

-- 沒有進入 CATCH 塊,提交事務
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO
技術圖片
技術圖片

當然最終的結果就是事務回滾,一條數據都沒有插入到數據表中,所以失敗時就全部失敗,確保了事務的原子性。

方式二 - 通過設置  XACT_ABORT 為 ON 來確保事務的原子性

先來看默認的設置,當  XACT_ABORT 為 OFF 狀態的時候。

技術圖片
技術圖片
-- SET XACT_ABORT OFF - 默認的 SQL Server 設置
SET XACT_ABORT OFF
BEGIN TRANSACTION
 -- 這一條會違反檢查約束,插入失敗
    INSERT INTO dbo.Account VALUES(1004,-1)
 -- 這一條會插入成功
 INSERT INTO dbo.Account VALUES(1005,500)
COMMIT TRANSACTION
技術圖片
技術圖片

當  XACT_ABORT 為 OFF 狀態即 SQL Server 默認設置下,上面的事務中,SQL Server 在通常情況下只會回滾執行失敗的語句,也就是說只會回滾 1004 這條數據,而 1005 會插入成功。很顯然,這違背了事務的原子性,因為我們也沒有顯示的寫出要 ROLLBACK TRANSACTION 來。

OK!那我們將 XACT_ABORT 設置為 ON,這時就告訴了它后面的事務,如果遇到錯誤就立即終止事務并回滾。這樣不通過顯示的 ROLLBACK TRANSACTION 也可以確保事務的原子性。

技術圖片

在上面的這個例子中,只有事務 2 會成功提交,而事務1和3會回滾,插入操作執行失敗。

注意一點,上面的每個事務后面加了一個 GO 關鍵字,如果不加 GO 這個關鍵字,一起執行這些 SQL 語句會導致事務2和3因為事務1的執行失敗而不能執行到, GO 關鍵字形成了一個批處理,表示前面的一組 SQL 語句一起處理。

GO 關鍵字非常有意思,GO 后面可以加上次數,表示前面的一條或者一組 SQL 執行幾次。

技術圖片

通過上面的示例,應該可以理解原子性與事務的關系了,以及如何實現事務的原子性。

技術圖片


事務中常見的問題

了解完事務的 ACID 的原則后,再來看看在 SQL Server 中多用戶并發的情況下,使用事務可能會遇到的一些情況:

臟讀 (Dirty Reads) : 一個事務正在訪問并修改數據庫中的數據但是沒有提交,但是另外一個事務可能讀取到這些已作出修改但未提交的數據。這樣可能導致的結果就是所有的操作都有可能回滾,比如第一個事務對數據做出的修改可能違背了數據表的某些約束,破壞了完整性,但是恰巧第二個事務卻讀取到了這些不正確的數據造成它自身操作也發生失敗回滾。

不可重復讀取(Non-Repeatable Reads):  A 事務兩次讀取同一數據,B事務也讀取這同一數據,但是 A 事務在第二次讀取前B事務已經更新了這一數據。所以對于A事務來說,它第一次和第二次讀取到的這一數據可能就不一致了。

幻讀(Phantom Reads): 與不可重復讀有點類似,都是兩次讀取,不同的是 A 事務第一次操作的比如說是全表的數據,此時 B 事務并不是只修改某一具體數據而是插入了一條新數據,而后 A 事務第二次讀取這全表的時候就發現比上一次多了一條數據,發生幻覺了。

更新丟失(Lost Update): 兩個事務同時更新,但由于某一個事務更新失敗發生回滾操作,這樣有可能的結果就是第二個事務已更新的數據因為第一個事務發生回滾而導致數據最終沒有發生更新,因此兩個事務的更新都失敗了。


SQL Server 中事務的隔離級別以及與臟讀,不可重復讀,幻讀等關系(代碼論證和時間序)

了解了在并發訪問數據庫的情況下可能會出現這些問題,就可以繼續了解數據庫隔離級別這樣的一個概念,通俗一點講就是:你希望通過何種方式讓并發的事務隔離開來,隔離到什么程度?比如可以容忍臟讀,或者不希望并發的事務出現臟讀的情況,那么這些可以通過隔離級別的設置使得并發事務之間的隔離程度變得寬松或者很嚴峻。

隔離級別越高,讀取臟數據或者造成數據不統一不完整的機會就越少,但是在高并發的系統中,性能降低就越嚴重。隔離級別越低,并發系統中性能上提升很大,但是數據本身可能不完整。

在 SQL Server 2012 中可以通過這樣的語法來設置事務的隔離級別 (從低到高排列):

技術圖片
技術圖片
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]
技術圖片
技術圖片

下面通過代碼示例來演示各個事務隔離級別的表現,運行下面 SQL 語句,插入一條測試語句。

技術圖片
技術圖片
TRUNCATE TABLE BIWORK_SSIS.dbo.Account
GO

INSERT INTO BIWORK_SSIS.dbo.Account VALUES(1001,1000)

SELECT * FROM BIWORK_SSIS.dbo.Account
GO
技術圖片
技術圖片

Read Uncommitted (未提交讀)

隔離級別最低,容易產生的問題就是臟讀,因為可以讀取其它事務修改了的但是沒有提交的數據。它的作用跟在事務中 SELECT 語句對象表上設置 (NOLOCK) 相同。

打開兩個查詢窗口,第一個窗口表示事務 A, 第二個窗口表示事務B。 事務A 保持默認的隔離級別,事務B 設置它們的隔離級別為 READ UNCOMMITTED, 可以通過 DBCC USEROPITIONS 查看更改后的結果。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
DBCC USEROPTIONS

測試步驟:

先執行事務 A 的 SQL 代碼

技術圖片
技術圖片
BEGIN TRANSACTION

UPDATE BIWORK_SSIS.dbo.Account
SET AccountBalance = 500 
WHERE ID  = 1001

WAITFOR DELAY ‘00:00:10‘

ROLLBACK TRANSACTION

SELECT * FROM BIWORK_SSIS.dbo.Account WHERE ID = 1001
技術圖片
技術圖片

馬上接著再執行 事務 B 的 SQL 代碼 

技術圖片
技術圖片
-- 第1次查詢 發生在 A 事務未提交或者回滾之前
SELECT * FROM BIWORK_SSIS.dbo.Account WHERE ID = 1001

WAITFOR DELAY ‘00:00:10‘

-- 第2次查詢 發生在 A 事務回滾之后
SELECT * FROM BIWORK_SSIS.dbo.Account WHERE ID = 1001
技術圖片
技術圖片

技術圖片

可以看出,事務 B 對 ID = 1001 的這條數據進行了兩次讀取,但是很顯然第一次讀取的數據是臟數據。下面模擬了一下它們發生的時序,雖然不算嚴謹,但是可以幫助理解臟讀產生的原因。

技術圖片

還可以把事務B 的隔離級別改回來成為默認的  READ COMMITTED,然后運行完事務 A 之后馬上運行帶有 NOLOCK 的查詢,效果和上面描述的也是一致的。 一旦加上 NOLOCK,可以認為它的作用就等同于隔離級別為 READ UNCOMMITTED。

SELECT * FROM BIWORK_SSIS.dbo.Account WITH(NOLOCK) WHERE ID = 1001

 

Read Committed (已提交讀)

這是 SQL Server 的默認設置,已提交讀,可以避免臟讀,可以滿足大多數要求。事務中的語句不能讀取已由其它事務做出修改但是還未提交的數據,但是能夠讀取由其它事務做出修改并提交了的數據。也就是說,有可能會出現 Non-Repeatable Reads 不可重復讀取和 Phantom Reads 幻讀的情況,因為當前事務中可能出現兩次讀取同一資源,但是兩次讀取的過程之間,另外一事務可能對這一資源完成了讀取更新并提交的行為,這樣數據前后可能就不一致了。因此,這一個默認的隔離級別能夠解決臟讀但是解決不了 Non-Repeatable Reads 不可重復讀。

接著上一個例子,看看如果將隔離級別設置為 READ COMMITTED,能否避免臟讀? 還是先運行事務 A,再接著運行事務 B。

技術圖片

因為已提交讀不能讀取已由其它事物做出修改但是還未提交的數據,因此事務B 就必須等待事務 A 完成對數據的修改提交或者回滾之后才能開始讀取。運行事務A 和事務B,明顯事務B 有一個等待事務A提交或者回滾的過程,看看它們的時序圖。

技術圖片

由此可以看出隔離級別 READ COMMITTED 可以避免臟讀,但是也有可能出現其它的問題,請看這個例子。先執行事務A,接著直接執行事務 B。

技術圖片

從上面的執行結果來看,很明顯在事務 A 中,同一個事務中對 ID  = 1001 的取值出現了前后不一致的情況。假設這里不是簡單的查詢,而是先查詢賬戶余額有 1000元錢,然后后面的動作就是取 1000元錢,很明顯第二次取的時候發現只有 500 元了。原因就是在第一次查詢和取的間隙之間被事務 B 鉆了空子,修改了余額。這種情況就是上面所介紹到的不可重復讀取,請看下面的時序圖。

技術圖片

所以 READ COMMITTED 已提交讀隔離級別能夠避免臟讀,但是仍然會遇到不可重復讀取的問題。

Repeatable Read (可重復讀)

不能讀取已由其它事務修改了但是未提交的行,其它任何事務也不能修改在當前事務完成之前由當前事務讀取的數據。但是對于其它事務插入的新行數據,當前事務第二次訪問表行時會檢索這一新行。因此,這一個隔離級別的設置解決了 Non-Repeatable Reads 不可重復讀取的問題,但是避免不了 Phantom Reads 幻讀。

接著上面的例子做出一些修改,增加了一些查詢,記得把 ID = 1001 的余額改回 1000。將事務 A 的隔離級別設置為 REPEATABLE READ 可重復讀級別,來看看這個隔離級別的表現。

技術圖片

盡管在最后的查詢結果中, ID  = 1001 的余額為 500 元,但是在事務 A 中的兩次讀取一次發生在 事務 B 開始之前,一次發生在 事務 B 提交之后,但是它們讀取的余額是保持一致的,看不到事務 B 對這個值的修改。

技術圖片

從上面的時序圖中可以看出,事務 A 第一次讀取到的 ID = 1001 的余額值和第二次讀取到的是一樣的,可以理解為在事務 A 的查詢期間是不允許事務 B 修改這個值的。 因為事務 A 確實沒有看到這個變化,所以事務A 也確實認為事務B 聽了它的話,沒有做出 Update 的操作。但是實際上,事務 B 已經完成了這個操作,只不過由于 事務 A 中隔離級別設置為 REPEATABLE READ 可重復讀,所以兩次讀取的結果始終保持著一致。

那么這里的示例是事務B在修改數據,如果是新增加一行記錄呢?

技術圖片

事務 A 又開始暈菜了!居然兩次查詢的結果不一樣,第二次查詢多了一條數據,這就是幻讀!

技術圖片

 

SNAPSHOT (快照隔離)

可以解決幻讀 Phantom Reads 的問題,當前事務中讀取的數據在整個事務開始到事務提交結束之間,這個數據版本是一致的。其它的事務可能對這些數據做出修改,但是對于當前事務來說它是看不到這些變化。有點類似于當前事務拿到這個數據的時候是拿到這個數據的快照,因此在這個快照上做出的操作同一事務中前后幾次操作都是基于同一數據版本。因此,這一個隔離級別的設置可以解決 Phantom Reads 幻讀問題。但是要注意的是,其它事務是可以在當前事務完成之前修改由當前事務讀取的數據。

在使用 SNAPSHOT 之前要注意,默認情況下數據庫不允許設置 SNAPSHOT 隔離級別,直接設置會出現類似于這樣的錯誤:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 3952, Level 16, State 1, Line 8

Snapshot isolation transaction failed accessing database ‘BIWORK_SSIS‘ because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

所以要使用 SET 命令開啟這個支持

ALTER DATABASE BIWORK_SSIS
SET ALLOW_SNAPSHOT_ISOLATION ON

并且在開始前先清空其它的 ID,只保留 ID = 1001 的這條記錄。

DELETE FROM BIWORK_SSIS.dbo.Account
WHERE ID <> 1001

技術圖片

這樣通過設置隔離級別是 SNAPSHOT就解決了幻讀的問題,保證了在事務 A 中查詢的數據行版本是前后一致的。

技術圖片

但是大家發現沒有?無論在事務 A 中使用 Repeatable Read 還是 Snapshot 仍然不可避免的阻止事務B 對共享的資源做出了修改,盡管這個修改沒有被事務 A 發現,事務 A 中的數據還是保持了一致,但是實際上還是做出了修改。只要事務 A 一提交結束,馬上就可以看到事務 B 做出的這些修改已經生效了?;毓酥疤岬降?,如果我第一次查詢有1000元,第二次動作可能就是取1000元。在這兩次動作之間另外的一個事務對金額做出了修改,盡管我兩次讀取都是1000元,但是實際上是不符合常理的。要么,我先查詢然后再取款這個動作是連貫的,然后另外一個事務再對金額做出修改。要么,其它事務先對金額做出修改,比如扣去500元,那么我再查詢再取款這個錢數還是一致的。也就是說,在事務 A 對某一個資源做出操作的時候,形成了獨占,事務 B 進不來?;蛘呤攣?B 在對這個資源做操作的時候,事務 A 也必須等待事務 B 結束后才能開始它的事務,那么這里就要使用到最嚴格的隔離級別了 - SERIALIZABLE。

 

SERIALIZABLE(序列化)

性能最低,隔離級別最高最嚴格,可以幾乎上面提到的所有問題。比如不能讀取其它已由其它事務修改但是沒有提交的數據,不允許其它事務在當前事務完成修改之前修改由當前事務讀取的數據,不允許其它事務在當前事務完成修改之前插入新的行。它的作用與在事務內所有 SELECT 語句中的所有表上設置 HOLDLOCK 相同,并發級別比較低但又對安全性要求比較高的時候可以考慮使用。如果并發級別很高,使用這個隔離級別,性能瓶頸將非常嚴重。

將事務 A 的隔離級別調整成 SERIALIZABLE,然后執行 A 然后再執行 B。

技術圖片

在這里可以看到事務B 的執行基本上是在事務A提交之后才開始的,當事務 A 在執行的時候,事務 B 因為也要訪問這個資源所以一直阻塞在那里直到事務 A 提交。 并不是說事務 B 沒有開始,而是說在執行 SELECT 查詢的時候因為事務 A 占用了這個資源,所以處于等待狀態。

技術圖片

在 SQL Server 中設置隔離級別要注意:一次只能設置一個隔離級別的選項,并且設置的隔離級別對當前連接一直有效直到顯式修改為止。事務中執行的所有讀取操作也都會在指定的隔離級別規則下運行,除非在 SELECT 操作語句中對表指定了其它的鎖或者版本控制行為。

注:上面的時序圖只是用來幫助理解事務的隔離級別,只是一個大概的執行順序,當然也跟我執行事務 A 和 事務 B 的時間點相關,所以并不能真正反映實際過程中 SQL 語句提交和執行的實際順序,真正提交的過程可以通過 SQL Profiler 去跟蹤看看。

SQL Server 中的事務與事務隔離級別以及如何理解臟讀, 未提交讀,不可重復讀和幻讀產生的過程和原因

標簽:調整   總結   primary   dir   bsp   運行   更新   序列化   mamicode   

原文地址:https://www.cnblogs.com/doggod/p/10913526.html

(0)
(0)
   
舉報
評論 一句話評論(0
0條  
登錄后才能評論!
? 2014 吉利平特名人堂 版權所有 京ICP備13008772號-2
迷上了代碼!
双色球计划方法 北京pk10直播开奖直播 北京pk10高手看走势 7星彩预测号码 极速快三计划软件免费下载 500彩票幸运快三计划软件 6码复式三中三共几组 在手机上炸金花有什么技巧 网上通比牛牛有假吗 二八杠生死门八个口 飞艇计划手机版 分分彩人工计划精准版 看牌抢庄斗牛赢现金 棋牌满20元提现 黑龙江11选五计划软件 11选5五码能组出多少组