前言

使用insert into on duplicate key update語(yǔ)句進(jìn)行插入去重，但是在測(cè)試過(guò)程中發(fā)現(xiàn)了死鎖現(xiàn)象:

```

ERROR1213(40001):Deadlockfound when trying togetlock;tryrestarting transaction

```

表鎖和行鎖

首先來(lái)了解一下表鎖和行鎖：表鎖是指對(duì)一整張表加鎖，一般是 DDL 處理時(shí)使用；而行鎖則是鎖定某一行或者某幾行赴捞，或者行與行之間的間隙。

表鎖由 MySQL Server 實(shí)現(xiàn)郁稍，行鎖則是存儲(chǔ)引擎實(shí)現(xiàn)赦政，不同的引擎實(shí)現(xiàn)的不同。在 MySQL 的常用引擎中 InnoDB 支持行鎖艺晴，而 MyISAM 則只能使用 MySQL Server 提供的表鎖昼钻。

表鎖

表鎖由 MySQL Server 實(shí)現(xiàn)，一般在執(zhí)行 DDL 語(yǔ)句時(shí)會(huì)對(duì)整個(gè)表進(jìn)行加鎖封寞，比如說(shuō) ALTER TABLE 等操作。在執(zhí)行 SQL 語(yǔ)句時(shí)仅财，也可以明確指定對(duì)某個(gè)表進(jìn)行加鎖狈究。

```

mysql>locktable userread(write);

# 分為讀鎖和寫鎖QueryOK,0rows affected(0.00sec)

mysql>select*fromuserwhereid=100;

# 成功mysql>select*fromrolewhereid=100;

# 失敗，未提前獲取該 role的讀表鎖

mysql>update usersetname='Tom'whereid=100;

# 失敗盏求，未提前獲得user的寫表鎖

mysql> unlock tables;?

# 顯示釋放表鎖Query OK, 0 rows affected (0.00 sec)

```

表鎖使用的是一次性鎖技術(shù)抖锥，也就是說(shuō)，在會(huì)話開始的地方使用 lock 命令將后續(xù)需要用到的表都加上鎖碎罚，在表釋放前磅废，只能訪問(wèn)這些加鎖的表，不能訪問(wèn)其他表荆烈，直到最后通過(guò) unlock tables 釋放所有表鎖拯勉。

除了使用 unlock tables 顯示釋放鎖之外，會(huì)話持有其他表鎖時(shí)執(zhí)行l(wèi)ock table 語(yǔ)句會(huì)釋放會(huì)話之前持有的鎖憔购；會(huì)話持有其他表鎖時(shí)執(zhí)行 start transaction 或者 begin 開啟事務(wù)時(shí)宫峦，也會(huì)釋放之前持有的鎖。

行鎖

不同存儲(chǔ)引擎的行鎖實(shí)現(xiàn)不同玫鸟，后續(xù)沒(méi)有特別說(shuō)明导绷，則行鎖特指 InnoDB 實(shí)現(xiàn)的行鎖。

在了解 InnoDB 的加鎖原理前屎飘，需要對(duì)其存儲(chǔ)結(jié)構(gòu)有一定的了解妥曲。InnoDB 是聚簇索引，也就是 B+樹的葉節(jié)點(diǎn)既存儲(chǔ)了主鍵索引也存儲(chǔ)了數(shù)據(jù)行钦购。而 InnoDB 的二級(jí)索引的葉節(jié)點(diǎn)存儲(chǔ)的則是主鍵值檐盟，所以通過(guò)二級(jí)索引查詢數(shù)據(jù)時(shí)，還需要拿對(duì)應(yīng)的主鍵去聚簇索引中再次進(jìn)行查詢肮雨。關(guān)于 InnoDB 和 MyISAM 的索引的詳細(xì)知識(shí)可以閱讀《Mysql探索(一):B+Tree索引》一文遵堵。

下面以兩條 SQL 的執(zhí)行為例，講解一下 InnoDB 對(duì)于單行數(shù)據(jù)的加鎖原理。

```

update user set age = 10 where id = 49;

update user set age = 10 where name = 'Tom';

```

第一條 SQL 使用主鍵索引來(lái)查詢陌宿，則只需要在 id = 49 這個(gè)主鍵索引上加上寫鎖锡足；第二條 SQL 則使用二級(jí)索引來(lái)查詢，則首先在 name = Tom 這個(gè)索引上加寫鎖壳坪，然后由于使用 InnoDB 二級(jí)索引還需再次根據(jù)主鍵索引查詢舶得，所以還需要在 id = 49 這個(gè)主鍵索引上加寫鎖，如上圖所示爽蝴。

