在閱讀本文前最好先看過三篇語句加鎖分析文章：

• 超全面MySQL語句加鎖分析(上篇)(求轉)
• 超全面MySQL語句加鎖分析(中篇)(求轉)
• 超全面MySQL語句加鎖分析(下篇)(求轉)

事前準備

為了故事的順利發展，我們先建一個表，并向表中插入一些記錄，下邊是SQL語句：

CREATE TABLE hero (
number INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number),
KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO hero VALUES
(1, 'l劉備', '蜀'),
(3, 'z諸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孫權', '吳');

現在hero表中的記錄情況就如下所示：

mysql> SELECT * FROM hero;
+--------+------------+---------+
| number | name | country |
+--------+------------+---------+
| 1 | l劉備 | 蜀 |
| 3 | z諸葛亮 | 蜀 |
| 8 | c曹操 | 魏 |
| 15 | x荀彧 | 魏 |
| 20 | s孫權 | 吳 |
+--------+------------+---------+
5 rows in set (0.01 sec)

現象

在小冊答疑群里有一位同學提了一個問題：說是在READ COMMITTED隔離級別下發生了一件百思不得其解的事兒。好的，首先構造環境，將當前會話默認的隔離級別設置成READ COMMITTED：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

事務T1先執行：

# T1中，隔離級別為READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
+--------+---------+---------+
| number | name | country |
+--------+---------+---------+
| 8 | c曹操 | 魏 |
| 15 | x荀彧 | 魏 |
+--------+---------+---------+
2 rows in set (0.01 sec)

country列并不是索引列，所以本條語句執行時肯定是使用掃描聚簇索引的全表掃描方式來執行，EXPLAIN語句也證明了我們的想法：

mysql> EXPLAIN SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | hero | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

我們之前學過MySQL語句的加鎖分析，知道在READ COMMITTED隔離級別下，如果采用全表掃描的方式執行查詢語句時，InnoDB存儲引擎將依次對每條記錄加正經記錄鎖，在server層測試該記錄是否符合WHERE條件，如果不符合則將加在該記錄上的鎖釋放掉。本例中使用FOR UPDATE語句，肯定加的是X型正經記錄鎖。只有兩條記錄符合WHERE條件，所以最終其實只對這兩條符合條件的記錄加了X型正經記錄鎖(就是number列值為8和15的兩條記錄)。當然，我們可以使用SHOW ENGINE INNODB STATUS命令證明我們的分析：

mysql> SHOW ENGINE INNODB STATUS\G
... 省略了很多內容

------------
TRANSACTIONS
------------
Trx id counter 39764
Purge done for trx's n:o < 39763 undo n:o < 0 state: running but idle
History list length 36
Total number of lock structs in row lock hash table 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479653009568, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 281479653012832, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 39763, ACTIVE 468 sec
2 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 19, OS thread handle 123145470611456, query id 586 localhost 127.0.0.1 root
TABLE LOCK table `xiaohaizi`.`hero` trx id 39763 lock mode IX
RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39763 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 6; hex 000000009b4a; asc J;;
2: len 7; hex 80000001d3012a; asc *;;
3: len 7; hex 63e69bb9e6938d; asc c ;;
4: len 3; hex e9ad8f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000009b4a; asc J;;
2: len 7; hex 80000001d30137; asc 7;;
3: len 7; hex 78e88d80e5bda7; asc x ;;
4: len 3; hex e9ad8f; asc ;;
... 省略了很多內容

其中id為39763的事務就是指T1，可以看出它為heap no值為4和5的兩條記錄加了X型正經記錄鎖(lock_mode X locks rec but not gap)。

然后再開啟一個隔離級別也為READ COMMITTED的事務T2，在其中執行：

# T2中，隔離級別為READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE country = '吳' FOR UPDATE;
(進入阻塞狀態)

很顯然，這條語句也會采用全表掃描的方式來執行，會依次去獲取每一條聚簇索引記錄的鎖。不過因為number值為8的記錄已經被T1加了X型正經記錄鎖，T2想得卻得不到，只能眼巴巴的進行阻塞狀態，此時的SHOW ENGINE INNODB STATUS也能證明我們的猜想(只截取了一部分)：

