SQL Server 事務隔離級別詳解

来源:http://www.cnblogs.com/guoxibaijv/archive/2017/02/22/6428085.html
-Advertisement-
Play Games

SQL 事務隔離級別 概述 隔離級別用於決定如果控制併發用戶如何讀寫數據的操作，同時對性能也有一定的影響作用。 步驟 事務隔離級別通過影響讀操作來間接地影響寫操作；可以在回話級別上設置事務隔離級別也可以在查詢（表級別）級別上設置事務隔離級別。事務隔離級別總共有6個隔離級別：READ UNCOMMIT ...


SQL 事務隔離級別

概述

     隔離級別用於決定如果控制併發用戶如何讀寫數據的操作，同時對性能也有一定的影響作用。

步驟

事務隔離級別通過影響讀操作來間接地影響寫操作；可以在回話級別上設置事務隔離級別也可以在查詢（表級別）級別上設置事務隔離級別。
事務隔離級別總共有6個隔離級別：
READ UNCOMMITTED(未提交讀,讀臟),相當於(NOLOCK)
READ COMMITTED(已提交讀,預設級別)
REPEATABLE READ(可以重覆讀),相當於(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已經提交讀隔離)
對於前四個隔離級別：READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE
隔離級別越高,讀操作的請求鎖定就越嚴格,鎖的持有時間久越長;所以隔離級別越高,一致性就越高,併發性就越低,同時性能也相對影響越大.

獲取事務隔離級別(isolation level)

DBCC USEROPTIONS

設置隔離

設置回話隔離
SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>
--註意:在設置回話隔離時(REPEATABLE READ)兩個單詞需要用空格間隔開,但是在表隔離中可以粘在一起(REPEATABLEREAD)

設置查詢表隔離
SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>)

1.READ UNCOMMITTED

READ UNCOMMITTED:未提交讀，讀臟數據
預設的讀操作:需要請求共用鎖,允許其他事物讀鎖定的數據但不允許修改.
READ UNCOMMITTED:讀操作不申請鎖,運行讀取未提交的修改,也就是允許讀臟數據,讀操作不會影響寫操作請求排他鎖.

 創建測試數據

複製代碼 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL,
Price FLOAT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00),(11,11.00),(12,12.00),(13,13.00),(14,14.00);
GO
SELECT ID,Price FROM Orders
複製代碼

新建回話1將訂單10的價格加1

複製代碼 
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
複製代碼

在另一個回話2中執行查詢操作

複製代碼 
首先不添加隔離級別,預設是READ COMMITTED,由於數據之前的更新操作使用了排他鎖，所以查詢一直在等待鎖釋放*/
SELECT ID,Price FROM Orders 
WHERE ID=10
---將查詢的隔離級別設置為READ UNCOMMITTED允許未提交讀，讀操作之前不請求共用鎖。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10;
--當然也可以使用表隔離,效果是一樣的
SELECT ID,Price FROM Orders WITH (NOLOCK)
WHERE ID=10
複製代碼

假設在回話1中對操作執行回滾操作,這樣價格還是之前的10,但是回話2中則讀取到的是回滾前的價格11,這樣就屬於一個讀臟操作

ROLLBACK TRANSACTION

2.READ COMMITTED

READ COMMITTED（已提交讀）是SQL SERVER預設的隔離級別，可以避免讀取未提交的數據，隔離級別比READ UNCOMMITTED未提交讀的級別更高;
該隔離級別讀操作之前首先申請並獲得共用鎖，允許其他讀操作讀取該鎖定的數據，但是寫操作必須等待鎖釋放，一般讀操作讀取完就會立刻釋放共用鎖。

新建回話1將訂單10的價格加1,此時回話1的排他鎖鎖住了訂單10的值

複製代碼 
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
複製代碼

在回話2中執行查詢，將隔離級別設置為READ COMMITTED

複製代碼 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10
---由於READ COMMITTED需要申請獲得共用鎖，而鎖與回話1的排他鎖衝突，回話被堵塞，