也就是說(shuō)使用主鍵索引需要加一把鎖沐批，使用二級(jí)索引需要在二級(jí)索引和主鍵索引上各加一把鎖。

根據(jù)索引對(duì)單行數(shù)據(jù)進(jìn)行更新的加鎖原理了解了蝎亚，那如果更新操作涉及多個(gè)行呢九孩，比如下面 SQL 的執(zhí)行場(chǎng)景。

```

update user set age = 10 where id > 49;

```

上述 SQL 的執(zhí)行過(guò)程如下圖所示发框。MySQL Server 會(huì)根據(jù) WHERE 條件讀取第一條滿足條件的記錄躺彬，然后 InnoDB 引擎會(huì)將第一條記錄返回并加鎖，接著 MySQL Server 發(fā)起更新改行記錄的 UPDATE 請(qǐng)求梅惯，更新這條記錄宪拥。一條記錄操作完成，再讀取下一條記錄铣减，直至沒(méi)有匹配的記錄為止她君。

這種場(chǎng)景下的鎖的釋放較為復(fù)雜，有多種的優(yōu)化方式葫哗，我對(duì)這塊暫時(shí)還沒(méi)有了解缔刹，還請(qǐng)知道的小伙伴在下方留言解釋。

下面主要依次介紹 InnoDB 中鎖的模式和類型魄梯，鎖的類型是指鎖的粒度或者鎖具體加在什么地方桨螺；而鎖模式描述的是鎖的兼容性，也就是加的是什么鎖酿秸，比如寫鎖或者讀鎖灭翔。

行鎖的模式

行鎖的模式有：讀意向鎖，寫意向鎖辣苏，讀鎖肝箱，寫鎖和自增鎖(auto_inc)，下面我們依次來(lái)看稀蟋。

讀寫鎖

讀鎖：又稱共享鎖（Share locks煌张，簡(jiǎn)稱 S 鎖），加了讀鎖的記錄退客，所有的事務(wù)都可以讀取骏融，但是不能修改链嘀，并且可同時(shí)有多個(gè)事務(wù)對(duì)記錄加讀鎖。

寫鎖：又稱排他鎖（Exclusive locks档玻，簡(jiǎn)稱 X 鎖）怀泊，或獨(dú)占鎖，對(duì)記錄加了排他鎖之后误趴，只有擁有該鎖的事務(wù)可以讀取和修改霹琼，其他事務(wù)都不可以讀取和修改，并且同一時(shí)間只能有一個(gè)事務(wù)加寫鎖凉当。

讀寫意向鎖

由于表鎖和行鎖雖然鎖定范圍不同枣申，但是會(huì)相互沖突。所以當(dāng)你要加表鎖時(shí)看杭，勢(shì)必要先遍歷該表的所有記錄忠藤，判斷是否加有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式楼雹，MySQL 引入了意向鎖熄驼，來(lái)檢測(cè)表鎖和行鎖的沖突。

意向鎖也是表級(jí)鎖烘豹，也可分為讀意向鎖（IS 鎖）和寫意向鎖（IX 鎖）。當(dāng)事務(wù)要在記錄上加上讀鎖或?qū)戞i時(shí)诺祸，要首先在表上加上意向鎖携悯。這樣判斷表中是否有記錄加鎖就很簡(jiǎn)單了，只要看下表上是否有意向鎖就行了筷笨。

意向鎖之間是不會(huì)產(chǎn)生沖突的憔鬼，也不和 AUTO_INC 表鎖沖突，它只會(huì)阻塞表級(jí)讀鎖或表級(jí)寫鎖胃夏，另外轴或，意向鎖也不會(huì)和行鎖沖突，行鎖只會(huì)和行鎖沖突仰禀。

自增鎖

AUTO_INC 鎖：又叫自增鎖（一般簡(jiǎn)寫成 AI 鎖）照雁，是一種表鎖，當(dāng)表中有自增列（AUTO_INCREMENT）時(shí)出現(xiàn)答恶。當(dāng)插入表中有自增列時(shí)饺蚊，數(shù)據(jù)庫(kù)需要自動(dòng)生成自增值，它會(huì)先為該表加 AUTO_INC 表鎖悬嗓，阻塞其他事務(wù)的插入操作污呼，這樣保證生成的自增值肯定是唯一的。

