第十一單元 事務與鎖

create database step2_unit13;
go
use step2_unit13;
go
-- 創建數據表
CREATE TABLE account (
id INT PRIMARY KEY identity,
NAME VARCHAR(10),
balance decimal(10,2)
);
-- 添加數據
INSERT INTO account (NAME, balance) VALUES ('張三', 1000), ('李四', 1000);

1. 應用場景說明

什麼是事務： 在實際的開發過程中，一個業務操作如：轉賬，往往是要多次訪問資料庫才能完成的。轉

賬是一個用戶扣錢，另一個用戶加錢。如果其中有一條 SQL 語句出現異常，這條 SQL 就可能執行失敗。

事務執行是一個整體，所有的 SQL 語句都必須執行成功。如果其中有 1 條 SQL 語句出現異常，則所有的

SQL 語句都要回滾，整個業務執行失敗。

模擬張三給李四轉 500 元錢，一個轉賬的業務操作最少要執行下麵的 2 條語句：

張三賬號-500

李四賬號+500

-- 張三賬號-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號+500
update account set balance = balance + 500 where name='李四';

假設當張三賬號上-500 元,伺服器崩潰了。李四的賬號並沒有+500 元，數據就出現問題了。我們需要保證其中

一條 SQL 語句出現問題，整個轉賬就算失敗。只有兩條 SQL 都成功了轉賬才算成功。這個時候就需要用到事務。

案例演示 1：事務提交

模擬張三給李四轉 500 元錢（成功）

begin transaction -- tran,開啟事務
-- 張三賬號-500
update account set balance = balance - 500 where name='張三';
-- 李四賬號+500
update account set balance = balance + 500 where name='李四';
commit; -- 提交

案例演示 2：事務回滾

模擬張三給李四轉 500 元錢（失敗）

begin transaction -- tran,開啟事務
begin try
    -- 張三賬號-500
    update account set balance = balance - 500 where name='張三';
    -- 李四賬號+500
    update account set balance = balance + 500 where name='李四';
    commit; -- 提交
end try
begin catch
    rollback;-- 回滾
end catch

總結: 如果事務中 SQL 語句沒有問題，commit 提交事務，會對資料庫數據的數據進行改變。 如果事務中 SQL 語句有問題，rollback 回滾事務，會回退到開啟事務時的狀態。

2. 事務原理

事務開啟之後, 所有的操作都會臨時保存到事務日誌中, 事務日誌只有在得到 commit 命令才會同步到數據表

中，其他任何情況都會清空事務日誌(rollback，斷開連接)

事務的執行步驟

1. 客戶端連接資料庫伺服器，創建連接時創建此用戶臨時日誌文件

2. 開啟事務以後，所有的操作都會先寫入到臨時日誌文件中

3. 所有的查詢操作從表中查詢，但會經過日誌文件加工後才返回

4. 如果事務提交則將日誌文件中的數據寫到表中，否則清空日誌文件。

3. 四大特性

原子性,一致性,隔離性,持久性（ACID）

1. Atomic（原子性）：事務中包含的操作被看做一個邏輯單元，這個邏輯單元中的操作要麼全部成 功，要麼全部失敗，保證數據的完整性。

2. Consistency（一致性）：事務完成時，數據必須處於一致狀態，數據的完整性約束沒有被破壞，事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒 有執行過一樣。如：轉賬前2個人的總金額是2000，轉賬後2 個人總金額也是 2000。

3. Isolation（隔離性）：事務允許多個用戶對同一個數據進行併發訪問，而不破壞數據的正確性 和完整性。同時，並行事務的修改必須與其他並行事務的修改相互獨立。

4. Durability（持久性）：事務結束後，事務處理的結果必須能夠得到固化(永久存儲到資料庫中了)。就算斷電了，也是保存下來的。

    

1. 事務的隔離級別

事務在操作時的理想狀態： 所有的事務之間保持隔離，互不影響。因為併發操作，多個用戶同時訪問同一個

數據。可能引發併發訪問的問題：

資料庫有四種隔離級別

上面的級別最低，下麵的級別最高。“是”表示會出現這種問題，“否”表示不會出現這種問題。

隔離級別越高，性能越差，安全性越高。

2. 事務快照

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

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

undo.log

作用：

1 讀操作時不會陷入block 和死鎖的問題中，SNAPSHOT 本身提高了資料庫系統的事務處理的性能。

2 避免了 臟讀，非一致性讀，以及丟失更新，和不可重覆讀等多個問題

4. 隔離級別演示

查看當前的隔離級別

DBCC USEROPTIONS 

1. 臟讀的演示

設置隔離級別為 read uncommit;

-- set transaction isolation level <隔離級別>

先將兩個賬號的金額都恢覆成1000元。

update account set balance=1000;

1. 打開 A 視窗登錄 ，設置隔離級別為最低

   set transaction isolation level read uncommitted;

    

