SQL Server Tnscation Isolation Level Test

Database > SQLServer SQLServer
發布時間 :
字數:1.9k 觀看數 : 評論:
  1. 前言
  2. 測試資料
  3. 異常狀況說明
  4. Read Uncommitted
    1. (V) 髒讀 (Dirty Read) 重現測試
    2. (V) 不可重複讀取（Nonrepeatable Read） 重現測試
    3. (V) 幻讀（Phantom Read） 重現測試
  5. Read Committed
    1. (X) 髒讀 (Dirty Read) 重現測試
    2. (V) 不可重複讀取（Nonrepeatable Read） 重現測試
    3. (V) 幻讀（Phantom Read） 重現測試
  6. Repeatable Read
    1. (X) 髒讀 (Dirty Read) 重現測試
    2. (X) 不可重複讀取（Nonrepeatable Read） 重現測試
    3. (V) 幻讀（Phantom Read） 重現測試
  7. Serializable
    1. (X) 髒讀 (Dirty Read) 重現測試
    2. (X) 不可重複讀取（Nonrepeatable Read） 重現測試
    3. (X) 幻讀（Phantom Read） 重現測試
  8. Dead Lock 重現範例
  9. 補充
  10. 參考文件

前言

測試不同隔離層級發生異常狀況

測試資料

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACIDSample1]') AND type in (N'U'))
DROP TABLE ACIDSample1
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACIDSample2]') AND type in (N'U'))
DROP TABLE ACIDSample2


CREATE TABLE ACIDSample1 (ID INT, Name NVARCHAR(20))
CREATE TABLE ACIDSample2 (ID INT, Name NVARCHAR(20))

DELETE ACIDSample1
INSERT ACIDSample1
SELECT 1,'Tom'
UNION
SELECT 2,'Jack'

DELETE ACIDSample2
INSERT ACIDSample2
SELECT 1,'Leo'
UNION
SELECT 2,'James'

ACIDSample1

ID Name
1 Tom
2 Jack

ACIDSample2

ID Name
1 Leo
2 James

異常狀況說明

  • 髒讀 (Dirty Read)
    當A交易還沒有 Commit，而B交易卻已經讀到更新後的資料

  • 不可重複讀取（Nonrepeatable Read）
    當A交易讀取了一筆資料，而B交易更新了該筆資料並 Commit 後，A交易再次讀取該筆資料，發現值已經改變

  • 幻讀（Phantom Read）
    當A交易讀取了一個結果集，而B交易(新增 or 更新 or 刪除)了某些資料並 Commit 後
    A交易再次讀取相同查詢條件的結果集，發現結果集已經改變

Read Uncommitted

可讀取到其他交易中未 Commit 的資料
會發生髒讀 (Dirty Read)、不可重複讀取（Nonrepeatable Read）、幻讀（Phantom Read）

(V) 髒讀 (Dirty Read) 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時已讀到未 commit 的 Jack_upd)
ROLLBACK
COMMIT

(V) 不可重複讀取（Nonrepeatable Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到 Jack)
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
COMMIT
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到 Jack_upd 與上次查詢的值不同)
COMMIT

(V) 幻讀（Phantom Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到一筆 Jack)
INSERT INTO ACIDSample1 SELECT 2, 'Jack_ins'
(ID = 2 新增一筆資料)
COMMIT
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到兩筆資料 Jack、Jack_ins)
COMMIT

Read Committed

SQL SERVER 預設的隔離層級，僅能讀取到其他交易中已 Commit 的資料
可避免髒讀 (Dirty Read)，但仍會發生不可重複讀取（Nonrepeatable Read）、幻讀（Phantom Read）

(X) 髒讀 (Dirty Read) 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時查詢會卡住)
ROLLBACK
COMMIT

(V) 不可重複讀取（Nonrepeatable Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到 Jack)
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
COMMIT
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到 Jack_upd 與上次查詢的值不同)
COMMIT

(V) 幻讀（Phantom Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到一筆 Jack)
INSERT INTO ACIDSample1 SELECT 2, 'Jack_ins'
(ID = 2 新增一筆資料)
COMMIT
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到兩筆資料 Jack、Jack_ins)
COMMIT

Repeatable Read

在該隔離層級，交易內參考到的資源，其他交易無法修改
確保交易內讀取到的資料是一致性的，不受其他交易影響
可避免髒讀 (Dirty Read)、不可重複讀取（Nonrepeatable Read），但仍會發生 幻讀（Phantom Read）

(X) 髒讀 (Dirty Read) 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時查詢會卡住)
ROLLBACK
COMMIT

(X) 不可重複讀取（Nonrepeatable Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到 Jack)
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
(此時更新會卡住)
SELECT * FROM ACIDSample1 WHERE ID = 2
(仍然讀到 Jack)
COMMIT
(因Session2 的鎖已釋放，資料已更新成 Jack_upd)
COMMIT

