【MYSQL】一個關於Innodb儲存引擎表的加鎖問題

ai3707發表於2016-05-06
MySql儲存引擎
今天有童鞋問了一個關於innodb加鎖處理的疑問，於是根據案例做出了分析，見下文：

版本Oracle mysql 5.5 (GPL)事務隔離級別 REPEATABLE-READ，現在遇到一個很奇怪的問題：
現將問題簡化：
表是這樣的表：
show create table a\G             
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

select * from a;
+----+
| id | 
+----+
|  1 |
+----+
1 rows in set (0.00 sec)

會話1：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中沒有，即不存在這條資料）
Empty set (0.00 sec)
完成執行 
再操作會話2：

會話2：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中沒有，即不存在這條資料）
此時沒有hang住，同樣返回結果：
Empty set (0.00 sec)
這個是為什麼、？？？

背景是因為業務需求就是先做判斷是否存在id=2的資料，若無，則insert一條進去，若兩個以上的會話同時這樣，會導致deadlock。將上述步驟中的where id = 2 修改為 where id = 1 （id=1有這條資料）同樣步驟跑一邊，此時會話1返回 Empty set (0.00 sec) 而會話2 正常hang住。這應該才是正常的。。。
為什麼id=2（原表中沒有的資料）的情況，沒有被鎖住呢？？？

後面又做了測試：
將a表的primary key刪除，即a表沒有主鍵，id列無約束時，且無論是否能夠匹配到資料，均可被正常鎖住。只有在主鍵匹配結果為empty時，無法被加上for update鎖。


分析：
首先，按照前面的SQL寫法：
會話1：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中沒有，即不存在這條資料）
Empty set (0.00 sec)
完成執行 
再操作會話2：
會話2：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中沒有，即不存在這條資料）

在這種情況下，無論id是不是主鍵、常規索引、或者無索引，都不會加鎖；因為加鎖至少是要針對於每行的資源，兩個會話中都根本沒有id=2產生，怎麼會鎖住呢？
而後樓主又說了業務需求是查詢是否有2，沒有則插入，那麼這兩個會話的寫法應該是第一個查詢結束後再插入id=2的記錄吧？？？ 所以不知道最上面SQL是不是漏掉了插入id=2呢？？
現在就當做第一個事務查詢後插入id=2的記錄，來分析下整個過程：
id為主鍵的情況下，手動開啟一個事務，執行：select * from a where id=2 for update;  這條SQL是要獲取id=2的一個當前讀，於是會走主鍵索引掃描，如果掃描到資料會在該記錄加上一個排它鎖X，因為是主鍵所以只會有一條id=2的值，所以找到資料的話只會涉及一條記錄的排它鎖；但是很不巧，主鍵中沒有id=2的值，所以此時資料庫未加任何鎖。然後這個事務又執行了：insert into a values (2)，這時表中新產生了一條資料id=2，因為id為主鍵，不允許重複，不能讓其他事務也插入id=2這一行資料，所以id=2會加一個X鎖。
那麼再看第二個會話，執行：select * from a where id=2 for update時，這時就一定會被鎖住了，因為select+forupdate時，會進行當前讀，也就是說要獲取到這一行的最新版本並加上X鎖，但是id=2已經在第一個會話中加入x鎖了，所以出現鎖衝突，第二個會話發生阻塞，等待會話1釋放X鎖
而在id不是主鍵的情況下，也無索引，第一個會話執行：select * from a where id=2 for update;在可重複讀的隔離級別下，這條SQL會將表中的所有記錄以及縫隙全鎖住，記錄加X鎖，縫隙加GAP鎖，也就是說insert,update,delete都執行不了，而第二個會話執行：select * from a where id=2 for update時，同理for update需要進行當前讀，加X鎖，而第一個會話已經將表鎖死了，所以會話2也產生阻塞

總結：在以上場景下，id是主鍵或不是主鍵，會話2執行forupdate時都會阻塞，但是阻塞原因不同；id為主鍵時，第一個會話的insert導致會話2的forupdate阻塞，id不是主鍵時，會話1的for update導致會話2的forupdate阻塞

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29018063/viewspace-2095021/，如需轉載，請註明出處，否則將追究法律責任。

相關文章