AUTO_INC 鎖具有如下特點(diǎn)：

AUTO_INC 鎖互不兼容包竹，也就是說(shuō)同一張表同時(shí)只允許有一個(gè)自增鎖燕酷；

自增值一旦分配了就會(huì) +1籍凝，如果事務(wù)回滾，自增值也不會(huì)減回去苗缩，所以自增值可能會(huì)出現(xiàn)中斷的情況饵蒂。

顯然，AUTO_INC 表鎖會(huì)導(dǎo)致并發(fā)插入的效率降低挤渐，為了提高插入的并發(fā)性苹享，MySQL 從 5.1.22 版本開始，引入了一種可選的輕量級(jí)鎖（mutex）機(jī)制來(lái)代替 AUTO_INC 鎖浴麻，可以通過(guò)參數(shù) innodb_autoinc_lock_mode 來(lái)靈活控制分配自增值時(shí)的并發(fā)策略得问。具體可以參考 MySQL 的 AUTO_INCREMENT Handling in InnoDB 一文，鏈接在文末软免。

不同模式鎖的兼容矩陣

下面是各個(gè)表鎖之間的兼容矩陣宫纬。

總結(jié)起來(lái)有下面幾點(diǎn)：

意向鎖之間互不沖突；

S 鎖只和 S/IS 鎖兼容膏萧，和其他鎖都沖突漓骚；

X 鎖和其他所有鎖都沖突；

AI 鎖只和意向鎖兼容榛泛；

行鎖的類型

根據(jù)鎖的粒度可以把鎖細(xì)分為表鎖和行鎖蝌蹂，行鎖根據(jù)場(chǎng)景的不同又可以進(jìn)一步細(xì)分，依次為 Next-Key Lock曹锨，Gap Lock 間隙鎖孤个，Record Lock 記錄鎖和插入意向 GAP 鎖。

不同的鎖鎖定的位置是不同的沛简，比如說(shuō)記錄鎖只鎖住對(duì)應(yīng)的記錄齐鲤，而間隙鎖鎖住記錄和記錄之間的間隔，Next-Key Lock 則所屬記錄和記錄之前的間隙椒楣。不同類型鎖的鎖定范圍大致如下圖所示给郊。

下面我們來(lái)依次了解一下不同的類型的鎖。

記錄鎖

記錄鎖是最簡(jiǎn)單的行鎖捧灰，并沒(méi)有什么好說(shuō)的淆九。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖，只鎖住 id = 49 或者 name = ‘Tom’ 這一條記錄凤壁。

當(dāng) SQL 語(yǔ)句無(wú)法使用索引時(shí)吩屹，會(huì)進(jìn)行全表掃描，這個(gè)時(shí)候 MySQL 會(huì)給整張表的所有數(shù)據(jù)行加記錄鎖拧抖，再由 MySQL Server 層進(jìn)行過(guò)濾煤搜。但是，在 MySQL Server 層進(jìn)行過(guò)濾的時(shí)候唧席，如果發(fā)現(xiàn)不滿足 WHERE 條件擦盾，會(huì)釋放對(duì)應(yīng)記錄的鎖嘲驾。這樣做，保證了最后只會(huì)持有滿足條件記錄上的鎖迹卢，但是每條記錄的加鎖操作還是不能省略的辽故。

所以更新操作必須要根據(jù)索引進(jìn)行操作，沒(méi)有索引時(shí)腐碱，不僅會(huì)消耗大量的鎖資源誊垢，增加數(shù)據(jù)庫(kù)的開銷，還會(huì)極大的降低了數(shù)據(jù)庫(kù)的并發(fā)性能症见。

間隙鎖

還是最開始更新用戶年齡的例子喂走，如果 id = 49 這條記錄不存在，這個(gè) SQL 語(yǔ)句還會(huì)加鎖嗎谋作？答案是可能有芋肠，這取決于數(shù)據(jù)庫(kù)的隔離級(jí)別。這種情況下遵蚜，在 RC 隔離級(jí)別不會(huì)加任何鎖帖池，在 RR 隔離級(jí)別會(huì)在 id = 49 前后兩個(gè)索引之間加上間隙鎖。