2. 打開 B 視窗,AB 視窗都開啟事務

   set transaction isolation level read uncommitted;
   begin transaction;

    

3. A 視窗更新 2 個人的賬戶數據，未提交

   -- 張三賬號-500
   update account set balance = balance - 500 where name='張三';
   -- 李四賬號+500
   update account set balance = balance + 500 where name='李四';

    

   B 視窗查詢賬戶

4. 

    

5. A 視窗回滾

   rollback;

    

6. B 視窗查詢賬戶，錢沒了

臟讀非常危險的，比如張三向李四購買商品，張三開啟事務，向李四賬號轉入 500 塊，然後打電話給李四說錢

已經轉了。李四一查詢錢到賬了，發貨給張三。張三收到貨後回滾事務，李四的再查看錢沒了。

解決臟讀的問題：將全局的隔離級別進行提升

將數據進行恢復：

UPDATE account SET balance = 1000;

1. 在 A 視窗設置全局的隔離級別為 read committed

   set tran isolation level read committed;

    

2. A 更新 2 個人的賬戶，未提交

   begin transaction;
   update account set balance=balance-500 where id=1;
   update account set balance=balance+500 where id=2;

    

3. B 視窗查詢賬戶 , 接著A 視窗再 commit 提交事務

   select * from account; -- sqlserver 2017 會一直等待account釋放，直到A視窗commit之後。

    

2. 不可重覆讀演示

設置隔離級別為read commited

set tran isolation level read committed;

將數據進行恢復：

UPDATE account SET balance = 1000;

1. B視窗開啟事務,查詢數據，先不提交

   begin tran;
   select * from account;

    

   

    

2. 在 A 視窗開啟事務，並更新數據

   begin tran;
   update account set balance=balance+500 where id=1;
   commit;

    

3. B 視窗查詢,發現與上一次讀取的結果不同。

   select * from account;
   commit;

    

   

    

解決不可重覆讀的問題：

將隔離級別進行提升為：repeatable read

將數據進行恢復：

UPDATE account SET balance = 1000;

1. A 視窗設置隔離級別為：repeatable read

   set tran isolation level repeatable read;

    

2. B視窗開啟事務,查詢數據，先不提交

   begin tran;
   select * from account;

    

3. 在 A 視窗開啟事務，並更新數據, 此時你會發現，事務會一直處於等待狀態，無法提交，直到B視窗中的事務提交完畢。

   begin tran;
   -- 事務會一直處於等待狀態，無法提交，直到B視窗中的事務提交完畢。
   update account set balance=balance+500 where id=1; 
   commit;

    

4. B 視窗提交事務

   commit; -- 此時A視窗中的事務也會緊跟著提交

    

3. 幻讀的演示

在SQL Server中，幻讀無法演示（一個事務中兩次讀取到的數據數量不一致），但是隨著事務隔離級別的提升，如果某個事務在update 數據之後未提交，則另外一個事務無法對當前資源進行操作，直到資源釋放。

4. 事務快照演示

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

ALTER DATABASE step2_unit13 SET ALLOW_SNAPSHOT_ISOLATION ON;

將數據進行恢復：

update account set balance=1000;

1. A視窗打開事務，並更新數據，不提交(隔離級別為預設的read committed)

   begin tran;
   update account set balance-=500 where id=1;
   update account set balance+=500 where id=2;
   select * from account

    

   

    

2. B視窗打開事務，讀取account,不提交

   -- 設置隔離級別
   set tran isolation level snapshot;
   begin tran;
   select * from account;

    

3. A 視窗 commit

   commit;
   select * from account

    

   

    

4. B視窗commit(提交之前再查詢一次，發現數據是一致的)

   select * from account
   commit;
   select * from account

    

   

    

5. 鎖

鎖是多用戶訪問同一資料庫資源時，對訪問的先後次序許可權管理的機制。

鎖的分類：

• 共用鎖

• 排他鎖

• 更新鎖

• 架構鎖

• 意向鎖

• 大容量更新鎖

   

悲觀鎖

總是假設最壞的情況，每次去拿數據的時候都認為別人會修改，所以每次在拿數據的時候都會上鎖，這樣別人想拿這個數據就會阻塞直到它拿到鎖（共用資源每次只給一個線程使用，其它線程阻塞，用完後再把資源轉讓給其它線程）。傳統的關係型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

樂觀鎖

總是假設最好的情況，每次去拿數據的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數據，可以使用版本號機制和CAS演算法實現。樂觀鎖適用於多讀的應用類型，這樣可以提高吞吐量，像資料庫提供的類似於write_condition機制，其實都是提供的樂觀鎖。

1. 行鎖

關鍵字：rowlock

select * from account rowlock where id=1

2. 表鎖

關鍵字：tableLockX

select * from account tableLockX where id=2

3. 共用鎖

共用鎖是用於讀取數據操作，允許多個事務讀取相同的數據，但不允許其他事務修改當前數據

