sql-server.png

加入滬江不久，我就被扔到一個將集團 SQL Sever 的數(shù)據(jù)庫遷移到 MySQL 的項目里，
同時伴隨進(jìn)行的還有 .net 系統(tǒng)遷移到 Java 系統(tǒng)。
在這個過程中我發(fā)現(xiàn)了一個很有趣的現(xiàn)象：歷史遺留的 .net 項目中，
幾乎所有的 SQL 中都會使用一個關(guān)鍵字：nolock。
這讓我很困惑，nolock 的字面意思是對當(dāng)前技術(shù)不使用鎖技術(shù)，為什么要這樣用呢？

我找了一個范例如下：

SELECT [id] 
FROM   [dbo].[foos] WITH(nolock) 
WHERE  aField = 42 
       AND bField = 1 

作為橫向支持工程師，開發(fā)工程師會問我：「數(shù)據(jù)庫即將從 SQL Server
遷移到 MySQL，我們編碼中還需要使用 nolock 么？
MySQL 里面對應(yīng)的寫法是什么？」。
我并沒有 SQL Server 的生產(chǎn)環(huán)境使用經(jīng)驗，一時間無法回答。
于是課后做相關(guān)知識學(xué)習(xí)，這里就是這次學(xué)習(xí)的一點成果。

這個問題將被拆解成三個小問題進(jìn)行回答：

• nolock 是什么？
• 為什么會需要在每個 Query 語句使用 nolock？
• MySQL 的對應(yīng)寫法是什么？

讓我們一個一個來看。

第一個問題：nolock 是什么？

nolock 是 SQL Server 的一個關(guān)鍵字，這類關(guān)鍵字官方將其稱之為 Hints。
Hints 的設(shè)計目的是為了能夠讓 SQL 語句在運行時，動態(tài)修改查詢優(yōu)化器的行為。
在語法上，Hints 以 WITH 開頭。除了 WITH(nolock)，
還有 TABLOCK / INDEX / ROWLOCK 等常見的 Hints。

讓我們仔細(xì)看看 MSDN 文檔上的解釋：

nolock 的作用等同于 READUNCOMMITTED

READUNCOMMITTED 這是一種 RDBMS 隔離級別。
使用 nolock 這個關(guān)鍵詞，可以將當(dāng)前查詢語句隔離級別調(diào)整為 READ UNCOMMITTED。

計算機基礎(chǔ)好的同學(xué)，應(yīng)該對 READUNCOMMITTED 這個關(guān)鍵詞還有印象。
而基礎(chǔ)不扎實的同學(xué)，也許只是覺得這個關(guān)鍵詞眼熟，但是講不清楚這是什么。
如果閱讀這句話完全沒有理解困難，那恭喜你，你可以直接跳到下一節(jié)了。
其他朋友就跟隨我繼續(xù)探索一下 RDMBS 的世界，復(fù)習(xí)一下隔離級別相關(guān)的知識。

隔離級別

SQL 92 定義了四個隔離級別
（Isolation (database systems) - Wikipedia），
其隔離程度由高到低是：

• 可序列化（Serializable）
• 可重復(fù)讀（Repeatable reads）
• 提交讀（Read committed）
• 未提交讀（Read uncommitted）

單單將這幾個技術(shù)名詞簡單地羅列出來并沒有什么意義，還有這幾個問題需要搞清楚：

• 隔離級別解決什么問題？
• 為什么存在多種隔離級別？
• 我們所謂的隔離級別從高到低，是什么含義，如何逐層降低的？

首先是「隔離級別解決什么問題？」，
用通俗的語言描述就是：加一個針對數(shù)據(jù)資源的鎖，從而保證數(shù)據(jù)操作過程中的一致性。

這是最簡單的實現(xiàn)方式，過于粗暴的隔離性將大幅降低性能，
多種隔離級別就是是為了取得兩者的平衡。

接下來我們來回答第二個問題「為什么存在多種粒度的隔離級別？」
這其實是一個需求和性能逐步平衡的過程，

我們逐層遞進(jìn)，將隔離級別由低到高逐層面臨進(jìn)行分析。

Read Uncommitted

Read Uncommitted 這個隔離級別是最低粒度的隔離級別，
如同它的名字一般，它允許在操作過程中不會鎖，從而讓當(dāng)前事務(wù)讀取到其他事務(wù)的數(shù)據(jù)。

read-uncommitted.png

如上圖所示，在 Transaction 2 查詢時候，Transaction 1 未提交的數(shù)據(jù)就已經(jīng)對外暴露。
如果 Transaction 1 最后 Rollback 了，那么 Transaction 讀取的數(shù)據(jù)就是錯誤的。

