這是基礎的最後一篇了，要來講鎖
任何 RDBMS 都會有鎖，這是為了並行使用同時避免彼此更新發生衝突，進而造成資料完整性問題。

鎖定層級

合理作法是盡可能讓必定發生的鎖只在最低的層級發生，例如可以 page 鎖就不要 table 鎖、可以 row 鎖就不要 page 鎖。

但是，取得鎖，這個動作，本身也會消耗資源，如果硬要用 row 鎖，那有可能會發生一個操作要數百萬的鎖，這樣會沒效率，在這種狀況，會考慮使用更高層級的鎖。

鎖有以下幾種層級

為了避免前面說的那種取一百萬鎖的狀況，SQL Server 再鎖定資料表內的某資源的時候，會在那個資源的上一層取得一個叫做意圖鎖定。

例如 : 如果 SQL Server 需要鎖定一個 RID 或 KEY，他也會在包含該資料列的 PAGE 上取得意圖鎖定，如果今天 Lock Manager 判斷再 page 鎖的話會更有效率，那他就會把鎖定層級往上到 page 層。

上述說的升級鎖，要注意 row 鎖不會升級成 page 鎖；會直接升級成 table 鎖。
如果 table 有進行分割，那 SQL Server 可以鎖定分割區。

SQL Server 升級鎖的門檻

1. 某個操作在一張 table 上需要超過 5000 個鎖定；
2. instance 用的鎖數量超過記憶體門檻。

但是我可以用 LOCK_ESCALATION 選項，針對特定 TABLE 去變更這個門檻

線上維護鎖定選項

接下來要示範操作，重建一個非叢集索引，然後設定 LOCK_ESCALATION，並且同時指定如果有任何操作封鎖索引重建超過一分鐘，就終止這個封鎖。

--Create the database

CREATE DATABASE [Lock]
ON PRIMARY
( 
    NAME = N'Lock', 
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL17.PRIMARYREPLICA\MSSQL\DATA\Lock.mdf' 
),
FILEGROUP MemOpt CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( 
    NAME = N'MemOpt', 
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL17.PRIMARYREPLICA\MSSQL\DATA\MemOpt' 
)
LOG ON
( 
    NAME = N'Lock_log', 
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL17.PRIMARYREPLICA\MSSQL\DATA\Lock_log.ldf' 
);
GO

USE [Lock];
GO

--Create and populate numbers table

DECLARE @Numbers TABLE
(
    [數字] INT
);

;WITH CTE([數字])
AS
(
    SELECT 1 AS [數字]
    UNION ALL
    SELECT [數字] + 1
    FROM CTE
    WHERE [數字] < 100
)
INSERT INTO @Numbers
SELECT [數字] FROM CTE;

--Create and populate name pieces

DECLARE @Names TABLE
(
    [名字] VARCHAR(30),
    [姓氏] VARCHAR(30)
);

INSERT INTO @Names
VALUES
    ('Peter', 'Carter'),
    ('Michael', 'Smith'),
    ('Danielle', 'Mead'),
    ('Reuben', 'Roberts'),
    ('Iris', 'Jones'),
    ('Sylvia', 'Davies'),
    ('Finola', 'Wright'),
    ('Edward', 'James'),
    ('Marie', 'Andrews'),
    ('Jennifer', 'Abraham');

--Create and populate Addresses table

CREATE TABLE dbo.[地址]
(
    [地址編號]     INT NOT NULL IDENTITY PRIMARY KEY,
    [地址列1]      NVARCHAR(50),
    [地址列2]      NVARCHAR(50),
    [地址列3]      NVARCHAR(50),
    [郵遞區號]     NCHAR(8)
);

INSERT INTO dbo.[地址]
VALUES
    ('1 Carter Drive', 'Hedge End', 'Southampton', 'SO32 6GH'),
    ('10 Apress Way', NULL, 'London', 'WC10 2FG'),
    ('12 SQL Street', 'Botley', 'Southampton', 'SO32 8RT'),
    ('19 Springer Way', NULL, 'London', 'EC1 5GG');

--Create and populate Customers table

