首先對(duì)mysql鎖進(jìn)行劃分：
- 按照鎖的粒度劃分：行鎖、表鎖昧捷、頁(yè)鎖
- 按照鎖的使用方式劃分：共享鎖刻炒、排它鎖（悲觀鎖的一種實(shí)現(xiàn)）
- 還有兩種思想上的鎖：悲觀鎖、樂(lè)觀鎖栖榨。
- InnoDB中有幾種行級(jí)鎖類型：Record Lock昆汹、Gap Lock、Next-key Lock
1. Record Lock：在索引記錄上加鎖
2. Gap Lock：間隙鎖
3. Next-key Lock：Record Lock+Gap Lock
行鎖
- 行級(jí)鎖是Mysql中鎖定粒度最細(xì)的一種鎖婴栽，表示只針對(duì)當(dāng)前操作的行進(jìn)行加鎖满粗。
- 行級(jí)鎖能大大減少數(shù)據(jù)庫(kù)操作的沖突。其加鎖粒度最小愚争，但加鎖的開銷也最大映皆。有可能會(huì)出現(xiàn)死鎖的情況。
- 行級(jí)鎖按照使用方式分為：共享鎖 和 排他鎖轰枝。
共享鎖用法（S鎖 / 讀鎖）：
- 若事務(wù)T捅彻，對(duì)數(shù)據(jù)對(duì)象A加上S鎖，則事務(wù)T可以讀A但
不能修改A鞍陨，其他事務(wù)只能再對(duì)A加S鎖步淹，而不能加X(jué)鎖（排他鎖），直到T釋放A上的S鎖诚撵。這保證了其他事務(wù)可以讀A贤旷，但在T釋放A上的S鎖之前不能對(duì)A做任何修改。
select ... lock in share mode;
- 共享鎖就是允許多個(gè)線程同時(shí)獲取一個(gè)鎖砾脑，一個(gè)鎖可以同時(shí)被多個(gè)線程擁有幼驶。
排它鎖用法（X鎖 / 寫鎖）：
- 若事務(wù)T，對(duì)數(shù)據(jù)對(duì)象A加上X鎖韧衣，事務(wù)T可以讀A也
可以修改A盅藻，其他事務(wù)不能再對(duì)A加任何鎖，直到T釋放A上的鎖畅铭。這保證了其他事務(wù)在T釋放A上的鎖之前不能再讀取和修改A氏淑。
select ... for update
- 排它鎖，也稱作獨(dú)占鎖硕噩，一個(gè)鎖在某一時(shí)刻只能被一個(gè)線程占有假残，其它線程必須等待鎖被釋放之后才可能獲取到鎖。
表鎖
- 表級(jí)鎖是mysql鎖中粒度最大的一種鎖，表示當(dāng)前的操作對(duì)整張表加鎖辉懒。
- 資源開銷比行鎖少阳惹，不會(huì)出現(xiàn)死鎖的情況，但是發(fā)生鎖沖突的概率很大眶俩。
- 被大部分的mysql引擎支持莹汤，MyISAM和InnoDB都支持表級(jí)鎖，但是InnoDB默認(rèn)的是行級(jí)鎖颠印。
注意：表鎖纲岭，講的是鎖的顆粒度，共享鎖线罕、排他鎖講的是使用方式止潮。
共享鎖用法：
- 釋義在上面行鎖講過(guò)，只是用法不同钞楼。
LOCK TABLE table_name [ AS alias_name ] READ
排它鎖用法：
- 釋義在上面行鎖講過(guò)喇闸，只是用法不同。
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE
解鎖用法：
unlock tables;
頁(yè)鎖
- 頁(yè)級(jí)鎖是MySQL中鎖定粒度介于行級(jí)鎖和表級(jí)鎖中間的一種鎖窿凤。
- 表級(jí)鎖速度快仅偎，但沖突多跨蟹，行級(jí)沖突少雳殊，但速度慢。所以取了折衷的頁(yè)級(jí)窗轩，一次鎖定相鄰的一組記錄夯秃。
- BDB支持頁(yè)級(jí)鎖。
1. MySQL 一種數(shù)據(jù)庫(kù)存儲(chǔ)引擎痢艺。
2. BDB引擎：Sleepycat Software給MySQL提供Berkeley DB事務(wù)性存儲(chǔ)引擎仓洼。這個(gè)存儲(chǔ)引擎典型被簡(jiǎn)稱為BDB。
3. BDB存儲(chǔ)引擎的支持包括在MySQL源碼分發(fā)版里堤舒，在MySQL-Max二進(jìn)制分發(fā)版里被激活色建。因此，mysqld和mysqld-nt是不支持BDB數(shù)據(jù)庫(kù)引擎的舌缤，若要支持應(yīng)選擇mysqld-max或mysqld-max-nt為服務(wù)箕戳。
4. 要查看當(dāng)前數(shù)據(jù)庫(kù)支持哪些引擎，可以使用show engines命令国撵。
樂(lè)觀鎖和悲觀鎖（思想設(shè)計(jì)上的鎖）
- 在數(shù)據(jù)庫(kù)的鎖機(jī)制中介紹過(guò)陵吸，數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）中的并發(fā)控制的任務(wù)是確保在多個(gè)事務(wù)同時(shí)存取數(shù)據(jù)庫(kù)中同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性和統(tǒng)一性以及數(shù)據(jù)庫(kù)的統(tǒng)一性。
- 樂(lè)觀并發(fā)控制(樂(lè)觀鎖)和悲觀并發(fā)控制（悲觀鎖）是并發(fā)控制主要采用的技術(shù)手段介牙。
- 無(wú)論是悲觀鎖還是樂(lè)觀鎖壮虫，都是人們定義出來(lái)的概念，可以認(rèn)為是一種思想环础。其實(shí)不僅僅是關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)中有樂(lè)觀鎖和悲觀鎖的概念囚似，像memcache剩拢、hibernate、tair等都有類似的概念谆构。
- 針對(duì)于不同的業(yè)務(wù)場(chǎng)景裸扶，應(yīng)該選用不同的并發(fā)控制方式。所以搬素，不要把樂(lè)觀并發(fā)控制和悲觀并發(fā)控制狹義的理解為DBMS中的概念呵晨，更不要把他們和數(shù)據(jù)庫(kù)中提供的鎖機(jī)制（行鎖、表鎖熬尺、排他鎖摸屠、共享鎖）混為一談。其實(shí)粱哼，在DBMS中季二，悲觀鎖正是利用數(shù)據(jù)庫(kù)本身提供的鎖機(jī)制來(lái)實(shí)現(xiàn)的。
- 市場(chǎng)上比較流行的數(shù)據(jù)庫(kù)管理系統(tǒng)產(chǎn)品主要是：Oracle揭措、IBM胯舷、Microsoft和Sybase、Mysql 等公司的產(chǎn)品绊含。
悲觀鎖
在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)里桑嘶，悲觀并發(fā)控制（又名“悲觀鎖”，Pessimistic Concurrency Control躬充，縮寫“PCC”）是一種并發(fā)控制的方法逃顶。它可以阻止一個(gè)事務(wù)以影響其他用戶的方式來(lái)修改數(shù)據(jù)。如果一個(gè)事務(wù)執(zhí)行的操作對(duì)某行數(shù)據(jù)應(yīng)用了鎖充甚，那只有當(dāng)這個(gè)事務(wù)把鎖釋放以政，其他事務(wù)才能夠執(zhí)行與該鎖沖突的操作。悲觀并發(fā)控制主要用于數(shù)據(jù)爭(zhēng)用激烈的環(huán)境伴找，以及發(fā)生并發(fā)沖突時(shí)使用鎖保護(hù)數(shù)據(jù)的成本要低于回滾事務(wù)的成本的環(huán)境中盈蛮。
- 悲觀鎖，正如其名技矮，它指的是對(duì)數(shù)據(jù)被外界（包括本系統(tǒng)當(dāng)前的其他事務(wù)抖誉，以及來(lái)自外部系統(tǒng)的事務(wù)處理）修改持悲觀態(tài)度，因此穆役，在整個(gè)數(shù)據(jù)處理過(guò)程中寸五，將數(shù)據(jù)處于鎖定狀態(tài)。
- 悲觀鎖的實(shí)現(xiàn)耿币，往往依靠數(shù)據(jù)庫(kù)提供的鎖機(jī)制（也只有數(shù)據(jù)庫(kù)層提供的鎖機(jī)制才能真正保證數(shù)據(jù)訪問(wèn)的排他性梳杏，否則，即使在本系統(tǒng)中實(shí)現(xiàn)了加鎖機(jī)制，也無(wú)法保證外部系統(tǒng)不會(huì)修改數(shù)據(jù)）
悲觀鎖的具體流程：
- 在對(duì)任意記錄進(jìn)行修改前十性，先嘗試為該記錄加上排他鎖（exclusive locking）
- 如果加鎖失敗叛溢，說(shuō)明該記錄正在被修改，那么當(dāng)前查詢可能要等待或者拋出異常劲适。 具體響應(yīng)方式由開發(fā)者根據(jù)實(shí)際需要決定楷掉。
- 如果成功加鎖，那么就可以對(duì)記錄做修改霞势，事務(wù)完成后就會(huì)解鎖了烹植。
- 其間如果有其他對(duì)該記錄做修改或加排他鎖的操作，都會(huì)等待我們解鎖或直接拋出異常愕贡。
在mysql的InnoDB引擎中使用悲觀鎖
- 其實(shí)就是使用mysql提供的排他鎖草雕。
- 首先我們得關(guān)閉mysql中的autocommit屬性，因?yàn)閙ysql默認(rèn)使用自動(dòng)提交模式固以，也就是說(shuō)當(dāng)我們進(jìn)行一個(gè)sql操作的時(shí)候墩虹，mysql會(huì)將這個(gè)操作當(dāng)做一個(gè)事務(wù)并且自動(dòng)提交這個(gè)操作。
-- 窗口1：
mysql> start transaction;
Query OK, 0 rows affected
-- 對(duì)7369這行憨琳，加排他鎖
mysql> select * from emp where empno = 7369 for update;
+-------+-------+-------+------+------------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-------+------+------------+-----+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
+-------+-------+-------+------+------------+-----+------+--------+
1 row in set
mysql>
窗口2：
mysql> start transaction;
Query OK, 0 rows affected
-- 執(zhí)行對(duì)該行進(jìn)行修改時(shí)诫钓，一直處于等待狀態(tài)
mysql> update emp set ename = 'aa' where empno = 7369;
-- 當(dāng)窗口1，提交事務(wù)（commit）后篙螟，窗口2開始執(zhí)行菌湃。
- 上面的例子展示了排它鎖的原理：一個(gè)鎖在某一時(shí)刻只能被一個(gè)線程占有，其它線程必須等待鎖被釋放之后才可能獲取到鎖或者進(jìn)行數(shù)據(jù)的操作闲擦。
悲觀鎖的優(yōu)點(diǎn)和不足
- 優(yōu)點(diǎn)：悲觀鎖實(shí)際上是采取了先取鎖再訪問(wèn)的策略慢味，為數(shù)據(jù)的處理安全提供了保證场梆。
- 缺點(diǎn)：在效率方面墅冷，由于額外的加鎖機(jī)制產(chǎn)生了額外的開銷，并且增加了死鎖的機(jī)會(huì)或油。并且降低了并發(fā)性寞忿。當(dāng)一個(gè)事物所以一行數(shù)據(jù)的時(shí)候，其他事物必須等待該事務(wù)提交之后顶岸，才能操作這行數(shù)據(jù)腔彰。
樂(lè)觀鎖
在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)里，樂(lè)觀并發(fā)控制（又名“樂(lè)觀鎖”辖佣，Optimistic Concurrency Control霹抛，縮寫“OCC”）是一種并發(fā)控制的方法。它假設(shè)多用戶并發(fā)的事務(wù)在處理時(shí)不會(huì)彼此互相影響卷谈，各事務(wù)能夠在不產(chǎn)生鎖的情況下處理各自影響的那部分?jǐn)?shù)據(jù)杯拐。在提交數(shù)據(jù)更新之前，每個(gè)事務(wù)會(huì)先檢查在該事務(wù)讀取數(shù)據(jù)后，有沒(méi)有其他事務(wù)又修改了該數(shù)據(jù)端逼。如果其他事務(wù)有更新的話朗兵，正在提交的事務(wù)會(huì)進(jìn)行回滾。
- 樂(lè)觀鎖（ Optimistic Locking ） 相對(duì)悲觀鎖而言顶滩，樂(lè)觀鎖假設(shè)認(rèn)為數(shù)據(jù)一般情況下不會(huì)造成沖突余掖，所以在數(shù)據(jù)進(jìn)行提交更新的時(shí)候，才會(huì)正式對(duì)數(shù)據(jù)的沖突與否進(jìn)行檢測(cè)礁鲁，如果發(fā)現(xiàn)沖突了盐欺，則讓返回用戶錯(cuò)誤的信息，讓用戶決定如何去做仅醇。
- 相對(duì)于悲觀鎖找田，在對(duì)數(shù)據(jù)庫(kù)進(jìn)行處理的時(shí)候，樂(lè)觀鎖并不會(huì)使用數(shù)據(jù)庫(kù)提供的鎖機(jī)制着憨。一般的實(shí)現(xiàn)樂(lè)觀鎖的方式就是記錄數(shù)據(jù)版本墩衙。
數(shù)據(jù)版本,為數(shù)據(jù)增加的一個(gè)版本標(biāo)識(shí)。當(dāng)讀取數(shù)據(jù)時(shí)甲抖，將版本標(biāo)識(shí)的值一同讀出漆改，數(shù)據(jù)每更新一次，同時(shí)對(duì)版本標(biāo)識(shí)進(jìn)行更新准谚。當(dāng)我們提交更新的時(shí)候挫剑，判斷數(shù)據(jù)庫(kù)表對(duì)應(yīng)記錄的當(dāng)前版本信息與第一次取出來(lái)的版本標(biāo)識(shí)進(jìn)行比對(duì)，如果數(shù)據(jù)庫(kù)表當(dāng)前版本號(hào)與第一次取出來(lái)的版本標(biāo)識(shí)值相等柱衔，則予以更新樊破，否則認(rèn)為是過(guò)期數(shù)據(jù)。
樂(lè)觀鎖的優(yōu)點(diǎn)和不足：
- 優(yōu)點(diǎn)：樂(lè)觀并發(fā)控制相信事務(wù)之間的數(shù)據(jù)競(jìng)爭(zhēng)(data race)的概率是比較小的唆铐，因此盡可能直接做下去哲戚，直到提交的時(shí)候才去鎖定，所以不會(huì)產(chǎn)生任何鎖和死鎖艾岂。
- 缺點(diǎn)：但如果直接簡(jiǎn)單這么做顺少，還是有可能會(huì)遇到不可預(yù)期的結(jié)果，例如兩個(gè)事務(wù)都讀取了數(shù)據(jù)庫(kù)的某一行王浴，經(jīng)過(guò)修改以后寫回?cái)?shù)據(jù)庫(kù)脆炎，這時(shí)就遇到了問(wèn)題。對(duì)于之前的操作都是無(wú)用功氓辣。
InnoDB鎖的特性
在不通過(guò)索引條件查詢的時(shí)候秒裕，InnoDB使用是表鎖！
由于 MySQL 的行鎖是針對(duì)索引加的鎖钞啸，不是針對(duì)記錄加的鎖,所以雖然是訪問(wèn)不同行的記錄几蜻，但是如果是使用相同的索引鍵癞松，是會(huì)出現(xiàn)鎖沖突的。
當(dāng)表有多個(gè)索引的時(shí)候入蛆，不同的事務(wù)可以使用不同的索引鎖定不同的行响蓉，另外，不論 是使用主鍵索引哨毁、唯一索引或普通索引枫甲，InnoDB 都會(huì)使用行鎖來(lái)對(duì)數(shù)據(jù)加鎖。
即便在條件中使用了索引字段扼褪，但是否使用索引來(lái)檢索數(shù)據(jù)是由 MySQL 通過(guò)判斷不同執(zhí)行計(jì)劃的代價(jià)來(lái)決定的想幻，如果 MySQL 認(rèn)為全表掃效率更高，比如：對(duì)一些很小的表话浇，它就不會(huì)使用索引脏毯，這種情況下 InnoDB 將使用表鎖，而不是行鎖幔崖。
因此食店，在分析鎖沖突時(shí)，別忘了檢查 SQL 的執(zhí)行計(jì)劃（explain查看）赏寇，以確認(rèn)是否真正使用了索引吉嫩。
有關(guān)執(zhí)行計(jì)劃的解釋可以看著這篇文章：淺談mysql執(zhí)行計(jì)劃之type
1. 通過(guò)非索引項(xiàng)檢索數(shù)據(jù)，加表鎖嗅定！
-- price屬性并沒(méi)有加索引自娩，因此這時(shí)候添加的鎖為表級(jí)鎖！
窗口1：
mysql> select * from product where price=88 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
| 2 | 蒙牛 | 88 | 1 |
+----+------+-------+-----+
窗口2：
mysql> update product set price=price-100 where id=6;
這里會(huì)等待渠退，直到窗口1 commit后顯示下面結(jié)果忙迁！
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
2. 使用相同索引值但是不同行引發(fā)的沖突
-- 這里的num屬性 加上了普通索引，price屬性并沒(méi)有索引
窗口1：
mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select * from product where num=1 and price=68 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
| 1 | 伊利 | 68 | 1 |
+----+------+-------+-----+
窗口2：
mysql> update product set price=price+100 where num=1 and price=88;
這里會(huì)發(fā)生等待碎乃，直到窗口1 commit 顯示下面結(jié)果
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from product;
+----+----------+-------+-----+
| id | name | price | num |
+----+----------+-------+-----+
| 1 | 伊利 | 68 | 1 |
| 2 | 蒙牛 | 188 | 1 |
+----+----------+-------+-----+
3. 當(dāng)使用索引檢索數(shù)據(jù)時(shí)不同事務(wù)可以操作不同行數(shù)據(jù)
鎖一行數(shù)據(jù)姊扔，DML操作其他行并沒(méi)有影響
窗口1：
mysql> select * from user where id=1 for update;
+----+-------+
| id | price |
+----+-------+
| 1 | 400 |
+----+-------+
窗口2：
mysql> update user set price=price+100 where id=2;
無(wú)需等待窗口1 commit
Database changed
Rows matched: 1 Changed: 1 Warnings: 0
Record Lock、Gap Lock荠锭、Next-key Lock鎖
Record Lock
- 單條索引上加鎖旱眯，record lock 永遠(yuǎn)鎖的是索引晨川，而非數(shù)據(jù)本身证九，如果innodb表中沒(méi)有索引，那么會(huì)自動(dòng)創(chuàng)建一個(gè)隱藏的聚集索引共虑，鎖住的就是這個(gè)聚集索引愧怜。
- 所以說(shuō)當(dāng)一條sql沒(méi)有走任何索引時(shí)，那么將會(huì)在每一條聚集索引后面加X(jué)鎖妈拌，這個(gè)類似于表鎖拥坛，但原理上和表鎖應(yīng)該是完全不同的蓬蝶。
Gap Lock 間隙鎖
- 間隙鎖，是在索引的間隙之間加上鎖猜惋，這是為什么Repeatable Read隔離級(jí)別下能防止幻讀的主要原因丸氛。
- 有關(guān)幻讀的詳細(xì)解釋：數(shù)據(jù)庫(kù)事務(wù)和隔離級(jí)別的理解
1、什么叫間隙鎖著摔？
mysql> select * from product_copy;
+----+--------+-------+-----+
| id | name | price | num |
+----+--------+-------+-----+
| 1 | 伊利 | 68 | 1 |
| 2 | 蒙牛 | 88 | 1 |
| 6 | tom | 2788 | 3 |
| 10 | 優(yōu)衣庫(kù) | 488 | 4 |
+----+--------+-------+-----+
-- 其中id為主鍵 num為普通索引
窗口A：
mysql> select * from product_copy where num=3 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
| 6 | tom | 2788 | 3 |
+----+------+-------+-----+
1 row in set
窗口B：
mysql> insert into product_copy values(5,'kris',1888,2);
-- 這里會(huì)等待 直到窗口A commit才會(huì)顯示下面結(jié)果
Query OK, 1 row affected
-- 但是下面是不需要等待的
mysql> update product_copy set price=price+100 where num=1;
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
mysql> insert into product_copy values(5,'kris',1888,5);
Query OK, 1 row affected
- 通過(guò)上面的例子可以看出Gap鎖的作用是在1,3的間隙之間加上了鎖缓窜。而且并不是鎖住了表，我更新num=1,5的數(shù)據(jù)是可以的谍咆『檀福可以看出鎖住的范圍是：(1,3]U[3,4)。
2摹察、為什么說(shuō) Gap鎖 是RR（repeatable-read/可重復(fù)讀）隔離級(jí)別下防止幻讀的主要原因恩掷？
- 解決幻讀的方式很簡(jiǎn)單，就是需要當(dāng)事務(wù)進(jìn)行當(dāng)前讀的時(shí)候供嚎，保證其他事務(wù)不可以在滿足當(dāng)前讀條件的范圍內(nèi)進(jìn)行數(shù)據(jù)操作黄娘。
- 根據(jù)索引的有序性，我們可以從上面的例子推斷出滿足where條件的數(shù)據(jù)克滴，只能插入在num=(1,3]U[3,4)兩個(gè)區(qū)間里面寸宏，只要我們將這兩個(gè)區(qū)間鎖住，那么就不會(huì)發(fā)生幻讀偿曙。
3氮凝、主鍵索引/唯一索引 + 當(dāng)前讀 會(huì)加上Gap鎖嗎？
窗口A：
mysql> select * from product_copy where id=6 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
| 6 | tom | 2788 | 3 |
+----+------+-------+-----+
窗口B：并不會(huì)發(fā)生等待
mysql> insert into product_copy values(5,'kris',1888,3);
Query OK, 1 row affected
- 例子說(shuō)明的其實(shí)就是行鎖的原因望忆，我只將id=6的行數(shù)據(jù)鎖住了罩阵，用Gap鎖的原理來(lái)解釋的話：因?yàn)橹麈I索引和唯一索引的值只有一個(gè)，所以滿足檢索條件的只有一行启摄，故并不會(huì)出現(xiàn)幻讀稿壁，所以并不會(huì)加上Gap鎖。
4歉备、通過(guò)范圍查詢是否會(huì)加上Gap鎖傅是？
窗口A：
mysql> select * from product_copy where num>3 for update;
+----+--------+-------+-----+
| id | name | price | num |
+----+--------+-------+-----+
| 10 | 優(yōu)衣庫(kù) | 488 | 4 |
+----+--------+-------+-----+
窗口B：會(huì)等待
mysql> insert into product_copy values(11,'kris',1888,5);
Query OK, 1 row affected
不會(huì)等待
mysql> insert into product_copy values(3,'kris',1888,2);
Query OK, 1 row affected
- 其實(shí)原因都是一樣，只要滿足檢索條件的都會(huì)加上Gap鎖
5蕾羊、檢索條件并不存在的當(dāng)前讀會(huì)加上Gap嗎喧笔？
- 等值查詢
窗口A：
mysql> select * from product_copy where num=5 for update;
Empty set
窗口B：6 和 4都會(huì)等待
mysql> insert into product_copy values(11,'kris',1888,6);
Query OK, 1 row affected
mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected
- 原因一樣會(huì)鎖住（4,5]U[5,n）的區(qū)間
- 范圍查詢
窗口A：
mysql> select * from product_copy where num>6 for update;
Empty set
窗口B：8 和 4 都會(huì)鎖住
mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected
mysql> insert into product_copy values(11,'kris',1888,8);
Query OK, 1 row affected
- 上面的2例子看出當(dāng)你查詢并不存在的數(shù)據(jù)的時(shí)候龟再，mysql會(huì)將有可能出現(xiàn)區(qū)間全部鎖住书闸。
Next-Key Lock
- 這個(gè)鎖機(jī)制其實(shí)就是前面兩個(gè)鎖相結(jié)合的機(jī)制，既鎖住記錄本身還鎖住索引之間的間隙利凑。
死鎖的原理及分析
MVCC
- MySQL的InnoDB存儲(chǔ)引擎浆劲，實(shí)現(xiàn)的是基于多版本并發(fā)控制協(xié)議—MVCC(Multi-Version Concurrency Control)
- MVCC最大的好處嫌术，相信也是耳熟能詳：讀不加鎖，讀寫不沖突牌借。在讀多寫少的OLTP應(yīng)用中度气，讀寫不沖突是非常重要的，極大的增加了系統(tǒng)的并發(fā)性能膨报，這也是為什么現(xiàn)階段蚯嫌，幾乎所有的RDBMS（關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)），都支持了MVCC丙躏。
2PL：Two-Phase Locking
- 傳統(tǒng)RDBMS（關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)）加鎖的一個(gè)原則择示，就是2PL (二階段鎖)：Two-Phase Locking。
- 相對(duì)而言晒旅，2PL比較容易理解栅盲，說(shuō)的是鎖操作分為兩個(gè)階段：加鎖階段 與 解鎖階段，并且保證加鎖階段與解鎖階段不相交废恋。下面谈秫，仍舊以MySQL為例，來(lái)簡(jiǎn)單看看2PL在MySQL中的實(shí)現(xiàn)鱼鼓。
image.png
為什么會(huì)發(fā)生死鎖拟烫？
- MyISAM中是不會(huì)產(chǎn)生死鎖的，因?yàn)镸yISAM總是一次性獲得所需的全部鎖迄本，要么全部滿足硕淑，要么全部等待。
- 而在InnoDB中嘉赎，鎖是逐步獲得的置媳，就造成了死鎖的可能。（不過(guò)現(xiàn)在一般都是InnoDB引擎公条，關(guān)于MyISAM不做考慮）拇囊。
- 在InnoDB中，行級(jí)鎖并不是直接鎖記錄靶橱，而是鎖索引寥袭。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語(yǔ)句操作了主鍵索引关霸，MySQL就會(huì)鎖定這條主鍵索引传黄。
- 如果一條語(yǔ)句操作了非主鍵索引，MySQL會(huì)先鎖定該非主鍵索引，再鎖定相關(guān)的主鍵索引聋呢。
- 當(dāng)兩個(gè)事務(wù)同時(shí)執(zhí)行，一個(gè)鎖住了主鍵索引淑玫，在等待其他相關(guān)索引。另一個(gè)鎖定了非主鍵索引卧蜓，在等待主鍵索引。這樣就會(huì)發(fā)生死鎖。
通過(guò)兩個(gè)SQL死鎖的例子來(lái)說(shuō)明
1. 兩個(gè)session的兩條語(yǔ)句
image.png
- 這種情況很好理解惭聂，首先session1獲得 id=1的鎖 session2獲得id=5的鎖，然后session想要獲取id=5的鎖 等待相恃，session2想要獲取id=1的鎖 辜纲，也等待！
2.兩個(gè)session的一條語(yǔ)句
image.png
- 這種情況需要我們了解數(shù)據(jù)的索引的檢索順序原理簡(jiǎn)單說(shuō)下：普通索引上面保存了主鍵索引拦耐，當(dāng)我們使用普通索引檢索數(shù)據(jù)時(shí)耕腾，如果所需的信息不夠，那么會(huì)繼續(xù)遍歷主鍵索引杀糯。
- 假設(shè)默認(rèn)情況是RR隔離級(jí)別扫俺，針對(duì)session 1 從name索引出發(fā)，檢索到的是（hdc,1）（hdc,6）不僅會(huì)加name索引上的記錄X鎖固翰，而且會(huì)加聚簇索引上的記錄X鎖狼纬，加鎖順序?yàn)橄萚1,hdc,100]，后[6,hdc,10] 這個(gè)順序是因?yàn)锽+樹結(jié)構(gòu)的有序性骂际。
- 而Session 2疗琉，從pubtime索引出發(fā)，[10,6],[100,1]均滿足過(guò)濾條件歉铝，同樣也會(huì)加聚簇索引上的記錄X鎖盈简，加鎖順序?yàn)閇6,hdc,10]，后[1,hdc,100]太示。
- 發(fā)現(xiàn)沒(méi)有送火，跟Session 1的加鎖順序正好相反，如果兩個(gè)Session恰好都持有了第一把鎖先匪，請(qǐng)求加第二把鎖种吸，死鎖就發(fā)生了。
避免死鎖呀非，這里只介紹常見的三種
- 如果不同程序會(huì)并發(fā)存取多個(gè)表坚俗，盡量約定以相同的順序訪問(wèn)表，可以大大降低死鎖機(jī)會(huì)岸裙。
- 在同一個(gè)事務(wù)中猖败，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率降允；
- 對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分恩闻，可以嘗試使用升級(jí)鎖定顆粒度，通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率剧董；
