想要用好 MySQL，索引與鎖是一個(gè)繞不開的話題。最近一直在維護(hù)以前的業(yè)務(wù)系統(tǒng)，線上頻繁報(bào)出數(shù)據(jù)庫死鎖的異常警告，為了排查以及規(guī)避死鎖的問題，因此對(duì) MySQL 的鎖(Innodb引擎)做了一個(gè)比較深入學(xué)習(xí)，順便加深自己對(duì) MySQL 的理解程度。個(gè)人感覺 MySQL 中的鎖還是非常的雜，官網(wǎng)對(duì)于鎖的介紹也是和盤托出，并沒有分門別類的羅列出來，下面分別從鎖的模式與算法來分析。

鎖的模式

MySQL 中有鎖的模式這個(gè)概念，在 Innodb 中鎖的模式有共享鎖(S鎖)、排它鎖(X鎖)兩大類。除了這兩大類，還有一個(gè)意向鎖的概念，意向鎖在鎖模式中有意向共享鎖(IS鎖)、意向排它鎖(IX鎖)兩類。

共享鎖(行級(jí)別)

共享鎖也叫讀鎖，對(duì)于同一個(gè)資源，大家都可以同時(shí)加上共享鎖，可以理解成非獨(dú)家的資源，大家都可以獲取。共享鎖與排它鎖互斥，在 MySQL 中可以使用“l(fā)ock in share mode”語法顯式的加共享鎖。

1select * from test where id = 1 lock in share mode;

排它鎖(行級(jí)別)

排它鎖也叫寫鎖，排它鎖不僅與共享鎖互斥，排它鎖與排它鎖也互斥，在 MySQL 中可以使用“for update”語法顯示的加排它鎖。

1select * from test where id = 1 for update;

意向鎖(表級(jí)別)

InnoDB 存儲(chǔ)引擎支持多粒度鎖定，當(dāng)數(shù)據(jù)庫對(duì)行記錄進(jìn)行加鎖時(shí)，首先會(huì)在粗粒度的表級(jí)別加上意向鎖。意向鎖的主要目的是為了提升表鎖的加鎖效率，假設(shè)沒有意向鎖，數(shù)據(jù)庫在對(duì)表進(jìn)行加鎖時(shí)，需要掃描表中所有的記錄，查看是否有行鎖與當(dāng)前要加的表鎖沖突。意向鎖之間是沒有沖突的，意向鎖與普通的表鎖之間有沖突。

意向共享鎖

意向共享鎖無法通過語法顯式的操作，當(dāng)加上行級(jí)別的共享鎖時(shí)，意向共享鎖就會(huì)存在，意向共享鎖與排它鎖(表級(jí))沖突。

意向排它鎖

意向排它鎖與上面的意向共享鎖類似，它與共享鎖(表級(jí))、排它鎖(表級(jí))都沖突。

普通鎖(表級(jí)別)

表級(jí)別的鎖因?yàn)榱６雀?#xff0c;所以在判斷是否有沖突時(shí)不僅要考慮表級(jí)別下的其它鎖，還要考慮表中的行鎖。

共享鎖

除了對(duì)行可以加共享鎖，也可以對(duì)表加共享鎖，通過“l(fā)ock table read”語法來對(duì)表加共享鎖。表級(jí)別的共享鎖不僅與表級(jí)別的排它鎖沖突，而且與行級(jí)別的排它鎖沖突。

1lock table test read;

排它鎖

除了對(duì)行可以加排它鎖，也可以對(duì)表加排它鎖，通過“l(fā)ock table write”語法來對(duì)表加共享鎖。表級(jí)別的排它鎖不僅與表級(jí)別的共享鎖、排它鎖沖突，而且與行級(jí)別的排它鎖也沖突。

1lock table test write;

鎖的算法

鎖的模式側(cè)重鎖的意圖，鎖的算法更加偏重鎖的范圍，例如下面列舉的這些鎖都是跟鎖住的范圍區(qū)間有關(guān)系。為了更好的演示鎖住的區(qū)間，下面給出測(cè)試的表結(jié)構(gòu)以及數(shù)據(jù)方便論證。

表結(jié)構(gòu)：

1

2

3

4

5

6

7

8CREATE TABLE `test_info` (

`id` int(11) NOT NULL,

`no` int(20) NOT NULL AUTO_INCREMENT,

`description` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `idx_no` (`no`) USING BTREE,

KEY `idx_description` (`description`(191)) USING BTREE

);

數(shù)據(jù)：

1

2

3

4

5INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (1, 10, '100');

INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (2, 20, '200');

INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (5, 50, '500');

INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (10, 100, '1000');

INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (11, 110, '1000');

記錄鎖

記錄鎖作用在精確匹配的唯一索引上，它與間隙鎖最大的差別在于間隙鎖鎖住的是不存在的區(qū)間。

記錄鎖 SQL 示例：

主鍵索引加鎖

事物一：

1select * from test_info where id = 1 for update;

事物二：

1select * from test_info where id = 1 for update;

事物二阻塞。

唯一索引加鎖

事物一：

1select * from test_info where no = 10 for update;

事物二：

1select * from test_info where no = 10 for update;

事物二阻塞

普通索引加鎖

事物一：

1select * from test_info where description = 1000 for update;

事物二：

1select * from test_info where id = 10 for update;

事物二阻塞，結(jié)束事物二，執(zhí)行事物三

事物三：

1select * from test_info where id = 11 for update;

事物三阻塞，結(jié)束事物三，執(zhí)行事物四

事物四：

1select * from test_info where id = 5 for update;

事物四未被阻塞。

普通索引作為二級(jí)索引被加鎖時(shí)，對(duì)應(yīng)的主鍵索引也是會(huì)被加鎖的，所以可以看到 id 為 10、11 的記錄都會(huì)被阻塞住。

間隙鎖

間隙鎖一般存在于 RR 的隔離級(jí)別，MySQL 在 RR 隔離級(jí)別下可以解決部分幻讀的問題，依靠的就是間隙鎖。間隙鎖的上界、下界都是開區(qū)間，間隙鎖存在的目的主要是為了阻塞插入操作，這樣就不會(huì)存在相同的條件下查詢的結(jié)果集不一致的情況。

間隙鎖示例(RR隔離級(jí)別)：

普通索引加鎖(未命中記錄)

事物一：

1select * from test_info where description = 300 for update;

事物二：

1INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (3, 21, '210');

事物二會(huì)阻塞，因?yàn)槭纠械氖挛锔綦x級(jí)別為 RR，并且在事物一中沒有查詢到記錄，為了避免幻讀發(fā)生，所以將 (200,500) 的期間鎖住了。上面的介紹中有提到間隙鎖一般存在于 RR 的隔離級(jí)別中，所以上面的示例在 RC 隔離級(jí)別下執(zhí)行事物二是不會(huì)被阻塞的。

普通索引加鎖(命中記錄)

事物一：

1select * from test_info where description = 200 for update;

事物二：

1select * from test_info where description = 210 for update;

事物二阻塞，結(jié)束事物二，執(zhí)行事物三

事物三：

1select * from test_info where description = 190 for update;

事物三阻塞。

如果普通索引命中了記錄，那么被鎖定的區(qū)間不僅有 (200,500)，還有 (100,200]，所以普通索引的前后都會(huì)被鎖住。

唯一索引加鎖

對(duì)于唯一索引如果命中記錄，那么會(huì)加上記錄鎖(臨間鎖退化為記錄鎖，記錄鎖已演示這種場(chǎng)景)，如果沒有命中記錄則會(huì)加上間隙鎖。

事物一：

1select * from test_info where no = 21 for update;

事物二：

1INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (3, 22, '210');

事物二阻塞

臨鍵鎖

臨鍵鎖是由記錄鎖與間隙鎖組成的(區(qū)間為左開右閉)，所以記錄鎖與間隙鎖可以理解成特殊場(chǎng)景下的臨鍵鎖。Innodb 引擎行鎖默認(rèn)采用的就是臨鍵鎖的算法，只是在不同的場(chǎng)景下會(huì)退化成記錄鎖或者間隙鎖。

特殊場(chǎng)景的鎖

插入操作

插入操作加鎖的操作是一個(gè)復(fù)雜的過程，并不會(huì)只存在一把鎖。插入過程中首先會(huì)加插入意向鎖，接著檢查是否有唯一鍵沖突的問題，針對(duì)唯一性沖突還有可能會(huì)加上 S GAP(RC的隔離級(jí)別也會(huì)存在)，當(dāng)記錄插入成功時(shí)還會(huì)對(duì)該條記錄加上 X 的記錄鎖。

事物一：

1

2

3delete from test_info where no = 50;

insert into `test_info`(`id`, `no`, `description`) values (5, 50, 500);

事物二：

1insert into `test_info`(`id`, `no`, `description`) values (3, 30, 300);

事物二阻塞

總結(jié)

以上是生活随笔為你收集整理的mysql数据库的各种锁_关于MySQL各类锁的总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。