CREATE TABLE dbo.[客戶]
(
    [客戶編號]         INT NOT NULL IDENTITY PRIMARY KEY,
    [名字]             VARCHAR(30) NOT NULL,
    [姓氏]             VARCHAR(30) NOT NULL,
    [帳單地址編號]     INT NOT NULL,
    [送貨地址編號]     INT NOT NULL,
    [信用額度]         MONEY NOT NULL,
    [餘額]             MONEY NOT NULL
);

SELECT * INTO #Customers
FROM
(
    SELECT
        (SELECT TOP 1 [名字] FROM @Names ORDER BY NEWID()) AS [名字],
        (SELECT TOP 1 [姓氏] FROM @Names ORDER BY NEWID()) AS [姓氏],
        (SELECT TOP 1 [數字] FROM @Numbers ORDER BY NEWID()) AS [帳單地址編號],
        (SELECT TOP 1 [數字] FROM @Numbers ORDER BY NEWID()) AS [送貨地址編號],
        (
            SELECT TOP 1 CAST(RAND() * [數字] AS INT) * 10000
            FROM @Numbers
            ORDER BY NEWID()
        ) AS [信用額度],
        (
            SELECT TOP 1 CAST(RAND() * [數字] AS INT) * 9000
            FROM @Numbers
            ORDER BY NEWID()
        ) AS [餘額]
    FROM @Numbers a
    CROSS JOIN @Numbers b
) a;

INSERT INTO dbo.[客戶]
SELECT * FROM #Customers;
GO

--This table will be used later in the chapter

CREATE TABLE dbo.[記憶體客戶]
(
    [客戶編號] INT NOT NULL IDENTITY
        PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 20000),

    [名字]             VARCHAR(30) NOT NULL,
    [姓氏]             VARCHAR(30) NOT NULL,
    [帳單地址編號]     INT NOT NULL,
    [送貨地址編號]     INT NOT NULL,
    [信用額度]         MONEY NOT NULL,
    [餘額]             MONEY NOT NULL
) 
WITH(MEMORY_OPTIMIZED = ON);
GO

INSERT INTO dbo.[記憶體客戶]
(
    [名字],
    [姓氏],
    [帳單地址編號],
    [送貨地址編號],
    [信用額度],
    [餘額]
)
SELECT
    [名字],
    [姓氏],
    [帳單地址編號],
    [送貨地址編號],
    [信用額度],
    [餘額]
FROM dbo.[客戶];
GO

CREATE INDEX idx_姓氏 ON dbo.[客戶]([姓氏]);
GO

--Set LOCK_ESCALATION to AUTO

ALTER TABLE dbo.[客戶] SET (LOCK_ESCALATION = AUTO);
GO
-- 如果資料表有分割，SQL Server 可以把鎖升級到「分割區層級」，不一定直接升級到整張表。
--Set WAIT_AT_LOW_PRIORITY

ALTER INDEX idx_姓氏 ON dbo.[客戶] REBUILD
WITH
(
    ONLINE = ON 
    (
        WAIT_AT_LOW_PRIORITY 
        (
            MAX_DURATION = 1 MINUTES,
            ABORT_AFTER_WAIT = BLOCKERS
--線上重建索引時，盡量低優先權等待，不要馬上去擋別人。
--但如果等超過 1 分鐘還是被別的交易擋住，就把那些 blocker 殺掉，讓索引重建繼續。
        )
    )
);
GO

鎖定類型

當一個交易已經在某個資源上拿到某種鎖的時候，另一個交易還能不能在同一個資源上拿另一種鎖?

這時候就會牽涉到各種鎖的態樣

最常見最大宗的鎖有三種，共享、排他、更新鎖。

共享鎖 S:
只看資料，不改資料
所以這種鎖不太會有什麼影響，可以跟很多人一起看資料一起同時持有這種鎖。

排他鎖 X:
要改這筆資料，其他人不要讀，不要改
這會用在例如 update、delete、insesrt 上
只要有這個鎖存在，其他人都不准來拿鎖

更新鎖 U:
這個比較特別，這意思是我現在需要更新這筆資料，但還沒開始更新，先卡位，避免等等升級成排他鎖而發生死結。

Sch-S 鎖 :
這個在統計資訊更新的時候有說過，這是結構鎖，我當時說的是，為了穩定讀取與使用物件結構，所以拿來鎖結構確保不會有人來修改物件結構。