(V) 幻讀（Phantom Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到一筆 Jack)
INSERT INTO ACIDSample1 SELECT 2, 'Jack_ins'
(ID = 2 新增一筆資料)
COMMIT
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到兩筆資料 Jack、Jack_ins)
COMMIT

Serializable

為最高的隔離層級，在A交易內讀取、寫入完全隔離，其他交易會被卡住，只能等A交易處理完後才會繼續執行

(X) 髒讀 (Dirty Read) 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時查詢會卡住)
ROLLBACK
COMMIT

(X) 不可重複讀取（Nonrepeatable Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到 Jack)
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
(此時更新會卡住)
SELECT * FROM ACIDSample1 WHERE ID = 2
(仍然讀到 Jack)
COMMIT
(因Session2 的鎖已釋放，資料已更新成 Jack_upd)
COMMIT

(X) 幻讀（Phantom Read） 重現測試

Session1 Session2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時讀到一筆 Jack)
INSERT INTO ACIDSample1 SELECT 2, 'Jack_ins'
(此時新增會卡住)
SELECT * FROM ACIDSample1 WHERE ID = 2
(仍然讀到一筆 Jack)
COMMIT
(因Session2 的鎖已釋放，資料新增了一筆 Jack_ins)
COMMIT

Dead Lock 重現範例

當死鎖發生時，SQL SERVER　會自行判斷要讓哪一方執行成功，另一方執行失敗則自動 ROLLBACK

  • Sample 1 (REPEATABLE READ 更新同張表)
Session1 Session2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM ACIDSample1 WHERE ID = 2
(加上 S鎖)
SELECT * FROM ACIDSample1 WHERE ID = 2
(加上 S鎖)
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
(此時更新卡住 因 S鎖與 X鎖 互斥，需等 Session 2 釋放鎖)
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
(此時發生 Dead Lock，因需要等 Session 1 釋放鎖，兩邊互等造成死結)
ROLLBACK
  • Sample 2 (撈取、更新不同表 (Read Committed、Repeatable Read、Serializable 皆會發生))
Session1 Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN		 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE ACIDSample1 SET Name='Jack_upd' WHERE ID = 2
(加上 X鎖)
UPDATE ACIDSample2 SET Name='James_upd' WHERE ID = 2
(加上 X鎖)
SELECT * FROM ACIDSample1 WHERE ID = 2
(此時查詢卡住 因 S鎖與 X鎖 互斥，需等 Session 2 釋放鎖)
SELECT * FROM ACIDSample2 WHERE ID = 2
(此時發生 Dead Lock，因需要等 Session 1 釋放鎖，兩邊互等造成死結)
ROLLBACK

補充

查詢鎖定狀態語法

SELECT
        tl.request_session_id AS [我的SID]
        ,tl.resource_type AS [資源類型]
        ,DB_NAME(tl.resource_database_id) AS [資料庫名稱]
        ,(CASE resource_type
                WHEN 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
                ELSE (SELECT
                                        OBJECT_NAME(object_id)
                                FROM sys.partitions
                                WHERE hobt_id = resource_associated_entity_id)
        END) AS [物件名稱]
        ,tl.resource_description AS [資源說明]
        ,tl.request_mode AS [鎖定類型]
        ,tl.request_status AS [狀態]
        ,wt.blocking_session_id AS [被阻塞SID]
        ,c.connect_time AS [連接時間]
        ,txt.text AS [最近執行語法]
       ,lock_txt.text AS [被阻塞的執行語法]
FROM sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_os_waiting_tasks AS wt
        ON tl.lock_owner_address = wt.resource_address
LEFT JOIN sys.dm_exec_connections AS c
        ON tl.request_session_id = c.session_id
LEFT JOIN sys.dm_exec_connections AS d
        ON wt.blocking_session_id = d.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) txt 
OUTER APPLY sys.dm_exec_sql_text(d.most_recent_sql_handle) lock_txt 
WHERE resource_type != 'DATABASE'
  AND tl.request_session_id > 50 
ORDER BY tl.request_session_id
GO

參考文件

STEVEN-朱玄 Blog - [MSSQL]檢查DB Lock相關狀態(dm_tran_locks x dm_os_waiting_tasks x dm_exec_connections)
Kenny’s Blog - RDBMS - 錯誤讀取現象及交易隔離層級原理介紹與舉例
小朱® 的技術隨手寫 - [SQL Server] 鎖定使用的藝術 (Part 2) - 隔離層次 (Isolation Level)
DotNet菜园 cnblogs - SQL SERVER的锁机制（一）——概述（锁的种类与范围）

轉載請註明來源，若有任何錯誤或表達不清楚的地方，歡迎在下方評論區留言，也可以來信至 leozheng0621@gmail.com
如果文章對您有幫助，歡迎斗內(donate)，請我喝杯咖啡

斗內💰

©2022-2022 LeoZheng

Built with Hexo and 3-hexo theme

×

歡迎斗內

github