事務(wù)隔離級(jí)別
什么是事務(wù)屹逛？
事務(wù)（Transaction）由作為包含執(zhí)行的單個(gè)命令或一組命令組成。
通過事務(wù)可以將多個(gè)操作合并為單個(gè)工作單元。
事務(wù)由事務(wù)開始(begin transaction)和事務(wù)結(jié)束(end transaction)之間執(zhí)行的全體操作組成。
如果某一事務(wù)成功，則在該事務(wù)中進(jìn)行的所有數(shù)據(jù)修改均會(huì)提交(commit transaction)掰派，成為數(shù)據(jù)庫(kù)中的永久組成部分。 如果事務(wù)遇到錯(cuò)誤且必須取消或回滾(rollback transaction)左痢，則所有數(shù)據(jù)修改均被清除靡羡。
如果鎖定持續(xù)時(shí)間過長(zhǎng)系洛，則涉及多個(gè)資源的事務(wù)可能會(huì)降低并發(fā)性。 因此略步，事務(wù)應(yīng)盡量保持簡(jiǎn)短描扯。
事務(wù)的4個(gè)特性
事務(wù)必須符合 ACID 屬性（原子性、一致性趟薄、隔離性和持久性）才能保證數(shù)據(jù)的一致性绽诚。
原子性（Atomicity）
事務(wù)必須只執(zhí)行一次，并且必須是原子的杭煎，即所有工作都已完成恩够，或者沒有任何工作。原子化消除了只處理部分操作的機(jī)會(huì)羡铲。一致性（Consistency）
事務(wù)必須保持?jǐn)?shù)據(jù)一致性蜂桶，將數(shù)據(jù)的一個(gè)一致狀態(tài)轉(zhuǎn)換為另一種一致的數(shù)據(jù)狀態(tài)。隔離性（Isolation）
事務(wù)必須為隔離單位也切，一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾扑媚。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾雷恃。持久性（Durability）
事務(wù)必須可恢復(fù)疆股，因此必須具有持久性。 如果事務(wù)已提交倒槐，則系統(tǒng)保證其更新可以保留押桃，即使在提交后計(jì)算機(jī)立即崩潰。 使用專用日志記錄导犹，系統(tǒng)重新啟動(dòng)過程可以完成事務(wù)所需的未完成操作，使事務(wù)持久羡忘。
事務(wù)的分類
事務(wù)分為三類：顯式事務(wù)谎痢、隱式事務(wù)、自動(dòng)提交事務(wù)
顯式事務(wù)
用 begin transaction 明確指定事務(wù)的開始卷雕，由 commit transaction 提交事務(wù)节猿、rollback transaction 回滾事務(wù)到事務(wù)結(jié)束。隱式事務(wù)
通過設(shè)置 set implicit_transactions on 語(yǔ)句漫雕，將隱式事務(wù)模式設(shè)置為打開滨嘱。
當(dāng)以隱式事務(wù)模式操作時(shí)，不必使用 begin transaction 開啟事務(wù)浸间，當(dāng)一個(gè)事務(wù)結(jié)束后太雨，這個(gè)模式會(huì)自動(dòng)啟用下一個(gè)事務(wù)，只需使用 commit transaction 提交事務(wù)或 Rollback Transaction 回滾事務(wù)即可魁蒜。自動(dòng)提交事務(wù)
這是 SQL Server 的默認(rèn)模式囊扳，它將每條單獨(dú)的 T-SQL 語(yǔ)句視為一個(gè)事務(wù)吩翻。如果成功執(zhí)行，則自動(dòng)提交锥咸。如果錯(cuò)誤狭瞎，則自動(dòng)回滾。
事務(wù)的隔離等級(jí)
事務(wù)的隔離級(jí)別 - 未提交的讀取
允許讀其他事務(wù)已修改未提交的數(shù)據(jù)行搏予，相當(dāng)于WITH(NOLOCK)提示熊锭，會(huì)引發(fā)臟讀的問題。
事務(wù)的隔離級(jí)別 - 已提交的讀取
SQL Server 默認(rèn)的隔離級(jí)別雪侥。當(dāng)事務(wù)在執(zhí)行讀操作時(shí)碗殷，會(huì)放置共享鎖，以防止其他事務(wù)修改數(shù)據(jù)校镐，當(dāng)讀完成時(shí)亿扁，會(huì)立即自動(dòng)釋放共享鎖，有利于事務(wù)并發(fā)鸟廓〈幼＃可避免臟讀，但會(huì)引發(fā)不可重復(fù)讀的問題引谜。
事務(wù)的隔離級(jí)別 - 可重復(fù)的讀取
所有的共享鎖（查詢數(shù)據(jù)行的讀行為）均保留至交易結(jié)束為止牍陌，而不是讀取完畢后就釋放共享鎖，避免了不可重復(fù)讀的問題员咽。因沒有申請(qǐng)范圍鎖毒涧，會(huì)產(chǎn)生幻讀問題。
事務(wù)的隔離級(jí)別 - 可序列化
事務(wù)隔離級(jí)別的最高級(jí)贝室，最嚴(yán)謹(jǐn)?shù)牡燃?jí)契讲，會(huì)鎖定整個(gè)范圍的索引鍵，事務(wù)之間完全隔離滑频，基本無(wú)并發(fā)捡偏。與SELECT搭配HOLDLOCK的效果相同，事務(wù)之間完全是串行化執(zhí)行峡迷。
事務(wù)的隔離級(jí)別-行版本控制隔離級(jí)別
事務(wù)的隔離級(jí)別-行版本控制隔離級(jí)別-讀取已提交的快照
執(zhí)行下面的語(yǔ)句開啟
ALTER DATABASE [DBName] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
在RCSI隔離級(jí)別下银伟，事務(wù)有兩個(gè)特性：
事務(wù)使用行版本（Row Version）代替共享鎖，讀操作不會(huì)阻塞其他事務(wù)的寫操作绘搞；
RCSI隔離級(jí)別保證語(yǔ)句級(jí)別的事務(wù)一致性彤避，查詢語(yǔ)句只能讀取在該語(yǔ)句執(zhí)行時(shí)已經(jīng)提交的數(shù)據(jù)，如果在該語(yǔ)句執(zhí)行時(shí)數(shù)據(jù)更新尚未提交夯辖，該語(yǔ)句讀取不到琉预。
存在不可重復(fù)讀的問題
事務(wù)的隔離級(jí)別-行版本控制隔離級(jí)別-快照
執(zhí)行下面的語(yǔ)句開啟
ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION ON
把會(huì)話的隔離級(jí)別設(shè)置為SNAPSHOT，事務(wù)才能訪問行版本的數(shù)據(jù)蒿褂。
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
如果不把會(huì)話的隔離級(jí)別設(shè)置為SNAPSHOT模孩，會(huì)話的隔離級(jí)別是悲觀模式下的READ COMMITTED尖阔。
SNAPSHOT隔離級(jí)別不會(huì)阻塞其他事務(wù)的寫操作，該隔離級(jí)別忽略數(shù)據(jù)的修改操作榨咐，只讀取事務(wù)開啟前已提交的行版本數(shù)據(jù)介却，事務(wù)運(yùn)行期間由其他事務(wù)修改的數(shù)據(jù)不會(huì)被讀取到。當(dāng)SNAPSHOT事務(wù)嘗試修改由其他事務(wù)修改的數(shù)據(jù)時(shí)块茁，產(chǎn)生更新沖突齿坷，更新操作會(huì)異常終止。
事務(wù)的隔離級(jí)別 - 讀取已提交與行版本控制隔離級(jí)別的對(duì)比
鎖的介紹
鎖模式
共享鎖
共享鎖（S 鎖）允許并發(fā)事務(wù)在封閉式并發(fā)控制下讀取 (SELECT) 資源数焊。 資源上存在共享鎖（S 鎖）時(shí)永淌，任何其他事務(wù)都不能修改數(shù)據(jù)。 讀取操作一完成佩耳，就立即釋放資源上的共享鎖（S 鎖）遂蛀，除非將事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)持續(xù)時(shí)間內(nèi)用鎖定提示（HOLDLOCK）保留共享鎖（S 鎖）干厚。
更新鎖
更新鎖（U 鎖）可以防止常見的死鎖李滴。共享模式到排他鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的排他鎖與其他事務(wù)的共享模式鎖不兼容蛮瞄；發(fā)生鎖等待所坯。若要避免這種潛在的死鎖問題，請(qǐng)使用更新鎖（U 鎖）挂捅。 一次只有一個(gè)事務(wù)可以獲得資源的更新鎖（U 鎖）芹助。 如果事務(wù)修改資源，則更新鎖（U 鎖）轉(zhuǎn)換為排他鎖（X 鎖）闲先。
排他鎖
排他鎖（X 鎖）可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪問状土。 使用排他鎖（X 鎖）時(shí)，任何其他事務(wù)都無(wú)法修改數(shù)據(jù)伺糠；僅在使用 NOLOCK 提示或未提交讀隔離級(jí)別時(shí)才會(huì)進(jìn)行讀取操作蒙谓。
數(shù)據(jù)修改語(yǔ)句（如 INSERT、UPDATE 和 DELETE）通常請(qǐng)求共享鎖和排他鎖退盯。
架構(gòu)鎖
在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類型是架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)泻肯。
意向鎖
建立鎖層次結(jié)構(gòu)渊迁，這些鎖指示事務(wù)正在處理層次結(jié)構(gòu)中較低級(jí)別的某些資源，而不是所有資源灶挟，較低級(jí)別的資源將具有共享鎖琉朽、更新鎖或排他鎖。意向鎖說(shuō)明SQL Server有在資源的低層獲得共享鎖或排它鎖的意向稚铣。
鎖的兼容性
事務(wù)是為了解決并發(fā)情景下箱叁，數(shù)據(jù)變更過程的一致性墅垮。
事務(wù)隔離級(jí)別為了解決并發(fā)情景下不同程度的數(shù)據(jù)讀取異常。
事務(wù)隔離級(jí)別由鎖來(lái)實(shí)現(xiàn)耕漱。
可鎖定的資源
鎖升級(jí)
鎖升級(jí)是將許多較細(xì)粒度的鎖轉(zhuǎn)換成數(shù)量更少的較粗粒度的鎖的過程算色，這樣可以減少系統(tǒng)開銷，但卻增加了并發(fā)爭(zhēng)用的可能性螟够。
當(dāng) SQL Server 數(shù)據(jù)庫(kù)引擎 獲取低級(jí)別的鎖時(shí)灾梦，它還將在包含更低級(jí)別對(duì)象的對(duì)象上放置意向鎖。如獲取了行上的共享鎖妓笙，還會(huì)在頁(yè)和表上放置意向共享鎖若河。
數(shù)據(jù)庫(kù)引擎 不會(huì)將行鎖或鍵范圍鎖升級(jí)到頁(yè)鎖，而是將它們直接升級(jí)到表鎖寞宫。 同樣萧福，頁(yè)鎖始終升級(jí)到表鎖。
鎖升級(jí)閾值
單個(gè) Transact-SQL 語(yǔ)句在單個(gè)未分區(qū)表或索引上獲得至少 5,000 個(gè)鎖辈赋，將觸發(fā)鎖升級(jí)鲫忍。
如何從表中刪除超過5000條的記錄？
DELETE FROM LogMessages WHERE LogDate < '2/1/2021' --會(huì)導(dǎo)致鎖升級(jí)引起阻塞
將大批操作分成多個(gè)小批操作炭庙，可以顯著減少每個(gè)事務(wù)累積的鎖數(shù)量饲窿，并防止鎖升級(jí)。
WHILE EXISTS (SELECT TOP 1 1 FROM LogMessages WHERE LogDate < '2/1/2021')
BEGIN
DELETE TOP(500) FROM LogMessages WHERE LogDate < '2/1/2021'
END
表提示應(yīng)用
在對(duì)數(shù)據(jù)的準(zhǔn)確性要求不太高時(shí)焕蹄，或查詢不會(huì)修改的數(shù)據(jù)時(shí)逾雄，盡量使用WITH(NOLOCK)
在取數(shù)時(shí)，不希望同一條記錄被多個(gè)進(jìn)程同時(shí)獲取到腻脏，在查詢時(shí)應(yīng)開啟事務(wù)并使用WITH(ROWLOCK,XLOCK,READPAST)
在高并發(fā)對(duì)同一條數(shù)據(jù)的值進(jìn)行修改的情況下鸦泳，為了保證數(shù)據(jù)的準(zhǔn)確性，在查詢時(shí)應(yīng)開啟事務(wù)并使用WITH(UPDLOCK)
NOLOCK一定不會(huì)產(chǎn)生阻塞嗎永品？
READUNCOMMITTED 和 NOLOCK 提示僅適用于數(shù)據(jù)鎖做鹰。 所有查詢（包括那些帶有 READUNCOMMITTED 和 NOLOCK 提示的查詢）都會(huì)在編譯和執(zhí)行過程中獲取 Sch-S（架構(gòu)穩(wěn)定性）鎖。 因此鼎姐，當(dāng)并發(fā)事務(wù)持有表的 Sch-M（架構(gòu)修改）鎖時(shí)钾麸，將阻塞查詢。 例如炕桨，數(shù)據(jù)定義語(yǔ)言 (DDL) 操作在修改表的架構(gòu)信息之前獲取 Sch-M 鎖饭尝。 所有并發(fā)查詢（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示運(yùn)行的查詢）都會(huì)在嘗試獲取 Sch-S 鎖時(shí)被阻塞。 相反献宫，持有 Sch-S 鎖的查詢將阻塞嘗試獲取 Sch-M 鎖的并發(fā)事務(wù)钥平。
阻塞與死鎖
阻塞
是指當(dāng)一個(gè)數(shù)據(jù)庫(kù)會(huì)話中的事務(wù)，正在鎖定其他會(huì)話事務(wù)想要讀取或修改的資源姊途，造成這些會(huì)話發(fā)出的請(qǐng)求進(jìn)入等待的狀態(tài)涉瘾。SQL Server 默認(rèn)會(huì)讓被阻塞的請(qǐng)求無(wú)限期地一直等待知态，直到原來(lái)的事務(wù)釋放相關(guān)的鎖，或直到它超時(shí) (根據(jù) SET LOCK_TIMEOUT參數(shù))立叛、服務(wù)器關(guān)閉负敏、進(jìn)程被殺死。一般的系統(tǒng)中囚巴，偶爾有短時(shí)間的阻塞是正常且合理的原在；但若設(shè)計(jì)不良的程序，就可能導(dǎo)致長(zhǎng)時(shí)間的阻塞彤叉，這樣就不必要地鎖定了資源庶柿，而且阻塞了其他會(huì)話欲讀取或更新的需求。
阻塞查看
查看阻塞方法1
SELECT session_id,wait_duration_ms,wait_type,blocking_session_id,resource_description
FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL
會(huì)話55的共享鎖請(qǐng)求被會(huì)話51的排他鎖阻塞了
查看阻塞方法2
SELECT SP.* FROM SYS.SYSPROCESSES SP
WHERE (SP.status = N'suspended' AND SP.blocked<>0)
OR (SP.spid IN (SELECT DISTINCT BLOCKED FROM SYS.SYSPROCESSES WHERE BLOCKED<>0) AND SP.BLOCKED=0)
blocked字段中的值就是引起阻塞的會(huì)話ID秽浇，blocked=0表示是阻塞源
查看阻塞鎖定的資源的情況
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id IN (51,55)
ORDER BY request_session_id,resource_type
減少阻塞的建議
盡可能讓事務(wù)輕薄短小浮庐、讓鎖定的時(shí)間盡量短，例如把不必要的命令移出事務(wù)外柬焕，或把一個(gè)大量更新的事務(wù)审残，切成多個(gè)更新較少的事務(wù)，以改善并發(fā)性斑举。
將組成事務(wù)的 SQL 語(yǔ)句搅轿，放在一個(gè)「批 (batch) 處理」，以避免不必要的延遲富玷。這些延遲常由 BEGIN TRAN ... COMMIT TRAN 命令之間的網(wǎng)絡(luò) I/O 所引起璧坟。
若對(duì)數(shù)據(jù)的準(zhǔn)確性要求不高，可以在SELECT語(yǔ)句中加WITH (NOLOCK)赎懦。
避免在事務(wù)執(zhí)行期間雀鹃，等待用戶的反饋或交互，這樣可能會(huì)造成無(wú)限期的持有鎖定励两。
避免事務(wù)開啟后查詢大量的數(shù)據(jù)黎茎，盡可能在事務(wù)開啟前先將數(shù)據(jù)插入臨時(shí)表。
關(guān)注SQL語(yǔ)句的性能当悔，提前給表建立合適的索引傅瞻，避免因缺失索引導(dǎo)致的阻塞。
對(duì)于有大量并發(fā)的數(shù)據(jù)庫(kù)盲憎，建議在充分測(cè)試的情況下嗅骄，開啟已提交讀快照隔離級(jí)別READ_COMMITTED_SNAPSHOT，消除讀取數(shù)據(jù)時(shí)的阻塞問題焙畔。
死鎖
指當(dāng)兩個(gè)進(jìn)程各自擁有一個(gè)鎖掸读，而這個(gè)鎖是對(duì)方繼續(xù)運(yùn)行所需要的串远，這樣就會(huì)出現(xiàn)兩個(gè)進(jìn)程相互阻止對(duì)方運(yùn)行的情況宏多，這就會(huì)出現(xiàn)死鎖儿惫，如果對(duì)這種情況不進(jìn)行處理的話，那么就將無(wú)限期的等待下去伸但，在SQL Server中內(nèi)置了死鎖探測(cè)肾请，每5秒鐘鎖監(jiān)視器就會(huì)檢查死鎖狀態(tài)，如果發(fā)現(xiàn)死鎖更胖，SQL Server通常選擇終止回滾開銷最小的進(jìn)程來(lái)解決死鎖問題铛铁。
在示例中，對(duì)于 Part 表鎖資源却妨，事務(wù) T1 依賴于事務(wù) T2饵逐。 同樣，對(duì)于 Supplier 表鎖資源彪标，事務(wù) T2 依賴于事務(wù) T1倍权。 因?yàn)檫@些依賴關(guān)系形成了一個(gè)循環(huán)，所以在事務(wù) T1 和事務(wù) T2 之間存在死鎖捞烟。
查詢死鎖信息的工具
跟蹤標(biāo)志 1204 和跟蹤標(biāo)志 1222
發(fā)生死鎖時(shí)薄声，跟蹤標(biāo)志 1204 和跟蹤標(biāo)志 1222 會(huì)返回在 SQL Server 錯(cuò)誤日志中捕獲的信息。
跟蹤標(biāo)志 1204 會(huì)報(bào)告由死鎖所涉及的每個(gè)節(jié)點(diǎn)設(shè)置格式的死鎖信息题画。
跟蹤標(biāo)志 1222 會(huì)設(shè)置死鎖信息的格式默辨，順序?yàn)橄劝催M(jìn)程，然后按資源苍息。
可以同時(shí)啟用這兩個(gè)跟蹤標(biāo)志缩幸，以獲取同一個(gè)死鎖事件的兩種表示形式。
重要
避免在導(dǎo)致死鎖的工作負(fù)載密集型系統(tǒng)上使用跟蹤標(biāo)志 1204 和 1222档叔。 使用這些跟蹤標(biāo)志可能會(huì)導(dǎo)致性能問題桌粉。 改用死鎖擴(kuò)展事件(#deadlock_xevent)。
事件探查器（SQL Profiler）死鎖圖形事件
死鎖擴(kuò)展事件
自 SQL Server 2012 (11.x) 起衙四，應(yīng)使用 xml_deadlock_report 擴(kuò)展事件 (xEvent)铃肯，而不使用 SQL 跟蹤或 SQL 探查器中的死鎖圖事件類。
將死鎖減至最少
按同一順序訪問對(duì)象传蹈。
避免事務(wù)中的用戶交互押逼。
保持事務(wù)簡(jiǎn)短并處于一個(gè)批處理中。
使用較低的隔離級(jí)別惦界。
使用基于行版本控制的隔離級(jí)別挑格。
- 將 READ_COMMITTED_SNAPSHOT 數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為 ON，以使讀取提交的事務(wù)可以使用行版本控制沾歪。
- 使用快照隔離漂彤。
使用綁定連接，在活動(dòng)事務(wù)中執(zhí)行sp_getbindtoken返回令牌，在新會(huì)話中運(yùn)行sp_bindsession綁定令牌挫望，將新會(huì)話綁定到同一事務(wù)上立润，以共享同一鎖空間。
常見死鎖類型 - 書簽查找死鎖
會(huì)話52查詢表數(shù)據(jù)使用了非聚集索引(NCL)查找媳板，在該索引上加了共享鎖桑腮，同一時(shí)間，會(huì)話55更新該表在聚集索引(CL)上加了排他鎖蛉幸，因更新的數(shù)據(jù)也在NCL中破讨，故會(huì)話55請(qǐng)求NCL的排他鎖，而會(huì)話52需要的數(shù)據(jù)NCL中沒有奕纫，需回CL中查找提陶，會(huì)請(qǐng)求CL的共享鎖，此時(shí)匹层，產(chǎn)生死鎖搁骑。
常見死鎖類型 - 鎖轉(zhuǎn)換引起死鎖
兩個(gè)進(jìn)程在各自的事務(wù)中都獲取了表中某行(id=7)的共享鎖，且都需要對(duì)該行做修改又固，那么兩個(gè)事務(wù)都要將共享鎖轉(zhuǎn)換成排他鎖仲器，因排他鎖與其他事務(wù)的共享鎖不兼容，發(fā)生鎖等待仰冠，因此每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享鎖乏冀，從而產(chǎn)生死鎖。