「讀到了其他事務(wù)修改了但是未提交的數(shù)據(jù)」即是臟讀。

Read Committed

想要避免臟讀，最簡單的方式就是在事務(wù)更新操作上加一把寫鎖，
其他事務(wù)需要讀取數(shù)據(jù)時候，需要等待這把寫鎖釋放。

read-committed-1.png

如上圖所示，Transaction 1 在寫操作時候，對數(shù)據(jù) A 加了寫鎖，
那么 Transaction 2 想要讀取 A，就必須等待這把鎖釋放。
這樣就避免當(dāng)前事務(wù)讀取其他事務(wù)的未提交數(shù)據(jù)。

但是除了臟讀，一致性的要求還需要「可重復(fù)讀」，即
「在一個事務(wù)內(nèi)，多次讀取的特定數(shù)據(jù)都必須是一致的
（即便在這過程中該數(shù)據(jù)被其他事務(wù)修改）」。

read-committed-2.png

上圖就是沒能保證「可重復(fù)度」，Transaction 2 第一次讀取到了數(shù)據(jù) A，
然后 Transaction 1 對數(shù)據(jù) A 更新到 A'，那么當(dāng) Tranction 2 再次讀取 A 時候，
它本來期望讀到 A，但是卻讀到了 A'，這和它的預(yù)期不相符了。
解決這個問題，就需要提升隔離級別到「Repeatable Read」。

Repeatable Read

這個名字非常容易理解，即保障在一個事務(wù)內(nèi)重復(fù)讀取時，
始終能夠讀取到相同的內(nèi)容。來看圖：

repeatable-read.png

如上所示，當(dāng) Transation 2 讀取 A 時候，會同時加上一把 Read Lock，
這把鎖會阻止 Transaction 1 將 A 更新為 A'，Transaction 1 要么選擇等待，
要么就選擇結(jié)束。

當(dāng)我們將隔離級別升到這里是，似乎已經(jīng)完美無缺了。
不管是寫入還是讀取，我們都可以保證數(shù)據(jù)的一致性不被破壞。
但是其實還有漏洞：新增數(shù)據(jù)的一致性！

上述的三個隔離級別，都是對特定的一行數(shù)據(jù)進(jìn)行加鎖，
那假如將要更新的數(shù)據(jù)還沒有寫入數(shù)據(jù)庫，如何進(jìn)行加鎖呢？
比如自增表的新鍵，或者現(xiàn)有數(shù)據(jù)內(nèi)的空缺 Key？

repeatable-read-2.png

如圖所示，在上述操作中，Transaction 2 查詢了一個范圍 Range 之后，Transaction 1
在這個范圍內(nèi)插入了一條新的數(shù)據(jù)。此時 Transaction 2 再次進(jìn)行范圍查詢時候，
會發(fā)現(xiàn)查詢到的 Range 和上次已經(jīng)不一樣了，多了一個 newA。

這就是最高隔離級別才能解決的「幻影讀」：
當(dāng)兩個完全相同的查詢語句執(zhí)行得到不同的結(jié)果集，
這常常在范圍查詢中出現(xiàn)。

Serializable

從字面意思看，該隔離級別需要將被操作的數(shù)據(jù)加鎖加一把鎖。
任何讀寫操作都需要先獲得這把鎖才能進(jìn)行。如果操作中帶 WHERE 條件，
還需要將 WHERE 條件相關(guān)的范圍全部加鎖。

serializable.png

如圖所示，在 Transaction 2 操作過程中，會對 Range 進(jìn)行加鎖，
此時其他事務(wù)無法操作其中的數(shù)據(jù)，只能等待或者放棄。

DB 的默認(rèn)隔離級別

現(xiàn)在我們已經(jīng)理解了隔離級別，那么「SQL Server 默認(rèn)使用的隔離級別是什么呢？」
根據(jù) Customizing Transaction Isolation Level
這個文檔描述，SQL Server 默認(rèn)隔離級別是 READ COMMITTED。

MySQL InnoDB 的默認(rèn)隔離級別可以在 MySQL :: MySQL 5.7 Reference Manual :: 14.5.2.1 Transaction Isolation Levels
查詢到，是 Read-Repeatable。

隔離級別并沒有最好之說，越高隔離級別會導(dǎo)致性能降低。
隔離級別的設(shè)定需要考慮業(yè)務(wù)場景。

第二個問題：為什么要使用 nolock？