----在回話1中執行事務提交
COMMIT TRANSACTION
/*由於回話1事務提交，釋放了訂單10的排他鎖，此時回話2申請共用鎖成功查到到訂單10的價格為修改後的價格11，READ COMMITTED由於是已提交讀隔離級別，所以不會讀臟數據.
但是由於READ COMMITTED讀操作一完成就立即釋放共用鎖,讀操作不會在一個事務過程中保持共用鎖,也就是說在一個事務的的兩個查詢過程之間有另一個回話對數據資源進行了更改,會導致一個事務的兩次查詢得到的結果不一致,這種現象稱之為不可重覆讀.*/
複製代碼

重置數據

UPDATE Orders 
SET Price=10
WHERE ID=10

3.REPEATABLE READ

REPEATABLE READ(可重覆讀)：保證在一個事務中的兩個讀操作之間，其他的事務不能修改當前事務讀取的數據，該級別事務獲取數據前必須先獲得共用鎖同時獲得的共用鎖不立即釋放一直保持共用鎖至事務完成，所以此隔離級別查詢完並提交事務很重要。

在回話1中執行查詢訂單10,將回話級別設置為REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10

新建回話2修改訂單10的價格

UPDATE Orders 
SET Price=Price+1
WHERE ID=10
---由於回話1的隔離級別REPEATABLE READ申請的共用鎖一直要保持到事務結束，所以回話2無法獲取排他鎖，處於等待狀態

在回話1中執行下麵語句，然後提交事務

SELECT ID,Price FROM Orders 
WHERE ID=10
COMMIT TRANSACTION

回話1的兩次查詢得到的結果一致，前面的兩個隔離級別無法得到一致的數據，此時事務已提交同時釋放共用鎖，回話2申請排他鎖成功，對行執行更新

REPEATABLE READ隔離級別保證一個事務中的兩次查詢到的結果一致，同時保證了丟失更新
丟失更新：兩個事務同時讀取了同一個值然後基於最初的值進行計算，接著再更新，就會導致兩個事務的更新相互覆蓋。
例如酒店訂房例子，兩個人同時預定同一酒店的房間，首先兩個人同時查詢到還有一間房間可以預定，然後兩個人同時提交預定操作，事務1執行number=1-0，同時事務2也執行number=1-0最後修改number=0,這就導致兩個人其中一個人的操作被另一個人所覆蓋，REPEATABLE READ隔離級別就能避免這種丟失更新的現象，當事務1查詢房間時事務就一直保持共用鎖直到事務提交，而不是像前面的幾個隔離級別查詢完就是否共用鎖，就能避免其他事務獲取排他鎖。

 4.SERIALIZABLE

SERIALIZABLE(可序列化)，對於前面的REPEATABLE READ能保證事務可重覆讀，但是事務只鎖定查詢第一次運行時獲取的數據資源（數據行），而不能鎖定查詢結果之外的行，就是原本不存在於數據表中的數據。因此在一個事務中當第一個查詢和第二個查詢過程之間，有其他事務執行插入操作且插入數據滿足第一次查詢讀取過濾的條件時，那麼在第二次查詢的結果中就會存在這些新插入的數據，使兩次查詢結果不一致，這種讀操作稱之為幻讀。
為了避免幻讀需要將隔離級別設置為SERIALIZABLE

複製代碼 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
複製代碼

在回話1中執行查詢操作,並將事務隔離級別設置為REPEATABLE READ(先測試一下前面更低級別的隔離)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

在回話2中執行插入操作

INSERT INTO Orders VALUES(15,15.00,1)

返回回話1重新執行查詢操作並提交事務

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

結果回話1中第二次查詢到的數據包含了回話2新插入的數據，兩次查詢結果不一致（驗證之前的隔離級別不能保證幻讀）

重新插入測試數據

複製代碼 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
複製代碼

接下來將回話級別設置為SERIALIZABLE,在回話1中執行查詢操作,並將事務隔離級別設置為SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

在回話2中執行插入操作

INSERT INTO Orders VALUES(15,15.00,1)