---TRANSACTION 39764, ACTIVE 34 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 20, OS thread handle 123145471168512, query id 590 localhost 127.0.0.1 root Sending data
SELECT * FROM hero WHERE country = '吳' FOR UPDATE
------- TRX HAS BEEN WAITING 34 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39764 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 6; hex 000000009b4a; asc J;;
2: len 7; hex 80000001d3012a; asc *;;
3: len 7; hex 63e69bb9e6938d; asc c ;;
4: len 3; hex e9ad8f; asc ;;

可以看到T2正在等待獲取heap no為4的記錄上的X型正經記錄鎖(lock_mode X locks rec but not gap waiting)。

以上是很正常的阻塞邏輯，我們都可以分析出來，不過如果在T2中執行下邊的UPDATE語句：

# T2中，隔離級別為READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE hero SET name = 'xxx' WHERE country = '吳';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

WTF? 竟然沒有阻塞，就這么隨意地執行成功了？同樣的WHERE條件，同樣的執行計劃，怎么SELECT ... FOR UPDATE和UPDATE語句的加鎖情況不一樣？

原因

哈哈，是的，的確不一樣。其實MySQL支持3種類型的讀語句：

• 普通讀(也稱一致性讀，英文名：Consistent Read)。

  這個就是指普通的SELECT語句，在末尾不加FOR UPDATE或者LOCK IN SHARE MODE的SELECT語句。普通讀的執行方式是生成ReadView直接利用MVCC機制來進行讀取，并不會對記錄進行加鎖。

  小貼士：對于SERIALIZABLE隔離級別來說，如果autocommit系統變量被設置為OFF，那普通讀的語句會轉變為鎖定讀，和在普通的SELECT語句后邊加LOCK IN SHARE MODE達成的效果一樣。

• 鎖定讀(英文名：Locking Read)。

  這個就是事務在讀取記錄之前，需要先獲取該記錄對應的鎖。當然，獲取什么類型的鎖取決于當前事務的隔離級別、語句的執行計劃、查詢條件等因素，具體可參見：超全面MySQL語句加鎖分析(上篇)(求轉)

• 半一致性讀(英文名：Semi-Consistent Read)。

  這是一種夾在普通讀和鎖定讀之間的一種讀取方式。它只在READ COMMITTED隔離級別下(或者在開啟了innodb_locks_unsafe_for_binlog系統變量的情況下)使用UPDATE語句時才會使用。具體的含義就是當UPDATE語句讀取已經被其他事務加了鎖的記錄時，InnoDB會將該記錄的最新提交的版本讀出來，然后判斷該版本是否與UPDATE語句中的WHERE條件相匹配，如果不匹配則不對該記錄加鎖，從而跳到下一條記錄；如果匹配則再次讀取該記錄并對其進行加鎖。這樣子處理只是為了讓UPDATE語句盡量少被別的語句阻塞。

  小貼士：半一致性讀只適用于對聚簇索引記錄加鎖的情況，并不適用于對二級索引記錄加鎖的情況。

很顯然，我們上邊所嘮叨的例子中是因為事務T2執行UPDATE語句時使用了半一致性讀，判斷number列值為8和15這兩條記錄的最新提交版本的country列值均不為UPDATE語句中WHERE條件中的'吳'，所以直接就跳過它們，不對它們加鎖了。

本知識點容易被忽略，各位同學在工作過程中分析的時候別忘記考慮一下Semi-Consistent Read喔，碼字不易，有幫助幫著轉發喔，么么噠～

小青蛙歷史文章(歷史文章，不容錯過)：

關于事務和鎖的一些細節個人所得稅漲了，日子又拮據了一點補數到底是個什么玩意兒？從根兒上理解一下MySQL小冊創作之心路歷程虛擬內存是個啥 | 一分鐘系列MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！

長按關注小青蛙，都是干貨喔

原文鏈接為《MySQL是怎樣運行的：從根兒上理解MySQL》小冊鏈接

總結

以上是生活随笔為你收集整理的mysql 值为0 但却被认为null_MySQL介于普通读和锁定读的加锁方式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。