我們已經(jīng)知道 nolock 的作用是動態(tài)調(diào)整隔離級別。
那為什么在 SQL Server 的 Query 操作中，需要啟用 nolock 呢？
我問了幾個工程師，他們都語焉不詳，或者是很泛泛地說：禁用讀寫鎖，可以提升查詢性能。

此時我產(chǎn)生了困惑：「那么此時的數(shù)據(jù)一致性就不需要考慮了么？
我們的數(shù)據(jù)庫，已經(jīng)到了需要禁用鎖的程度來進(jìn)行優(yōu)化了么？」
我于是自己去探索，想知道為何廣泛使用 nolock 會成為一個「最佳實踐」？

由于時代久遠(yuǎn)，我只能追述到一些相關(guān)信息，比如
Top 10 SQL Server Integration Services Best Practices | SQL Server Customer Advisory Team
中提到 「Use the NOLOCK or TABLOCK hints to remove locking overhead.」
但這個是針對于 SSIS 查詢器，并不是針對業(yè)務(wù)內(nèi)部使用。
反而能找到一大堆的文檔，在反對使用 nolock 這個關(guān)鍵字。

繼續(xù)追查下去，還從蛛絲馬跡中尋找到一個使用 nolock 的理由，
SQL Server 默認(rèn)是 Read Committed，
更新操作會產(chǎn)生排它鎖，會 block 這個資源的查詢操作，
已插入但未提交的數(shù)據(jù)主鍵也會產(chǎn)生一個共享鎖，
而此時則會 block 這張表的全表查詢和 Insert 操作。
為了避免 Insert 被 Block，就會推薦使用 nolock。

為了驗證這是原因，我做一些 nolock 測試。

nolock 測試

檢查當(dāng)前 SQL Server 隔離級別，確認(rèn)隔離級別是默認(rèn)的 Read Committed：

SELECT CASE transaction_isolation_level
       WHEN 0
         THEN 'Unspecified'
       WHEN 1
         THEN 'ReadUncommitted'
       WHEN 2
         THEN 'ReadCommitted'
       WHEN 3
         THEN 'Repeatable'
       WHEN 4
         THEN 'Serializable'
       WHEN 5
         THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

-- ReadCommitted

創(chuàng)建表，初始化數(shù)據(jù)：

CREATE TABLE foos (
  id    BIGINT    NOT NULL,
  value NCHAR(10) NULL,
  CONSTRAINT pk PRIMARY KEY clustered (id)
);
INSERT INTO foos (id, value) VALUES (1, '1'), (2, '2');

在 Transaction 1 中發(fā)起 Update 操作（INSERT / DELETE 同理），但是并不做 Commit 提交：

BEGIN TRANSACTION;
INSERT INTO foos (id, value) VALUES (3, '3');

開啟一個新的 Session，發(fā)起全表查詢和新增 PK 查詢操作：

SELECT * FROM foos;
SELECT * FROM foos WHERE id = 4;

不出所料，此時查詢果然會被 Block 住。

MVCC

并發(fā)控制的手段有這些：封鎖、時間戳、樂觀并發(fā)控制、悲觀并發(fā)控制。
SQL Server 在 2015 后，引入了 MVCC（多版本控制）。
如果最終數(shù)據(jù)是一致，會允許數(shù)據(jù)寫入，否則其他事務(wù)會被阻止寫入。
那么 MVCC 引入是否可以解決 Insert 數(shù)據(jù)的鎖問題？
同樣，我做了以下測試：

查詢 SQL Server 使用啟用 MVCC ALLOW_SNAPSHOT_ISOLATION：

SELECT name, snapshot_isolation_state FROM sys.databases;

使用 T-SQL 啟用測試表的 SNAPSHOT_ISOLATION：

ALTER DATABASE HJ_Test3D SET ALLOW_SNAPSHOT_ISOLATION ON;

接著重復(fù)上面里面的 Insert 試驗，依然被 Block 住。
看來 MVCC 并不能解決 Insert 鎖的問題。

SQL Server 2005 之后還需要使用 nolock 么？

從官方文檔和上文測試可以看到，在 Insert 時候，由于排它鎖的存在，
會導(dǎo)致 SELECT ALL 以及 SELECT 新插入數(shù)據(jù)的相關(guān)信息被鎖住。
在這兩種情景下面是需要使用 nolock 的。

除此之外，有這么幾類場景可以使用 nolock：