間隙鎖是一種加在兩個(gè)索引之間的鎖吭净，或者加在第一個(gè)索引之前睡汹，或最后一個(gè)索引之后的間隙。這個(gè)間隙可以跨一個(gè)索引記錄寂殉，多個(gè)索引記錄帮孔，甚至是空的。使用間隙鎖可以防止其他事務(wù)在這個(gè)范圍內(nèi)插入或修改記錄不撑，保證兩次讀取這個(gè)范圍內(nèi)的記錄不會(huì)變，從而不會(huì)出現(xiàn)幻讀現(xiàn)象晤斩。

值得注意的是焕檬，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入澳泵，所以加間隙 S 鎖和加間隙 X 鎖沒(méi)有任何區(qū)別实愚。

Next-Key 鎖

Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖兔辅。假設(shè)一個(gè)索引包含15腊敲、18、20 维苔，30碰辅，49，50 這幾個(gè)值介时，可能的 Next-key 鎖如下：

(-∞, 15]没宾，(15, 18]凌彬，(18, 20]，(20, 30]循衰，(30, 49]铲敛，(49, 50]，(50, +∞)

通常我們都用這種左開右閉區(qū)間來(lái)表示 Next-key 鎖会钝，其中伐蒋，圓括號(hào)表示不包含該記錄，方括號(hào)表示包含該記錄迁酸。前面四個(gè)都是 Next-key 鎖先鱼，最后一個(gè)為間隙鎖。

和間隙鎖一樣胁出，在 RC 隔離級(jí)別下沒(méi)有 Next-key 鎖型型，只有 RR 隔離級(jí)別才有。還是之前的例子全蝶，如果 id 不是主鍵闹蒜，而是二級(jí)索引，且不是唯一索引抑淫，那么這個(gè) SQL 在 RR 隔離級(jí)別下就會(huì)加如下的 Next-key 鎖 (30, 49](49, 50)

此時(shí)如果插入一條 id = 31 的記錄將會(huì)阻塞住绷落。之所以要把 id = 49 前后的間隙都鎖住，仍然是為了解決幻讀問(wèn)題始苇，因?yàn)?id 是非唯一索引砌烁，所以 id = 49 可能會(huì)有多條記錄，為了防止再插入一條 id = 49 的記錄催式。

插入意向鎖

插入意向鎖是一種特殊的間隙鎖（簡(jiǎn)寫成 II GAP）表示插入的意向函喉，只有在 INSERT 的時(shí)候才會(huì)有這個(gè)鎖。注意荣月，這個(gè)鎖雖然也叫意向鎖管呵，但是和上面介紹的表級(jí)意向鎖是兩個(gè)完全不同的概念，不要搞混了哺窄。

插入意向鎖和插入意向鎖之間互不沖突捐下，所以可以在同一個(gè)間隙中有多個(gè)事務(wù)同時(shí)插入不同索引的記錄。譬如在上面的例子中萌业，id = 30 和 id = 49 之間如果有兩個(gè)事務(wù)要同時(shí)分別插入 id = 32 和 id = 33 是沒(méi)問(wèn)題的坷襟，雖然兩個(gè)事務(wù)都會(huì)在 id = 30 和 id = 50 之間加上插入意向鎖，但是不會(huì)沖突生年。

插入意向鎖只會(huì)和間隙鎖或 Next-key 鎖沖突婴程，正如上面所說(shuō)，間隙鎖唯一的作用就是防止其他事務(wù)插入記錄造成幻讀抱婉，正是由于在執(zhí)行 INSERT 語(yǔ)句時(shí)需要加插入意向鎖排抬，而插入意向鎖和間隙鎖沖突懂从，從而阻止了插入操作的執(zhí)行。

不同類型鎖的兼容矩陣

不同類型鎖的兼容下如下圖所示蹲蒲。

其中番甩，第一行表示已有的鎖，第一列表示要加的鎖届搁。插入意向鎖較為特殊缘薛，所以我們先對(duì)插入意向鎖做個(gè)總結(jié)，如下：

插入意向鎖不影響其他事務(wù)加其他任何鎖卡睦。也就是說(shuō)宴胧，一個(gè)事務(wù)已經(jīng)獲取了插入意向鎖，對(duì)其他事務(wù)是沒(méi)有任何影響的表锻；

插入意向鎖與間隙鎖和 Next-key 鎖沖突恕齐。也就是說(shuō)，一個(gè)事務(wù)想要獲取插入意向鎖瞬逊，如果有其他事務(wù)已經(jīng)加了間隙鎖或 Next-key 鎖显歧，則會(huì)阻塞。

其他類型的鎖的規(guī)則較為簡(jiǎn)單：