但後面沒有細說
這不是哪來鎖一般資料的 Insert、Update、Delete 的
這是用來鎖統計資訊、鎖引、欄位定義、資料表結構、metadata等等會用到的東西的。
中文可以理解成 : 結構描述穩定鎖，所以她最重要的是為了穩定。

例如 :

SELECT * 
FROM TABLENAME 

很多人會以為，SELECT 只拿 S 鎖，但實際上SQL Server 在查詢的時候，也會需要確認這張表的 metadata 是穩定的，所以其實還會多拿一個 Sch-S 鎖。

按照我前面的說法可以猜想到，這個鎖不是拿來擋 DML 的所以持有這個鎖的時候，還是可以進行 DML 的操作。

他只會去擋另外一個等等會說明的 Sch-M 鎖，也就是 DDL 指令。

所以就可以理解到，有一個 SELECT 的交易還沒有 COMMIT，但是卻不能做 ALTER TABLE 的原因，就是因為那個 SELECT 現在還持有 Sch-S 鎖。

Sch-M 鎖 :
這跟 Sch-S 很像，中文叫做結構描述修改鎖。
這個鎖很強，不只會擋 DDL 也會擋 DML。
這是當有一個交易正在做 DML 的時候，就會持有這個鎖，擋住其他想要查詢想要修改的人。
這最常發生在更新統計資訊、重建索引

更新統計資訊

一個查詢在正在執行的時候，會去讀取統計資訊以便置做出執行計畫，讀取統計資訊這個動作，就是實際上發出 Sch-S 鎖的時候，所以才會有 SELECT 也會拿 Sch-S 鎖的現象。

而更新統計資訊，因為這是改 metadata，要把新的 metadata 寫回去的時候拿 Sch-M 鎖。

這時候就會發生，背景在更新統計資訊，阿前面的 SELECT 不能查的狀況。
這狀況我在統計資訊那邊也有說過怎麼解

就是把統計資訊設定成非同步更新，但副作用是那一次的查尋會因為沒有更新統計資訊，而使用爛的執行計畫，好處就是不會被這個鎖擋住。

重建索引

重建索引的時候，當然一定鎖

不過也有一種 online 的重建索引方式，但儘管是這種方式也不代表全成完全不鎖

online 的方式是，會先短暫的拿 Sch-M，然後開始建置此時允許 DML，最後切換再一次短暫 Sch-M。

那這會有問題，如果再建置的時候進行一個很長時間的 DML，就會導致最後要切換的時候重建索引一直拿不到 Sch-M 鎖，線上重建索引就會很久，是卡在這。

這也是我在上一個線上維護鎖定選項中，設定一個

WAIT_AT_LOW_PRIORITY
(
MAX_DURATION = 1 MINUTES,
ABORT_AFTER_WAIT = BLOCKERS
)

的用意。

Online index rebuild 需要 Sch-M 的時候，如果被其他交易擋住，就先低優先權等。
等超過 1 分鐘，就殺掉 blocker。

一般鎖

意圖鎖

鎖定相容性

死鎖

如果兩個不同的程序已經在不同的資源上取的鎖，但兩邊都被封鎖，並且都在等待對方完成，這種情況就叫做死鎖。

一般來說遇到封鎖，絕大多數狀況都可以自動完成只是要等，但是死鎖不行，一定要親自介入，並且通常只有一邊的查詢可以完成。

SQL Server 有一個東西去監控死鎖，deadlock monitor，這東西遇到死鎖時，會檢查這些程序是否已經被指派交易優先權。

如果這些程序有不同的交易優先權，SQL Server 會終止優先權最低的程序。

如果它們的優先權相同，SQL Server 會根據資源使用量，終止成本最低的程序。

如果兩個程序的成本也相同，SQL Server 會隨機挑選其中一個程序並終止它。

降低死鎖風險

遵循以下準則

• 在適當的情況下使用樂觀式隔離層級；同時也應考量相關取捨，例如 TempDB 使用量、磁碟額外負擔等。
• 交易內不應該包含使用者互動；這可以避免鎖定被持有過長時間。意思是交易開始後，不要停下來等人按按鈕互動、輸入資料、確認畫面、選選項等等
• 交易應該盡可能短，並且盡量放在同一個批次中；這可以避免長時間執行的交易，因為長交易會讓鎖定被持有超過必要時間。
• 所有可程式化物件都應該以相同順序存取物件；這可以降低死結發生的可能性，但代價可能是第一張資料表上的競爭增加。