返回回話1重新執行查詢操作並提交事務

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

兩次執行的查詢結果相同

 

重置所有打開回話的預設隔離級別

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

5.SNAPSHOT

SNAPSHOT快照：SNAPSHOT和READ COMMITTED SNAPSHOT兩種隔離（可以把事務已經提交的行的上一版本保存在TEMPDB資料庫中）
SNAPSHOT隔離級別在邏輯上與SERIALIZABLE類似
READ COMMITTED SNAPSHOT隔離級別在邏輯上與 READ COMMITTED類似
不過在快照隔離級別下讀操作不需要申請獲得共用鎖，所以即便是數據已經存在排他鎖也不影響讀操作。而且仍然可以得到和SERIALIZABLE與READ COMMITTED隔離級別類似的一致性；如果目前版本與預期的版本不一致，讀操作可以從TEMPDB中獲取預期的版本。

如果啟用任何一種基於快照的隔離級別，DELETE和UPDATE語句在做出修改前都會把行的當前版本複製到TEMPDB中，而INSERT語句不需要在TEMPDB中進行版本控制，因為此時還沒有行的舊數據

無論啟用哪種基於快照的隔離級別都會對更新和刪除操作產生性能的負面影響，但是有利於提高讀操作的性能因為讀操作不需要獲取共用鎖；

5.1SNAPSHOT

SNAPSHOT 在SNAPSHOT隔離級別下,當讀取數據時可以保證操作讀取的行是事務開始時可用的最後提交版本
同時SNAPSHOT隔離級別也滿足前面的已提交讀,可重覆讀,不幻讀;該隔離級別實用的不是共用鎖,而是行版本控制
使用SNAPSHOT隔離級別首先需要在資料庫級別上設置相關選項

在打開的所有查詢視窗中執行以下操作

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

重置測試數據

複製代碼 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
複製代碼 複製代碼 
在回話1中打開事務,將訂單10的價格加1,並查詢跟新後的價格
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

SELECT ID,Price,type FROM Orders
WHERE ID=10
---查詢到更新後的價格為11

---在回話2中將隔離級別設置為SNAPSHOT,並打開事務(此時查詢也不會因為回話1的排他鎖而等待,依然可以查詢到數據)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

---查詢到的結果還是回話1修改前的價格,由於回話1在預設的READ COMMITTED隔離級別下運行,SQL SERVER必須在更新前把行的一個副本複製到TEMPDB資料庫中
--在SNAPSHOT級別啟動事務會請求行版本

---現在在回話1中執行提交事務,此時訂單10的價格為11
COMMIT TRANSACTION

---再次在回話二中查詢訂單10的價格並提交事務,結果還是10,因為事務要保證兩次查詢的結果相同

SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

---此時如果在回話2中重新打開一個事務,查詢到的訂單10的價格則是11
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

/*SNAPSHOT隔離級別保證操作讀取的行是事務開始時可用的最後已提交版本,由於回話1的事務未提交,所以訂單10的最後提交版本還是修改前的價格10,所以回話2讀取到的價格是回話2事務開始前的已提交版本價格10,當回話1提交事務後,回話2重新新建一個事務此時事務開啟前的價格已經是11了，所以查詢到的價格是11，同時SNAPSHOT隔離級別還能保證SERIALIZABLE的隔離級別*/
複製代碼

5.2READ COMMITTED SNAPSHOT

READ COMMITTED SNAPSHOT也是基於行版本控制,但是READ COMMITTED SNAPSHOT的隔離級別是讀操作之前的最後已提交版本,而不是事務前的已提交版本，有點類似前面的READ COMMITTED能保證已提交讀,但是不能保證可重覆讀,不能避免幻讀,但是又比 READ COMMITTED隔離級別多出了不需要獲取共用鎖就可以讀取數據

要啟用READ COMMITTED SNAPSHOT隔離級別同樣需要修改資料庫選項，在回話1，回話2中執行以下操作(執行下麵的操作當前連接必須是資料庫的唯一連接，可以通過查詢已連接當前資料庫的進程,然後KILL掉那些進程,然後再執行該操作,否則可能無法執行成功)

