圖文詳解SQL中的事務(wù)+隔離級別+阻塞+死鎖，看不懂找我！
點擊上方SQL數(shù)據(jù)庫開發(fā)，關(guān)注獲取SQL視頻教程
SQL專欄
來源 | 悟空聊架構(gòu)（ID：PassJava666）
本篇主要內(nèi)容如下：
一、事務(wù)
1.1 什么是事務(wù)
為單個工作單元而執(zhí)行的一系列操作。如查詢、修改數(shù)據(jù)、修改數(shù)據(jù)定義。
1.2 語法
「（1）顯示定義事務(wù)的開始、提交」
BEGIN`?`TRAN``INSERT`?`INTO`?`b(t1)?``VALUES``(1)``INSERT`?`INTO`?`b(t1)?``VALUES``(2)``COMMIT`?`TRAN
「（2）隱式定義」
如果不顯示定義事務(wù)的邊界，則SQL Server會默認把每個單獨的語句作為一個事務(wù)，即在執(zhí)行完每個語句之后就會自動提交事務(wù)。
1.3 事務(wù)的四個屬性ACID
「（1）原子性Atomicity」
1.事務(wù)必須是
原子工作單元。事務(wù)中進行的修改，要么全部執(zhí)行，要么全都不執(zhí)行；2.在事務(wù)完成之前（提交指令被記錄到事務(wù)日志之前），系統(tǒng)出現(xiàn)故障或重新啟動，SQL Server將會撤銷在事務(wù)中進行的所有修改；
3.事務(wù)在處理中遇到錯誤，SQL Server通常會
自動回滾事務(wù)；4.少數(shù)不太嚴重的錯誤不會引發(fā)事務(wù)的自動回滾，如主鍵沖突、鎖超時等；
5.可以使用錯誤處理來捕獲第4點提到的錯誤，并采取某種操作，如把錯誤記錄在日志中，再
回滾事務(wù)；6.SELECT @@TRANCOUNT可用在代碼的任何位置來判斷當前使用SELECT @@TRANCOUNT的地方是否位于一個打開的事務(wù)當中，如果不在任何打開的事務(wù)范圍內(nèi)，則該函數(shù)返回0；如果在某個打開的事務(wù)返回范圍內(nèi)，則返回一個大于0的值。打開一個事務(wù)，@@TRANCOUNT=@@TRANCOUNT+1；提交一個事務(wù)，@@TRANCOUNT-1。
「（2）一致性Consiitency」
1.同時發(fā)生的事務(wù)在修改和查詢數(shù)據(jù)時不發(fā)生沖突；
2.一致性取決于應用程序的需要。后面會講到一致性級別，以及如何對一致性進行控制。
「（3）隔離性Isolation」
1.用于控制數(shù)據(jù)訪問，確保事務(wù)只訪問處于期望的一致性級別下的數(shù)據(jù)；
2.使用鎖對各個事務(wù)之間正在修改和查詢的數(shù)據(jù)進行隔離。
「（4）持久性Durability」
1.「寫事務(wù)日志：」 在將數(shù)據(jù)修改寫入到磁盤上數(shù)據(jù)庫的數(shù)據(jù)分區(qū)之前會把這些修改寫入到磁盤上數(shù)據(jù)庫的事務(wù)日志中，把提交指令記錄到磁盤的事務(wù)日志中以后，即時數(shù)據(jù)修改還沒有應用到磁盤的數(shù)據(jù)分區(qū)，也可以認為事務(wù)是持久化的。
2.「系統(tǒng)重新啟動：」 正常啟動或在發(fā)生系統(tǒng)故障之后啟動，SQL Server會每個數(shù)據(jù)庫的事務(wù)日志，進行回復處理。
3.「恢復處理包含兩個階段：」 重做階段和撤銷階段。
4.「前滾：」 在重做階段，對于提交指令已經(jīng)寫入到日志的事務(wù)，但數(shù)據(jù)修改還沒有應用到數(shù)據(jù)分區(qū)的事務(wù)，數(shù)據(jù)庫引擎會重做這些食物所做的所有修改。
5.「回滾：」 在撤銷階段，對于提交指令沒有寫入到日志中的事務(wù)，數(shù)據(jù)庫引擎會撤銷這些事務(wù)所做的修改。（這句話需要research,可能是不正確的。因為提交指令沒有寫入到數(shù)據(jù)分區(qū)，撤銷修改是指撤銷哪些修改呢？）
二、鎖
2.1 事務(wù)中的鎖
（1）SQL Server使用鎖來實現(xiàn)事務(wù)的隔離。
（2）事務(wù)獲取鎖這種控制資源，用于保護數(shù)據(jù)資源，防止其他事務(wù)對數(shù)據(jù)進行沖突的或不兼容的訪問。
2.2 鎖模式
「（1）排他鎖」
a.當試圖修改數(shù)據(jù)時，事務(wù)只能為所依賴的數(shù)據(jù)資源請求排他鎖。
b.持有排他鎖時間：一旦某個事務(wù)得到了排他鎖，則這個事務(wù)將一直持有排他鎖直到事務(wù)完成。
c.排他鎖和其他任何類型的鎖在多事務(wù)中不能在同一階段作用于同一個資源。
如：當前事務(wù)獲得了某個資源的排他鎖，則其他事務(wù)不能獲得該資源的任何其他類型的鎖。其他事務(wù)獲得了某個資源的任何其他類型的鎖，則當前事務(wù)不能獲得該資源的排他鎖。
「（2）共享鎖」
a.當試圖讀取數(shù)據(jù)時，事務(wù)默認會為所依賴的數(shù)據(jù)資源請求共享鎖。 b.持有共享鎖時間：從事務(wù)得到共享鎖到讀操作完成。 c.多個事務(wù)可以在同一階段用共享鎖作用于同一數(shù)據(jù)資源。 d.在讀取數(shù)據(jù)時，可以對如何處理鎖定進行控制。后面隔離級別會講到如何對鎖定進行控制。
2.3 排他鎖和共享鎖的兼容性
（1）如果數(shù)據(jù)正在由一個事務(wù)進行修改，則其他事務(wù)既不能修改該數(shù)據(jù)，也不能讀取（至少默認不能）該數(shù)據(jù)，直到第一個事務(wù)完成。
（2）如果數(shù)據(jù)正在由一個事務(wù)讀取，則其他事務(wù)不能修改該數(shù)據(jù)（至少默認不能）。
2.4 可鎖定的資源的類型
RID、KEY（行）、PAGE（頁）、對象（例如表）、數(shù)據(jù)庫、EXTENT（區(qū)）、分配單元（ALLOCATION_UNIT）、堆（HEAP）、以及B樹（B-tree）。
「RID: 標識頁上的特定行」
格式: fileid: pagenumber: rid （1:109:0 ）
其中fileid標識包含頁的文件， pagenumber標識包含行的頁，rid標識頁上的特定行。
fileid與sys.databases_files 目錄視圖中的file_id列相匹配
例子：
在查詢視圖sys.dm_tran_locks的時候有一行的resource_description列顯示RID 是1:109:0 而status列顯示wait,
表示第1個數(shù)據(jù)文件上的第109頁上的第0行上的鎖資源。
2.5 鎖升級
SQL Server可以先獲得細粒度的鎖（例如行或頁），在某些情況下將細粒度鎖升級為更粗粒度的鎖（例如，表）。
例如單個語句獲得至少5000個鎖，就會觸發(fā)鎖升級，如果由于鎖沖突而導致無法升級鎖，則SQL Server每當獲取1250個新鎖時出發(fā)鎖升級。
三、阻塞
3.1 阻塞
當多個事務(wù)都需要對某一資源進行鎖定時，默認情況下會發(fā)生阻塞。被阻塞的請求會一直等待，直到原來的事務(wù)釋放相關(guān)的鎖。鎖定超時期限可以限制，這樣就可以限制被阻塞的請求在超時之前要等待的時間。
階段1：事務(wù)A請求資源S1，事務(wù)不對資源S1進行操作
階段2：事務(wù)A用鎖A鎖定資源S1，事務(wù)B請求對資源S1進行不兼容的鎖定（鎖B）,鎖B的請求被阻塞，事務(wù)B將進入等待狀態(tài)
階段3：事務(wù)A正在釋放鎖A，事務(wù)B等待鎖A釋放，
階段4：事務(wù)A的鎖A已釋放，事務(wù)B用鎖B鎖定資源S1
3.2 排除阻塞問題
例子：
3.2.1 準備工作：
1.準備測試數(shù)據(jù)
--先創(chuàng)建一張表Product作為測試。id為表的主鍵，price為product的價格
CREATE?TABLE?[dbo].[myProduct](
????[id]?[int]?NOT?NULL,
????[price]?[money]?NOT?NULL
)?ON?[PRIMARY]
GO
--插入一條數(shù)據(jù)，id=1,price=10
INSERT?INTO?[TSQLFundamentals2008].[dbo].[myProduct]([id],[price])VALUES(1,10)
2.模擬阻塞發(fā)生的情況
在SQL Server中打開三個查詢窗口Connection1、Connection2、Connection3，分別按順序執(zhí)行表格中的執(zhí)行語句。
--Connection1
BEGIN?TRAN
UPDATE?dbo.myProduct?SET?price?=?price?+?1?WHERE?id=1
「結(jié)論1：」
為了更新id=1這一行數(shù)據(jù)，會話必須先獲得一個排他鎖。事務(wù)處于一直打開狀態(tài)，沒有提交，所以事務(wù)一直持有排他鎖，直到事務(wù)提交并完成。
--Connection2
SELECT?*?FROM?dbo.myProduct?WHERE?id=1
「結(jié)論2：」
事務(wù)為了讀取數(shù)據(jù)，需要請求一個共享鎖，但是這一行已經(jīng)被其他會話持有的排他鎖鎖定，而且共享鎖和排他鎖不是兼容的，所以會話被阻塞，進入等待狀態(tài)
--Connection3
SELECT??request_session_id?AS?會話id?,
????????resource_type?AS?請求鎖定的資源類型?,
????????resource_description?AS?描述?,
????????request_mode?AS?模式?,
????????request_status?AS?狀態(tài)
FROM????sys.dm_tran_locks
「結(jié)論3：」
「會話56：」（1）狀態(tài)WAIT-等待鎖
（2）正在等待第1個數(shù)據(jù)文件上的第109頁上的第0行資源的共享鎖
（3）持有第1個數(shù)據(jù)文件上的第109頁資源的意向共享鎖
（3）持有OBJECT資源，意向共享鎖
（4）持有DATABASE資源，意向共享鎖
「會話52：」
（1）狀態(tài)WAIT-授予鎖
（2）正在等待第1個數(shù)據(jù)文件上的第109頁上的第0行資源的排他鎖（3）持有第1個數(shù)據(jù)文件上的第109頁資源的排他鎖
（3）持有OBJECT資源，排他鎖
（4）持有DATABASE資源，排他鎖
演示與總結(jié)如下所示：
3.2.2 分析阻塞原因
3.2.2.1 sys.dm_tran_locks 視圖
（1）該動態(tài)視圖可以查詢出哪些資源被哪個進程ID鎖了
（2）查詢出對資源授予或正在等待的鎖模式
（3）查詢出被鎖定資源的類型
上面的查詢語句3已經(jīng)用到了這個視圖，可以參考上圖中的分析說明。
3.2.2.2 sys.dm_exec_connections 視圖
（1）查詢出該動態(tài)視圖可以查詢出進程相關(guān)的信息
（2）查詢出最后一次發(fā)生讀操作和寫操作的時間last_read,last_write
（3）查詢出進程執(zhí)行的最后一個SQL批處理的二進制標記most_recent_sql_handle
SELECT??session_id?,
????????connect_time?,
????????last_read?,
????????last_write?,
????????most_recent_sql_handle
FROM????sys.dm_exec_connections
?
WHERE???session_id?IN?(?52,?56?)
「結(jié)論：」
「會話52：」
（1）connect_time連接時間：2016-06-07 07:09:41.103
（2）last_read最后一次讀操作時間：2016-06-07 07:10:56.233
（3）last_write最后一次寫操作時間：2016-06-07 07:10:57.873
（4）most_recent_sql_handle這是一個二進制標記，最后一個SQL批處理
「會話56：」
（1）狀態(tài)WAIT-授予鎖
（2）正在等待第1個數(shù)據(jù)文件上的第109頁上的第0行資源的排他鎖（3）持有第1個數(shù)據(jù)文件上的第109頁資源的排他鎖
（3）持有OBJECT資源，排他鎖
（4）持有DATABASE資源，排他鎖
演示與總結(jié)如下所示：
3.2.2.3 sys.dm_exec_sql_text 表函數(shù)
示例：
會話52：
執(zhí)行的SQL語句：
BEGIN?TRAN
UPDATE?dbo.myProduct
SET?price?=?price?+?1
WHERE?id?=?1
會話56：
執(zhí)行的SQL語句：
(@1?tinyint)
SELECT?*?FROM?[dbo].[myProduct]
WHERE?[id]=@1
（1）該函數(shù)可以將二進制標記most_recent_sql_handle作為參數(shù)，然后返回SQL代碼。
（2）阻塞進程在不斷地運行，所以在代碼中看到的最后一個操作不一定是導致問題的語句。在本例中最后一條執(zhí)行語句是導致阻塞的語句。
SELECT??session_id?,
????????text
FROM????sys.dm_exec_connections
????????CROSS?APPLY?sys.dm_exec_sql_text
????????(most_recent_sql_handle)?AS?ST
WHERE???session_id?IN?(?52,?56?)
演示與總結(jié)如下所示：
3.2.2.4 sys.dm_exec_sessions 視圖
（1）會話建立的時間login_time
（2）特定于會話的客戶端工作站名稱host_name
（3）初始化會話的客戶端程序的名稱program_name
（4）會話所使用的SQL Server登錄名login_name
（5）最近一次會話請求的開始時間last_request_start_time
（6）最近一次會話請求的完成時間last_request_end_time
SELECT?*?FROM?sys.dm_exec_sessions
演示與總結(jié)如下所示：
3.2.2.5 sys.dm_exec_requests 視圖
（1）識別出阻塞鏈涉及到的會話、爭用的資源、被阻塞會話等待了多長時間
SELECT?*?FROM?sys.dm_exec_sessions
「結(jié)論：」
「會話56：」
（1）被會話52阻塞，blocking_session_id = 52
（2）會話52的開始時間start_time
（3）狀態(tài)掛起status = suspended
（4）掛起的命令command
演示與總結(jié)如下所示：
3.2.3 解決阻塞問題
3.2.3.1 Lock_TIMEOUT 選項
（1）設(shè)置會話等待鎖釋放的超時期限
（2）默認情況下會話不會設(shè)置等待鎖釋放的超時期限
（3）設(shè)置會話超時期限為5秒， SET Lock_TIMEOUT 5000
（4）鎖定如果超時，不會引發(fā)事務(wù)回滾
（5）取消會話超時鎖定的設(shè)置，SET LOCK_TIMEOUT -1
如果超時，將顯示以下錯誤：
3.2.3.1 KILL命令
（1）殺掉會話52，KILL 52
（2）殺掉會話，會引起事務(wù)回滾，同時釋放排他鎖
四、隔離級別
4.1 基本概念
「（1）隔離級別用來做什么」
a.隔離級別用于決定如何控制并發(fā)用戶讀寫數(shù)據(jù)的操作
「（2）寫操作」
a.任何對表做出修改的語句
b.使用排他鎖
c.不能修改讀操作獲得的鎖和鎖的持續(xù)時間
「（3）讀操作：」
a.任何檢索數(shù)據(jù)的語句
b.默認使用共享鎖
c.使用隔離級別來控制讀操作的處理方式
4.2 隔離級別的分類
（1）未提交讀 （READ UNCOMMITTED）
（2）已提交讀（READ COMMITTED）（默認值）
（3）可重復讀（REPEATABLE READ）
（4）可序列化（SERIALIZABLE）
（5）快照（SNAPSHOT）
（6）已經(jīng)提交讀快照（READ_COMMITTED_SNAPSHOT）
4.3 隔離級別的設(shè)置
「（1）設(shè)置整個會話的隔離級別」
SET?TRANSACTION?ISOLATION?LEVEL?<isolation?name>;
SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED;
「（2）用表提示設(shè)置查詢的隔離級別」
SELECT?...?FROM?<table>?WITH?(<isolation?name>);
SELECT?*?FROM?dbo.myProduct?WITH?(READCOMMITTED);
注意：
1.設(shè)置會話選項的隔離級別時，隔離級別中的每個單詞之間需要用空格分隔
2.用表提示的隔離級別時，隔離級別中的每個單詞之間不需要用空格分隔
3.表提示的隔離級別有同義詞，如：NOLOCK->READUNCOMMITTED,HOLDLOCK->REPEATABLEREAD
4.隔離級別的嚴格性：1.未提交讀<2.已提交讀<3.可重復讀<4.可序列化
5.隔離級別越高，一致性越高，并發(fā)性越低
6.基于快照的隔離級別，SQL Server將提交過的行保存到tempdb數(shù)據(jù)庫中，當讀操作發(fā)現(xiàn)行的當前版本和它們預期的不一致時，可以立即得到行的以前版本，從而不用請求共享鎖也能取得預期的一致性。
4.4 隔離級別的行為方式
4.4.1 未提交讀 （READ UNCOMMITTED）
打開兩個查詢窗口，Connetion1,connection2
Step1: 執(zhí)行Connection1的階段2的SQL 語句，然后執(zhí)行connection2的SQL語句
Step2: 執(zhí)行Connection1的階段3的SQL 語句，執(zhí)行connection2的SQL語句
Step3: 執(zhí)行Connection1的階段4的SQL 語句，執(zhí)行connection2的SQL語句
--階段2
UPDATE??myProduct
SET?????price?=?price?+?1
WHERE???id?=?1;
??
SELECT??id?,
????????price
FROM????dbo.myProduct
WHERE???id?=?1;
??
--階段3
UPDATE??myProduct
SET?????price?=?price?+?5
WHERE???id?=?1;
??
SELECT??id?,
????????price
FROM????dbo.myProduct
WHERE???id?=?1;
??
--階段4
COMMIT?TRAN
--在階段2執(zhí)行之后
SET?TRAN?ISOLATION?LEVEL?READ?UNCOMMITTED
BEGIN?TRAN;
SELECT??id?,
????????price
FROM????dbo.myProduct
WHERE???id?=?1
?
COMMIT?TRAN;
兩個事務(wù)的流程圖：
階段1：Price=10,事務(wù)A對myProduct表請求排他鎖
階段2：事務(wù)A對myProduct表使用了排他鎖，更新price = price + 1，然后事務(wù)A查詢price的價格: price=11。事務(wù)B不請求任何鎖，事務(wù)B在A更新Price之后進行查詢，price=11
階段3：事務(wù)A更新price = price + 5，然后事務(wù)A查詢price的價格，price = 16。事務(wù)B查詢price的價格: price=16
階段4：事務(wù)A釋放排他鎖
階段5：事務(wù)A中查詢price的價格:price = 16。事務(wù)B查詢price的價格: price=16
「大家可以看到事務(wù)B有兩種結(jié)果，這就是“未提交讀 （READ UNCOMMITTED）”隔離級別的含義：」
（1）讀操作可以讀取未提交的修改（也稱為臟讀）。
（2）讀操作不會妨礙寫操作請求排他鎖，其他事務(wù)正在進行讀操作時，寫操作可以同時對這些數(shù)據(jù)進行修改。
（3）事務(wù)A進行了多次修改，事務(wù)B在不同階段進行查詢時可能會有不同的結(jié)果。
4.4.2 已提交讀（READ COMMITTED）（默認值）
打開兩個查詢窗口，Connetion1,connection2
Step1: 執(zhí)行Connection1的SQL 語句
UPDATE`?`dbo.myProduct?``SET`?`price?=?price?+?1?``WHERE`?`id=1
SELECT`?`*?``FROM`?`dbo.myProduct?``WHERE`?`id?=1
Step2: 執(zhí)行Connection2的SQL 語句
SET`?`TRANSACTION`?`ISOLATION`?`LEVEL`?`READ`?`COMMITTED
SELECT`?`*?``FROM`?`dbo.myProduct?``WHERE`?`id?=?1
兩個事務(wù)的流程圖：
階段1：Price=10,事務(wù)A對myProduct表請求排他鎖
階段2：事務(wù)A對myProduct表使用了排他鎖，更新price = price + 1，然后事務(wù)A查詢price的價格: price=11。然后事務(wù)B請求共享鎖進行讀操作，查詢price，
由于在當前隔離級別下，事務(wù)A的排他鎖和事務(wù)B的共享鎖存在沖突，所以事務(wù)B需要等待事務(wù)A釋放排他鎖后才能讀取數(shù)據(jù)。
階段3：事務(wù)A提交事務(wù)（COMMIT TRAN）
階段4：事務(wù)A提交完事務(wù)后，釋放排他鎖
階段5：事務(wù)B獲得了共享鎖，進行讀操作，price=11
「“已提交讀 （READ UNCOMMITTED）”隔離級別的含義：」
（1）必須獲得共享鎖才能進行讀操作，其他事務(wù)如果對該資源持有排他鎖，則共享鎖必須等待排他鎖釋放。
（2）讀操作不能讀取未提交的修改，讀操作讀取到的數(shù)據(jù)是提交過的修改。
（3）讀操作不會在事務(wù)持續(xù)期間內(nèi)保留共享鎖，其他事務(wù)可以在兩個讀操作之間更改數(shù)據(jù)資源，讀操作因而可能每次得到不同的取值。這種現(xiàn)象稱為“不可重復讀”
4.4.3 可重復讀（REPEATABLE READ）
打開兩個查詢窗口，Connetion1,connection2
Step1: 執(zhí)行Connection1的SQL 語句
SET`?`TRANSACTION`?`ISOLATION`?`LEVEL`?`REPEATABLE`?`READ
SELECT`?`*?``FROM`?`dbo.myProduct?``WHERE`?`id?=?1
Step2: 執(zhí)行Connection2的SQL 語句
UPDATE`?`dbo.myProduct?``SET`?`price?=?price?+?1?``WHERE`?`id=1???
演示與總結(jié)如下所示：
兩個事務(wù)的流程圖：
階段1：Price=10,事務(wù)A對myProduct表請求共享鎖
階段2：事務(wù)A對myProduct表使用了共享鎖，事務(wù)A查詢price的價格: price=10，事務(wù)A一直持有共享鎖直到事務(wù)A完成為止。然后事務(wù)B請求排他鎖進行寫操作price=price+1，
由于在當前隔離級別下，事務(wù)A的共享鎖和事務(wù)B請求的排他鎖存在沖突，所以事務(wù)B需要等待事務(wù)A釋放共享鎖后才能修改數(shù)據(jù)。
階段3：事務(wù)A查詢price, price=10, 說明事務(wù)B的更新操作被阻塞了，更新操作沒有被執(zhí)行。然后事務(wù)A提交事務(wù)（COMMIT TRAN）
階段4：事務(wù)A提交完事務(wù)后，釋放共享鎖
階段5：事務(wù)B獲得了排他鎖，進行寫操作，price=11
**“可重復讀 （REPEATABLE READ）”隔離級別的含義：? **
（1）必須獲得共享鎖才能進行讀操作，獲得的共享鎖將一直保持直到事務(wù)完成之止。
（2）在獲得共享鎖的事務(wù)完成之前，沒有其他事務(wù)能夠獲得排他鎖修改這一數(shù)據(jù)資源，這樣可以保證實現(xiàn)可重復的讀取。
（3）兩個事務(wù)在第一次讀操作之后都將保留它們獲得的共享鎖，所以任何一個事務(wù)都不能獲得為了更新數(shù)據(jù)而需要的排他鎖，這種情況將會導致死鎖（deadlock），不過卻避免了更新沖突。
4.4.4 可序列化（SERIALIZABLE）
打開兩個查詢窗口，Connetion1,connection2
Step1: 執(zhí)行Connection1的SQL 語句
BEGIN?TRANSACTION
SET?TRANSACTION?ISOLATION?LEVEL?SERIALIZABLE???
SELECT?*?FROM?dbo.myProduct?WHERE?id?=?1
Step2: 執(zhí)行Connection2的SQL 語句
INSERT`?`INTO`?`dbo.myProduct(id,?price)?``VALUES`?`(1,?20)
演示與總結(jié)如下所示：
兩個事務(wù)的流程圖：
階段1：Price=10,事務(wù)A對myProduct表請求共享鎖
階段2：事務(wù)A對myProduct表使用了共享鎖，事務(wù)A查詢id=1的price的價格:1行記錄，price=10，事務(wù)A一直持有共享鎖直到事務(wù)A完成為止。然后事務(wù)B請求排他鎖進行插入操作id=1,price=20，
由于在當前隔離級別下，事務(wù)B試圖增加能夠滿足事務(wù)A的讀操作的查詢搜索條件的新行，所以事務(wù)A的共享鎖和事務(wù)B請求的排他鎖存在沖突，事務(wù)B需要等待事務(wù)A釋放共享鎖后才能插入數(shù)據(jù)。
階段3：事務(wù)A查詢出id=1的數(shù)據(jù)只有1行，說明事務(wù)B的插入操作被阻塞了，插入操作沒有被執(zhí)行。然后事務(wù)A提交事務(wù)（COMMIT TRAN）
階段4：事務(wù)A提交完事務(wù)后，釋放共享鎖
階段5：事務(wù)B獲得了排他鎖，進行插入操作，插入成功，查詢出id=1的數(shù)據(jù)有兩條-
「“可序列化（SERIALIZABLE）”隔離級別的含義：」
（1）必須獲得共享鎖才能進行讀操作，獲得的共享鎖將一直保持直到事務(wù)完成之止。
（2）在獲得共享鎖的事務(wù)完成之前，沒有其他事務(wù)能夠獲得排他鎖修改這一數(shù)據(jù)資源，且當其他事務(wù)增加能夠滿足當前事務(wù)的讀操作的查詢搜索條件的新行時，其他事務(wù)將會被阻塞，直到當前事務(wù)完成然后釋放共享鎖，其他事務(wù)才能獲得排他鎖進行插入操作。
（3）事務(wù)中的讀操作在任何情況下讀取到的數(shù)據(jù)是一致的，不會出現(xiàn)幻影行（幻讀）。
（4）范圍鎖：讀操作鎖定滿足查詢搜索條件范圍的鎖。
4.5 隔離級別總結(jié)
「臟讀：」 讀取未提交的更改。
「不可重復讀：」 讀操作不會在事務(wù)持續(xù)期間內(nèi)保留共享鎖，其他事務(wù)可以在兩個讀操作之間更改數(shù)據(jù)資源，讀操作因而可能每次得到不同的取值。
「丟失更新：」 兩個事務(wù)進行讀操作，獲得資源上的共享鎖，讀取完數(shù)據(jù)后，不再持有資源上的任何鎖，兩個事務(wù)都能更新這個值，最后進行更新的事務(wù)將會覆蓋其他事務(wù)做的更改，導致其他事務(wù)更改的數(shù)據(jù)丟失。
「幻讀：」 第一次和第二次讀取到的數(shù)據(jù)行數(shù)不一致。
「范圍鎖：」 讀操作鎖定滿足查詢搜索條件范圍的鎖
五.死鎖
死鎖是指一種進程之間互相永久阻塞的狀態(tài)，可能涉及兩個或更多的進程。
打開兩個查詢窗口，Connetion1,connection2
Step1: 執(zhí)行Connection1的SQL 語句
SET`?`TRANSACTION`?`ISOLATION`?`LEVEL`?`READ`?`COMMITTED
BEGIN`?`TRAN
UPDATE`?`dbo.myProduct?``SET`?`price?=?price?+?1?``WHERE`?`id=1
SELECT`?`*?``FROM`?`dbo.myOrder?``WHERE`?`id?=1
Step2: 執(zhí)行Connection2的SQL 語句
SET`?`TRANSACTION`?`ISOLATION`?`LEVEL`?`READ`?`COMMITTED
BEGIN`?`TRAN
UPDATE`?`dbo.myOrder?``SET`?`customer?=?``'ddd'`?`WHERE`?`id?=?1
SELECT`?`*?``FROM`?`dbo.myProduct?``WHERE`?`id?=?1
演示與總結(jié)如下所示：
兩個事務(wù)的流程圖：
階段1：Price=10,事務(wù)A對myProduct表請求排他鎖。Customer = aaa,事務(wù)B對myOrder請求排他鎖
階段2：事務(wù)A對myProduct表使用了排他鎖，更新price = price + 1。然后事務(wù)B對myOrder表使用了排他鎖，更新customer=ddd。
階段3：事務(wù)A查詢myOrder表，對myOrder表請求共享鎖，因為事務(wù)A的請求的共享鎖與事務(wù)B的排他鎖沖突，所以事務(wù)A被阻塞。然后事務(wù)B查詢myProduct表，對myProduct表請求共享鎖，因為事務(wù)B的請求的共享鎖與事務(wù)A的排他鎖沖突，所以事務(wù)B被阻塞。
階段4：事務(wù)A等待事務(wù)B的排他鎖釋放，事務(wù)B等待事務(wù)A的排他鎖釋放，導致死鎖。事務(wù)A和事務(wù)B都被阻塞了。
階段5：SQL Server在幾秒之內(nèi)檢測到死鎖，會選擇一個事務(wù)作為死鎖的犧牲品，終止這個事務(wù)，并回滾這個事務(wù)所做的操作。在這個例子中，事務(wù)A被終止，提示信息：事務(wù)(進程 ID 53)與另一個進程被死鎖在 鎖 資源上，并且已被選作死鎖犧牲品。請重新運行該事務(wù)。
“「死鎖 （Dead Lock）」” 的一些注意事項：
（1）如果兩個事務(wù)沒有設(shè)置死鎖優(yōu)先級，且兩個事務(wù)進行的工作量也差不多一樣時，任何一個事務(wù)都有可能被終止。
（2）解除死鎖要付出一定的系統(tǒng)開銷，因為這個過程會涉及撤銷已經(jīng)執(zhí)行過的處理。
（3）事務(wù)處理的時間時間越長，持有鎖的時間就越長，死鎖的可能性也就越大，應該盡可能保持事務(wù)簡短，把邏輯上可以不屬于同一個工作單元的操作移到事務(wù)以外。
（4）上面的例子中，事務(wù)A和事務(wù)B以相反順序訪問資源，所以發(fā)生了死鎖。如果兩個事務(wù)按同樣的順序來訪問資源，則不會發(fā)生這種類型的死鎖。在不改變程序的邏輯情況下，可以通過交換順序來解決死鎖的問題。
——End——
后臺回復關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨 后臺回復關(guān)鍵字：進群，帶你進入高手如云的交流群。 推薦閱讀 這是一個能學到技術(shù)的公眾號，歡迎關(guān)注