理解交易

任何會造成資料或物件被修改的動作，都是在交易的脈絡中發生的。

SQL Server 支援三種交易

• 自動認可交易
  • 這是預設的
• 明確交易
  • 這是每一次交易都手動去下 BEGIN TRANSACTION、COMMIT
• 隱含交易
  • 這是自動下 BEGIN TRANSACTION 但是要手動下 COMMIT

交易最大的重點是 ACID

原子性 A

這意思是交易不可能只提交一部份，要馬全部成功，要馬全部失敗

但是 SQL Server 在這個部分有提供一個 save point，標記點，當發生 roll back的時候，save point 之前的內容會被提交，而 save point 之後的可以繼續提交或回覆。

這作用是，如果說今天有大量新增 table，但是中途有一個新增失敗，但是我不想要全部失敗，所以有一個 Save point 可以用，避免因為這個失敗導致其他已經大量做好的東西要全部重做。

下面範例就是我再 customers 做大量 insert，中間插入一個 addresses 的 insert 然後故意讓他失敗。

SELECT COUNT(*) InitialCustomerCount 
FROM dbo.Customers;

SELECT COUNT(*) InitialAddressesCount 
FROM dbo.Addresses;

BEGIN TRANSACTION;

DECLARE @Numbers TABLE
(
    Number INT
);