-- 視窗1（添加共用鎖）
begin tran
select * from account with(holdlock) where id=1;
waitfor delay '00:00:10';
commit;

-- 視窗2（可讀）
begin tran
select * from account where id=1;
commit;

-- 視窗3（等待視窗1結束，才可執行）
begin tran
update account set balance=500 where id=1;
commit;
 

4. 排它鎖

又稱為獨占鎖，當事務對數據資源進行增刪改操作時，不允許其它任何事務操作這塊資源（可以讀取）。防止對同一資源進行多重操作。

-- 視窗1
begin tran;
update account set balance=500 where id=1; -- 更新鎖自動轉換為排它鎖
-- 等效於 update account with(updlock) set balance=500 where id=1
waitfor delay '00:00:10';
commit;

-- 視窗2(可讀)
begin tran;
select * from account
commit;

-- 視窗3（等待視窗1結束才會執行）
begin tran
update account set balance=1500 where id=1;
commit;
 

5. 更新丟失

第一類丟失更新 （通過設置隔離級別可以防止 Repeatable Read）

A事務撤銷時，把已經提交的B事務的更新數據覆蓋了。這種錯誤可能造成很嚴重的問題，通過下麵的賬戶取款轉賬就可以看出來：

A事務在撤銷時，“不小心”將B事務已經轉入賬戶的金額給抹去了。

第二類丟失更新 （需要應用程式控制，樂觀鎖）

A事務覆蓋B事務已經提交的數據，造成B事務所做操作丟失：

上面的例子里由於支票轉賬事務覆蓋了取款事務對存款餘額所做的更新，導致銀行最後損失了100元，相反如果轉賬事務先提交，那麼用戶賬戶將損失100元。

更新鎖

上述更新丟失問題目前無法重現，因為系統在執行update 語句時，會自帶更新鎖，在更新數據時，會將更新鎖自動轉換為排它鎖（別人只能讀）。

update account with(updlock) set balance=1000 where id=1

6. 死鎖

在多個任務中，每個任務鎖定了其他任務試圖鎖定的資源，會造成這些任務永久阻塞，從而出現死鎖，此時系統處於死鎖狀態。

-- 視窗1(發生死鎖)
begin tran;
    update account set balance=500 where id=1;
    waitfor delay '0:0:10'
    update account set balance=1500 where id=2;
commit;

-- 視窗2（執行成功）
begin tran;
    update account set balance=1500 where id=2;
    waitfor delay '0:0:8'
    update account set balance=500 where id=1;
commit;

如果先執行視窗2，視窗1會發生死鎖, 視窗1中事務所要的資源正好被視窗2中的事務鎖住了。

(1 行受影響) 消息 1205，級別 13，狀態 51，第 4 行 事務(進程 ID 51)與另一個進程被死鎖在 鎖 資源上，並且已被選作死鎖犧牲品。請重新運行該事務。

如何減少死鎖

1. 在所有事務中以相同的次序使用資源

2. 使事務儘可能簡短並且在一個批處理中

3. 避免在事務內和用戶進行交互，減少資源的鎖定時間

4. 為死鎖超時參數設置一個合理範圍

6. 事務與鎖的關係

事務與鎖是不同的。

1. 事務具有ACID（ 原子性、一致性、隔離性和持久性），鎖是用於解決隔離性的一種機制。

2. 事務的隔離級別通過鎖的機制來實現。

3. 另外鎖有不同的粒度，同時事務也是有不同的隔離級別的（一般有四種：讀未提交Read uncommitted， 讀已提交Read committed， 可重覆讀Repeatable read， 可串列化Serializable）。

在具體的程式設計中，開啟事務其實是要資料庫支持才行的，如果資料庫本身不支持事務，那麼仍然無法確保你在程式中使用的事務是有效的。 鎖可以分為樂觀鎖和悲觀鎖： 悲觀鎖：認為在修改資料庫數據的這段時間里存在著也想修改此數據的事務； 樂觀鎖：認為在短暫的時間里不會有事務來修改此資料庫的數據； 我們一般意義上講的鎖其實是指悲觀鎖，在數據處理過程中，將數據置於鎖定狀態（由資料庫實現）。 回到你的問題，如果開啟了事務，在事務沒提交之前，別人是無法修改該數據的；如果rollback，你在本次事務中的修改將撤消（不是別人修改的會沒有，因為別人此時無法修改）。當然，前提是你使用的資料庫支持事務。還有一個要註意的是，部分資料庫支持自定義SQL鎖覆蓋事務隔離級別預設的鎖機制，如果使用了自定義的鎖，那就另當別論。 重點：一般事務使用的是悲觀鎖（具有排他性）。

 配套視頻鏈接：【SQLServer 資料庫高級階段】.net 6 開發系列 ，全網最新，最全！(已完結)_嗶哩嗶哩_bilibili