• 在 SSIS 查詢器中進(jìn)行數(shù)據(jù)分析，不需要精準(zhǔn)數(shù)據(jù)
• 歷史數(shù)據(jù)進(jìn)行查詢，沒有數(shù)據(jù)更新操作，也不會產(chǎn)生臟數(shù)據(jù)

我們需要思考一下，性能和數(shù)據(jù)一致性上的權(quán)衡上，
我們是否愿意放棄數(shù)據(jù)一致性而為了提高一絲絲性能？
以及我們有多少場景，會頻繁使用 SELECT ALL 操作而沒有查詢條件？

微軟官方在 2015 的特性列表里面，明確地指出 nolock 特性未來會在某個版本被廢除：

Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement.

而改為推薦：

Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.

事實上，我聽過不少團隊會禁止在生產(chǎn)環(huán)境使用不帶 WHERE 條件的 SQL。
那在這種模式下，產(chǎn)生相關(guān)的問題的幾率也就更小了。
如果有很高的并發(fā)需求，那需要考慮一下是否需要其他優(yōu)化策略：比如使用主從分離、
Snapshot 導(dǎo)出、流式分析等技術(shù)。

第三個問題：MySQL 的對應(yīng)寫法是什么？

終于輪到 MySQL 的討論了。MySQL，InnoDB 天生支持 MVCC，
并且支持 innodb_autoinc_lock_mode AUTO_INCREMENT Handling in InnoDB。
這樣可以避免 Insert 操作鎖住全局 Select 操作。
只有在同時 Insert 時候，才會被 Block 住。

innodb_autoinc_lock_mode 支持幾種模式：

• innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
  • 涉及auto-increment列的插入語句加的表級AUTO-INC鎖，只有插入執(zhí)行結(jié)束后才會釋放鎖
• innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
  • 可以事先確定插入行數(shù)的語句，分配連續(xù)的確定的 auto-increment 值
  • 對于插入行數(shù)不確定的插入語句，仍加表鎖
  • 這種模式下，事務(wù)回滾，auto-increment 值不會回滾，換句話說，自增列內(nèi)容會不連續(xù)
• innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
  • 同一時刻多條 SQL 語句產(chǎn)生交錯的 auto-increment 值

這里也做了相應(yīng)的測試。首先檢查數(shù)據(jù)庫隔離級別和 innodb_autoinc_lock_mode 模式：

SELECT @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
SHOW variables LIKE 'innodb_autoinc_lock_mode';

檢查后發(fā)現(xiàn)都是 Repeatable Read，innodb_autoinc_lock_mode 模式是 1。
然后創(chuàng)建測試表：

CREATE TABLE `foos` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

在 Transaction 1 中 Insert 數(shù)據(jù)：

START TRANSACTION;
INSERT INTO foos (name) VALUES ("a");

在 Transaction 2 中 Select 數(shù)據(jù)，可以正常查詢：

SELECT * FROM   foos;

在 Transaction 2 中 Insert 數(shù)據(jù)，會被 Block 住：

START TRANSACTION;
INSERT INTO foos (name) VALUES ("a");

這個測試可以證明 MySQL 可以在 innodb_autoinc_lock_mode=1 下，
Insert 同時 Query 不會被 Block，
但是在另外一個事務(wù)中 Insert 會被 Block。
結(jié)論是，由于 innodb_autoinc_lock_mode 的存在，MySQL 中可以不需要使用 nolock
關(guān)鍵詞進(jìn)行查詢。

回顧一下

本文著重去回答這么幾個問題：

• 為什么要用 noloc？
• 為什么要改變隔離級別？
• 為什么 MySQL 不需要做類似的事情？

雖然只湊足了三個 「為什么」 的排比，
但是聰明的讀者仍然會發(fā)現(xiàn)，我是使用了著名的
五個為什么
方法思考問題。
通過使用這個方法，我們最后不但打破了老舊的最佳實踐，還了解了本質(zhì)原理，
并找到了新的最佳實踐。

希望讀者朋友在遇到困難時候，多問幾個為什么，多抱著打破砂鍋問到底的精神，
這樣才能讓每個困難成為我們成長的墊腳石。

相關(guān)資料

• 事務(wù)隔離 - 維基百科，自由的百科全書
• Table Hints (Transact-SQL) | Microsoft Docs
• Snapshot Isolation in SQL Server | Microsoft Docs
• sys.databases (Transact-SQL) | Microsoft Docs
• MySQL :: MySQL 5.7 Reference Manual :: 15.3 InnoDB Multi-Versioning

原文鏈接: 一個關(guān)于 nolock 的故事 - Log4D
歡迎關(guān)注我的微信公眾號：窺豹