複製代碼 
ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

-----在回話1中打開事務,將訂單10的價格加1,並查詢跟新後的價格,並保持事務一直處於打開狀態
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

--查詢到的價格是11
SELECT ID,Price,type FROM Orders
WHERE ID=10

---在回話2中打開事務查詢訂單10並一直保持事務處於打開狀態(此時由於回話1還未提交事務,所以回話2中查詢到的還是回話1執行事務之前保存的行版本)
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10
--查詢到的價格還是10

---在回話1中提交事務
COMMIT TRANSACTION 

---在回話2中再次執行查詢訂單10的價格,並提交事務
SELECT ID,Price,type FROM Orders
WHERE ID=10
COMMIT TRANSACTION 
--此時的價格為回話1修改後的價格11,而不是事務之前已提交版本的價格,也就是READ COMMITTED SNAPSHOT隔離級別在同一事務中兩次查詢的結果不一致.
複製代碼

關閉所有連接,然後打開一個新的連接,禁用之前設置的資料庫快照隔離級別選項

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

 

 

總結

   理解了事務隔離級別有助於理解事務的死鎖。

 

轉自：http://www.cnblogs.com/chenmh/p/3998614.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SQL基礎筆記
    Codecademy中Learn SQL, SQL: Table Transformaton和SQL: Analyzing Business Metrics三門課程的筆記，以及補充的附加筆記。 Codecademy的課程以SQLite編寫，筆記中改成了MySQL語句。 I. Learn SQL 1. ...
  • ORACLE RAC集群原理
    ORACLE RAC原理：在一個應用環境當中，所有的伺服器使用和管理同一個資料庫，目的是為了分散每一臺伺服器的工作量，硬體上至少需要兩台以上的伺服器，而且還需 要一個共用存儲設備。同時還需要兩類軟體，一個是集群軟體，另外一個就是Oracle資料庫中的RAC組件。同時所有伺服器上的OS都應該是同一類O ...
  • MySQL資料庫基礎（二）（約束以及修改數據表）（持續更新中）
    一，約束以及修改數據表 約束的作用？1.約束保證數據的完整性、一致性；2.約束分為表級約束、列級約束；3.約束類型包括：NOT NULL（非空約束）、PRIMARY KEY（主鍵約束）、UNIQUE KEY（唯一約束）、DEFAULT（預設約束）、FOREIGN KEY（外鍵約束）； 列級約束：只針 ...
  • oracle 表 視圖 存儲過程 序列 job
    table 表 --delete table drop table Test1; -- Create table create table TEST1 ( ID NUMBER, T_NAME VARCHAR2(100), DT DATE ); -- 添加註釋 comment on column TE ...
  • 在CentOS 7中安裝Redis 3.2.8
    Redis 3.x.x系列的安裝於之前的2.x.x略有不同，需要先編譯依賴。 ...
  • 【CNMP系列】CentOS7.0下安裝MySql5.6服務
    接上一回的話，CentOS7.0下安裝好了Nginx服務，對於我們的CNMP，我們可以開始我們的M啦，就是傳統意義上的MySql服務 MySql簡介 MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，目前屬於 Oracle 旗下產品。在 WEB 應用方面，MySQL是最好的 R ...
  • MySQL資料庫基本操作命令
    1. 本地連接MySQL資料庫： Mysql –h 連接地址（本地為localhost） -u 用戶名（root） -p[密碼] [資料庫名]; 舉例 ：mysql –h localhost –u root –p123456 db_test(註意：-p後不能有空格) 密碼和資料庫名可以不加。密碼不加 ...
  • 【SQL基礎】三種類別語句
    SQL 語句主要可以劃分為以下 3 個類別。 DDL（Data Definition Languages）語句：數據定義語言，這些語句定義了不同的數據段、資料庫、表、列、索引等資料庫對象的定義。常用的語句關鍵字主要包括 create、drop、alter等。 就是對資料庫內部的對象進行創建、刪除、修 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類