;WITH CTE(Number)
AS
(
    SELECT 1 Number
    UNION ALL
    SELECT Number + 1
    FROM CTE
    WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number 
FROM CTE;

--Create and populate name pieces

DECLARE @Names TABLE
(
    FirstName VARCHAR(30),
    LastName  VARCHAR(30)
);

INSERT INTO @Names
VALUES
    ('Peter', 'Carter'),
    ('Michael', 'Smith'),
    ('Danielle', 'Mead'),
    ('Reuben', 'Roberts'),
    ('Iris', 'Jones'),
    ('Sylvia', 'Davies'),
    ('Finola', 'Wright'),
    ('Edward', 'James'),
    ('Marie', 'Andrews'),
    ('Jennifer', 'Abraham');

--Populate Customers table

SELECT * INTO #Customers
FROM
(
    SELECT
        (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName,
        (SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName,
        (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) BillingAddressID,
        (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) DeliveryAddressID,
        (
            SELECT TOP 1 CAST(RAND() * Number AS INT) * 10000
            FROM @Numbers
            ORDER BY NEWID()
        ) CreditLimit,
        (
            SELECT TOP 1 CAST(RAND() * Number AS INT) * 9000
            FROM @Numbers
            ORDER BY NEWID()
        ) Balance
    FROM @Numbers a
    CROSS JOIN @Numbers b
) a;

INSERT INTO dbo.Customers
SELECT * 
FROM #Customers;

SAVE TRANSACTION CustomerInsert;

BEGIN TRY

    --Populate Addresses table - Will fail, due to length of Post Code

    INSERT INTO dbo.Addresses
    VALUES
    (
        '1 Apress Towers',
        'Hedge End',
        'Southampton',
        'SA206 2BQ'
    );

END TRY
BEGIN CATCH

    ROLLBACK TRANSACTION CustomerInsert;

END CATCH;

COMMIT TRANSACTION;

SELECT COUNT(*) FinalCustomerCount 
FROM dbo.Customers;

SELECT COUNT(*) FinalAddressesCount 
FROM dbo.Addresses;

一致性 C

交易的時候，必須把資料庫從一個一致的狀態，在交易完成的時候，仍然保持一致。

所有交易的資料都必須符合所有資料規則、條件約束、資料型別等等。

在條件約束的方面上，有變通方式，CHECK 可以用 NOCHECK 去暫停，等到暫停結束在把她調整回來，但是這樣做會讓 SQL Server 把條件約束標記為不信任，然後查詢優化器就會去忽略這個條件約束，一直到用下列與法去命令驗證資料。

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL;

這裡的紅字是一個效能調教的方向。之後會說

隔離性 S

並行交易是否能夠看到另一個交易在提交之前所做的資料修改。

隔離交易可以避免交易異常現象，SQL Server 透過兩種方式來控制隔離性

• 其中之一就是這裡說的鎖
• 另外是維護資料列的多個版本

每個交易都會以某個已經定義的隔離層級執行

隔離層級是一個很重要的項目，他影響甚廣，但是在說明隔離層級之前，要先說明交易可能會發生的異常現象。

交易異常現象

以下三種現象我們稱為異常

• Dirty reads
• 不可重複讀
• 幻讀

Dirty Reads

當一個交易讀取到，實際上從未存在於資料庫中的資料時，就稱為 Dirty Reads

這個在 checkpoint 那一個地方有說過類似的東西。
在 checkpoint 之前，資料尚未被寫入實體檔案，仍然存在於記憶體中。
可是別人卻查的到，因為他是存在記憶體，這個可以視作一種 Dirty Pages。

但注意這是 Dirty Pages 不是 Dirty Reads。

另一種狀況是交易A 正在執行 INSERT，交易 B 就去查詢這筆 INSERT，結果最後交易 A ROLL BACK，那交易 B 查到的這筆 INSERT 就是 Dirty Reads。

這種狀況預設下
SQL SERVER 會發給 交易A 排他鎖
而交易B 取得的是共享鎖，所以交易 B 會被 A的排他鎖擋住

不可重複讀

當一個交易讀取同一筆資料列兩次，但每次得到不同結果時，就會發生不可重複讀。

這種狀況預設如下

交易A 正在進行
取得 S 鎖 SELECT ID = 1 放掉 S 鎖
取得 S 鎖 SELECT ID = 2 放掉 S 鎖
取得 S 鎖 SELECT ID = 3 放掉 S 鎖 ….

此時 交易 B 去 UPDATE ID = 1，而且 COMMIT，因為 S鎖已經放掉了所以預設會讓他改

交易 A 尚未結束，他在最後一個 SELECT 還需要再去 SELECT ID = 1

這時候就會發生，在同一個交易裡，第一次讀 ID = 1 資料跟第二次 ID = 1 的資料不一樣

雖然這個案例在邏輯上是異常，但是在預設的 SQL Server 狀態下，會判定這是正常查詢。

幻讀

這跟不可重複讀很像，只不過不可重複讀是針對 row 的值，而幻讀是在說 row 的數量。

隔離等級

但是有些時候，上面說的那三種異常，對我業務邏輯來說不一定就是異常。

所以 sql server 提供幾種隔離等級，去處理這些問題。

一共有四種悲觀、兩種樂觀等級。

悲觀隔離等級會使用鎖定來防止交易異常現象

樂觀隔離等級會使用資料列版本控制來做

悲觀隔離等級

Read Uncommitted

這是限制最少的等級

它的運作方式是：寫入操作會取得鎖定，但讀取操作不會取得任何鎖定。

這表示在這個隔離層級下，讀取操作不會封鎖其他讀取者或寫入者。結果是，前面各節所描述的所有交易異常現象都有可能發生。

Read Committed

這是前面一直說的預設等級

它的運作方式是：讀取操作會取得共享鎖定，寫入操作也會取得鎖定。
一旦該資料已經被讀取，鎖釋放。

可以防止 dirty read，但是不可重複、幻讀仍然會發生。

Repeatable Read

這是 Read Committed 的延伸，接觸到的所有資料列取得取得共享鎖，但是跟 Read Committed 不同的是，這些鎖會持有到交易結束為止。

所以這種等級，dirty read 跟不可重複讀都不可能發生，但是幻讀還是有可能會發生。

還有由於持有鎖的時間變得更長，鎖以死鎖會更有可能發生。

Serializable

這是最嚴格的隔離等級，也是最容易發生死鎖的等級。

它的運作方式不只是為寫入操作取得鎖定，也會為讀取操作取得鍵範圍鎖定，並且將這些鎖定持有到交易結束。

因為鍵範圍鎖定會以這種方式被持有，所以所有交易異常現象都不可能發生，包括幻讀。

樂觀隔離等級

這個跟悲觀隔離等級不一樣，悲觀隔離等級他可以在單一交易中去設定，但是樂觀隔離等級一定要在資料庫層級上做設定，並且 RCSI 會直接預設取代 Read Committed。

Snapshot

這裡的快照，跟前面擴展覆寫說的快照是不一樣的東西，這裡是只交易的快照。

一個交易開始之後，這個交易裡面的所有 SELECT，都會看到交易開始那一刻，已經提交的資料版本。

跟資料庫快照很像，他也是版本紀錄，當有人去更動資料時，會去紀錄舊版本的資料，而不是整份複製。

差別是，交易快照只存在這次交易。

如果是用這個快照隔離，那就不會發生悲觀的髒讀、不可重複讀、幻讀，而且在讀的時候也不會有鎖的問題。

沒有鎖死結機率就下降。

看似都很完美但這是有代價得

1. 因為是快照，所以一定要有地方存， 2019 以前會存在 tempdb，之後會存在另一個db裡

更準確來說，如果2019以後的有啟用 ADR，那他確實會存在使用者資料庫裏面的PVS，但沒啟用的話那一樣還是存在 tempDB。

2. 如果是這樣的話，長交易就很危險因為沒辦法把快照清掉。
3. 如果去變更同一個 row，在悲觀裡面頂多就是等，但在樂觀裡面會報錯

Read Committed Snapshot Isolation ( RCSI )

讀取這個 SELECT statement 開始時，最後一個已提交版本。在 READ COMMITTED 隔離語意下，用 row versioning 取代讀取時的 S 鎖等待。

原本 Read Committed :
SELECT 用 S 鎖，遇到 UPDATE 的 X 鎖會等。

RCSI :
SELECT 遇到 X 鎖時，不等，直接去讀 UPDATE 之前的版本。

這跟快照隔離最大差異是，快照隔離的範圍是一個交易，RCSI 的範圍是一個語法。

理解隔離等級

上面都只是理論，用實際案例比較好理解
首先，隔離等級是在決定，當交易 A 正在處理資料時，交易 B 到底可以看到什麼、要不要等、看哪一個版本。

可以拆解成兩派

• 悲觀式 : 我怕你改道我正在看的東西，所以先用鎖擋住妳
• 樂觀式 : 妳要改就改，我不搶鎖，我去讀舊的版本

我們現在先設定一個場景
假設資料現在是 :
ID = 1
Balance = 1000

此時有一個交易 A

BEGIN TRAIN

UPDATE Account
SET Balance = 500
WHERE ID = 1;

-- 還沒 COMMIT

現在 A 已經把 1000 改成 500，但還沒提交。
此時交易B

SELECT Balance
FROM Account
WHERE ID = 1

不同隔離等級的差異就是 :
B 現在是要看到 500? 還是等 A? 還是直接看到以前的 1000?

1. Read Uncommitted
   最鬆。
   B 不管 A 有沒有提交，都直接看到 500。
   但因為 A 還沒 COMMIT，所以他是有可能 ROLLBACK 的，那B就會看到一個其實從來沒有正式存在過的資料。
   而這個就叫做髒讀。

2. Read Committed
   這是SQL Server 預設。

這個規則是：
只能讀到已經提交的資料。

剛才 A 還有 COMMIT，所以因為規則，此時 B 只能等 A COMMIT，之後才看到 500。
這種狀況下髒讀不會發生。
但是會有下面這種狀況發生

--狀況是 交易 A 已經 COMMIT

--交易 B 開始交易
BEGIN TRAN 
SELECT Balance 
FROM Account
WHERE ID = 1 

--這時候會看到 500
--但是交易 B 還沒結束他還在做別的事情

--這時候交易 C 又同時開始啟動交易
BEGIN TRAN 
UPDATE Account
SET Balance = 900
WHERE ID = 1

--交易 C 還沒 COMMIT

--然後交易 B 再最後，出於業務需要他再一次的
SELECT Balance 
FROM Account 
WHERE ID = 1

--因為交易 C 還沒 COMMIT，這時候交易 B 會被鎖擋住

--接下來交易 C COMMIT 了
-- 那這時候交易 B 會看到 Balance = 900

變成對交易 B 來說，他從頭到尾都在同一個交易裡面，
可是第一次找是 500、
第二次找卻變成 900 了
而這就叫做不可重複讀。

發生的原因就是，在交易 B 第一次 SELECT 拿到 S 鎖之後，他馬上就放掉了，所以交易 C 才可以去改資料。

3. Repeatable Read
   這個就是在上面那個規則 Read Committed 上再加一條：
   我讀過的 Row，你在我交易結束之前不能改。

這個層級會直接解決剛剛交易 B 一拿到 S 鎖之後馬上放掉的問題，他會繼續持有到整個交易 B 結束。
因此他不會發生髒讀、不可重複讀。

但如果在改一下
現在改成交易 B 是看

BEGIN TRAN
SELECT Balance 
FROM Account 
WHERE ID > 1

假設結果是看到有兩筆
ID = 1
ID = 2

因為 Repeatable Read 有一個重點是，讀過的 ROW。
但他沒說那沒讀過的呢?

在交易 B 結束之前，又有一個交易 D 去寫

INSERT INTO Account .......

然後交易 B 因為還沒結束，然後也是因為業務需要，在COMMIT 又查一次 ID > 1
就會發現比第一次讀的時候多了一行資料。
這就是幻讀。

4. Serializable
   你可以把它理解成：
   Repeatable Read + 把「查詢範圍」也鎖住。

所以延續上面的例子

BEGIN TRAN
SELECT Balance 
FROM Account 
WHERE ID > 1

如果是這個等級，那就會把 ID > 1 都鎖住，交易 D 要 INSERT 那就只能等交易 B COMMIT。
那這個層級，就不會發生髒讀、不可重複讀、幻讀。

但是當然，這代價最大，因為他鎖最多，那死鎖的風險也就最高。

以上四個是悲觀隔離
接下來是樂觀隔離

在樂觀隔離中，想法就換了，他可以接受，去讀之前的版本。

1. Snapshot
   回到 Read Uncommitted 的案例中
   交易 A 去 UPDATE Balance = 500
   交易 B 去查，在那邊說會是 500，但這裡不一樣
   當在這個隔離等級的狀況下
   交易 B 啟動的瞬間，他會去快照現在的資料樣貌
   所以只要接下來交易 B 都還沒有結束，他就會一直用這個瞬間的資料來處理。
   那這樣的話就不管那些什麼髒讀幻讀什麼讀了，因為之後資料不管怎樣變動，只要交易 B 還沒有結束，他都是看當下快照的結果，後續資料怎麼改都跟交易 B 無關。

2. RCSI
   這跟 Read Committed 很像，也結合 Snapshot 的概念
   交易 A UPDATE Balance = 500 還沒 commit
   交易 B 去查會得到還沒 COMMIT 前的資料就是 1000
   交易 A COMMIT，換交易 C UPDATE Balance = 900 還沒 COMMIT
   交易 B 在接著查，會查到交易 A 提交的 500。
   可以看出他跟 Snapshot 的差異是，Snapshot 的快照是綁在整個交易，而 RCSI 則是只限定在一個陳述式裡面。

最後，樂觀隔離是有代價的，因為要快照，就一定要有硬碟空間，有硬碟空間又會有I/O問題，然後就會有記憶體、CPU的資源要消耗。
交易的時間越長，裡面的快照就會越亂越肥，因為妳想用這種隔離方式就代表你會有同時交易的問題，同時交易每一個都要快照一次，如果時間又很長，快照就要存很久，這對硬體來說會有壓力。
然後如果在快照裡面兩個交易同時去 UPDATE 同一筆，例如說
交易A 去 UPDATE ID = 1 的
交易B 也去 UPDATE ID = 1的
悲觀式的只會鎖定，就等
樂觀式的會直接噴錯。

RCSI 的話會等，不會噴錯。

對於樂觀式的快照方式我寫的很籠統，再更細一點是他只會快照 row 的版本，可是這樣就太多了，有些東西要寫很細是真的可以寫很多，可是一些比較沒有實際用途的就不想寫出來增加負擔

持久性 D

交易一旦 COMMIT 成功，就算 SQL Server 當機、重開機、斷電等等，這筆交易也不能消失，稱為持久性。

正常交易流程

1. 使用 commit 指令
2. sql server 把交易紀錄寫到 log 快取記憶體
3. sql server 把 log 快取 flush 到硬碟的 .ldf 上
4. flush 完成
5. COMMIT 才成功

其中第三點，叫做 write-ahead logging（WAL，預寫式記錄） 。

預寫的意思是相對實際資料來說，因為 COMMIT 的時候實際資料不一定已經寫回 MDF 了。

但是後來 SQL SERVER 有透過一種功能放關這個規則，叫做 delayed durability。

這功能運作方式是 : 延後 log 快取 flush 到磁碟，直到下列其中一件事情發生

• log cache 變滿，並自動 flush 到磁碟。
• 同一個資料庫中有一個完全持久性的交易提交。
• 對該資料庫執行 sp_flush_log 系統預存程序。

這好處是 commit 會變快、log flush 等待時間變少、大量的小交易吞吐量會提升
缺點就是，沒有辦法保證一致性，突發的當機會讓已經成功的交易消失。

觀察死結 / 交易

sys.dm_tran_active_transactions

這東西是微軟提供的 DMV

-- 用來觀察超過 10 分鐘的交易
SELECT
        name AS 交易名稱
        ,transaction_begin_time AS 交易開始時間
        ,CASE transaction_type
            WHEN 1 THEN N'讀取/寫入'
            WHEN 2 THEN N'唯讀'
            WHEN 3 THEN N'系統'
            WHEN 4 THEN N'分散式'
        END AS 交易類型
        ,CASE transaction_state
            WHEN 0 THEN N'初始化中'
            WHEN 1 THEN N'已初始化但尚未開始'
            WHEN 2 THEN N'作用中'
            WHEN 3 THEN N'已結束'
            WHEN 4 THEN N'提交中'
            WHEN 5 THEN N'已準備'
            WHEN 6 THEN N'已提交'
            WHEN 7 THEN N'回復中'
            WHEN 8 THEN N'已回復'
        END AS 交易狀態
        ,SUBSTRING
        (
            TXT.text,
            (er.statement_start_offset / 2) + 1,
            (
                (
                    CASE 
                        WHEN er.statement_end_offset = -1
                        THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
                        ELSE er.statement_end_offset
                    END - er.statement_start_offset
                ) / 2
            ) + 1
        ) AS 目前執行語句
        ,TXT.text AS 完整批次語句
        ,es.host_name AS 主機名稱
        ,CASE tat.transaction_type
            WHEN 1 THEN N'讀取/寫入交易'
            WHEN 2 THEN N'唯讀交易'
            WHEN 3 THEN N'系統交易'
            WHEN 4 THEN N'分散式交易'
            ELSE N'未知'
        END AS 交易類型說明
        ,SUSER_SNAME(es.security_id) AS 執行交易的登入帳號
        ,es.memory_usage * 8 AS 記憶體使用量KB
        ,es.reads AS 讀取次數
        ,es.writes AS 寫入次數
        ,es.cpu_time AS CPU時間
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions st
        ON tat.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions es
        ON st.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
        ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
WHERE st.is_user_transaction = 1
        AND tat.transaction_begin_time < DATEADD(MINUTE, -10, GETDATE());

sys.dm_tran_locks

這個 DVM 用來觀察目前鎖定的資訊

通常會搭配

sys.dm_os_waiting_tasks

用來看目前等待資源的工作

SELECT
        DB_NAME(tl.resource_database_id) AS 資料庫名稱
        ,tl.resource_type AS 資源類型
        ,tl.resource_subtype AS 資源子類型
        ,tl.resource_description AS 鎖定資源描述
        ,tl.request_mode AS 要求鎖定模式
        ,tl.request_status AS 要求狀態
        ,os.session_id AS 被封鎖Session
        ,os.blocking_session_id AS 封鎖來源Session
        ,os.resource_description AS 等待資源描述
        ,OBJECT_NAME(
                    CAST(
                        SUBSTRING(os.resource_description,
                                  CHARINDEX('objid=', os.resource_description, 0) + 6, 9)
                        AS INT)
                    ) AS 被鎖定資料表
FROM sys.dm_os_waiting_tasks os
INNER JOIN sys.dm_tran_locks tl
        ON os.session_id = tl.request_session_id
WHERE tl.request_owner_type IN ('TRANSACTION', 'SESSION', 'CURSOR');

TF 1204、1222

這兩個東西會擷取死鎖的資訊，然後寫成 ERROR LOG，不過已經漸漸淘汰不需要使用了

System health session

可以透過這個東西去查看死結，並且回溯查詢死結的詳細資訊。