間隙鎖不和其他鎖（不包括插入意向鎖）沖突确镊；

記錄鎖和記錄鎖沖突士骤，Next-key 鎖和 Next-key 鎖沖突，記錄鎖和 Next-key 鎖沖突蕾域；

死鎖

在解決Mysql 死鎖的問(wèn)題之前拷肌，還是先來(lái)了解一下什么是死鎖。

死鎖是指兩個(gè)或兩個(gè)以上的進(jìn)程在執(zhí)行過(guò)程中旨巷，因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象巨缘，若無(wú)外力作用，它們都將無(wú)法推進(jìn)下去采呐，此時(shí)稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖带猴，這些永遠(yuǎn)在互相等的進(jìn)程稱為死鎖進(jìn)程。

死鎖的表現(xiàn)

死鎖的具體表現(xiàn)有兩種：

Mysql 增改語(yǔ)句無(wú)法正常生效懈万，使用Mysql GUI 工具編輯字段的值時(shí)，會(huì)出現(xiàn)異常靶病。 如何避免死鎖

阻止死鎖的途徑就是避免滿足死鎖條件的情況發(fā)生会通，為此我們?cè)陂_發(fā)的過(guò)程中需要遵循如下原則：

1、盡量避免并發(fā)的執(zhí)行涉及到修改數(shù)據(jù)的語(yǔ)句娄周。

2涕侈、要求每一個(gè)事務(wù)一次就將所有要使用到的數(shù)據(jù)全部加鎖，否則就不允許執(zhí)行煤辨。

3裳涛、預(yù)先規(guī)定一個(gè)加鎖順序木张，所有的事務(wù)都必須按照這個(gè)順序?qū)?shù)據(jù)執(zhí)行封鎖。如不同的過(guò)程在事務(wù)內(nèi)部對(duì)對(duì)象的更新執(zhí)行順序應(yīng)盡量保證一致端三。

查看死鎖

Mysql 查詢是否存在鎖表有多種方式舷礼，這里只介紹一種最常用的。

1郊闯、查看正在進(jìn)行中的事務(wù)

```

SELECT * FROM information_schema.INNODB_TRX

```

2妻献、查看正在鎖的事務(wù)

```

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

```

3、查看等待鎖的事務(wù)

```

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

```

4团赁、查詢是否鎖表

```

SHOW OPEN TABLES where In_use > 0;

```

在發(fā)生死鎖時(shí)育拨，這幾種方式都可以查詢到和當(dāng)前死鎖相關(guān)的信息。

5欢摄、查看最近死鎖的日志

```

show engine innodb status

```

解除死鎖

如果需要解除死鎖熬丧，有一種最簡(jiǎn)單粗暴的方式，那就是找到進(jìn)程id之后怀挠，直接干掉析蝴。

查看當(dāng)前正在進(jìn)行中的進(jìn)程

```

show processlist

// 也可以使用SELECT*FROM information_schema.INNODB_TRX;

```

這兩個(gè)命令找出來(lái)的進(jìn)程id 是同一個(gè)。

殺掉進(jìn)程對(duì)應(yīng)的進(jìn)程 id

```

kill id

驗(yàn)證（kill后再看是否還有鎖）

SHOW OPEN TABLES where In_use > 0;

```

Oracle查看鎖表sql

查看鎖表進(jìn)程SQL語(yǔ)句1：

```

select sess.sid,? ? sess.serial#,? ? lo.oracle_username,? ? lo.os_user_name,? ? ao.object_name,? ? lo.locked_mode? ? from v$locked_object lo,? ? dba_objects ao,? ? v$session sesswhere ao.object_id = lo.object_id and lo.session_id = sess.sid;

```

查看鎖表進(jìn)程SQL語(yǔ)句2：

```

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

```

殺掉鎖表進(jìn)程：

如有記錄則表示有l(wèi)ock唆香，記錄下SID和serial# 嫌变，將記錄的ID替換下面的738,1429，即可解除LOCK

```

alter system kill session '738,1429';

```

查看導(dǎo)致鎖表的sql語(yǔ)句是那一條

```

select l.session_id sid,? ? s.serial#,? ? l.locked_mode,? ? l.oracle_username,? ? s.user#,? ? l.os_user_name,? ? s.machine,? ? s.terminal,? ? a.sql_text,? ? a.action? ? from v$sqlarea a, v$session s, v$locked_object l where l.session_id = s.sid? ? and s.prev_sql_addr = a.address? ? order by sid, s.serial#;

```