這篇文章主要介紹“的InnoDB鎖機制介紹”，在日常操作中，相信很多人在MySQL的InnoDB鎖機制介紹問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”MySQL的InnoDB鎖機制介紹”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！
一 背景
MySQL鎖機制是一個(gè)極其復雜的實(shí)現，為數據庫并發(fā)訪(fǎng)問(wèn)和數據一致提供保障。這里僅僅針對MySQL訪(fǎng)問(wèn)數據的三種鎖做介紹，加深自己對鎖方面的掌握。
二 常見(jiàn)的鎖機制
我們知道對于InnoDB存儲引擎而言,MySQL 的行鎖機制是通過(guò)在索引上加鎖來(lái)鎖定要目標數據行的。常見(jiàn)的有如下三種鎖類(lèi)型，本文未聲明情況下都是在RR 事務(wù)隔離級別下的描述。
2.1 Record Locks
記錄鎖實(shí)際上是索引上的鎖，鎖定具體的一行或者多行記錄。當表上沒(méi)有創(chuàng )建索引時(shí)，InnoDB會(huì )創(chuàng )建一個(gè)隱含的聚族索引，并且使用該索引鎖定數據。通常我們可以使用 show innodb status 看到行鎖相關(guān)的信息。
2.2 Gap Locks
間隙鎖是鎖定具體的范圍，但是不包含行鎖本身。比如
select * from tab where id>10 and id<20；
RR事務(wù)隔離級別下會(huì )鎖定10-20之間的記錄，不允許類(lèi)似15這樣的值插入到表里，以便消除“幻讀”帶來(lái)的影響。間隙鎖的跨度可以是1條記錄(Record low就可以認為是一個(gè)特殊的間隙鎖 ，多行，或者為空。當訪(fǎng)問(wèn)的字段是唯一鍵/主鍵時(shí)，間隙鎖會(huì )降級為Record lock。RR事務(wù)隔離級別下訪(fǎng)問(wèn)一個(gè)空行 ，也會(huì )有間隙鎖，后續會(huì )舉例子說(shuō)明。
我們可以通過(guò)將事務(wù)隔離級別調整為RC 模式或者設置innodb_locks_unsafe_for_binlog=1 (該參數已經(jīng)廢棄)來(lái)禁用Gap鎖。
2.3 Next-Key Locks
是Record Lock+Gap Locks,鎖定一個(gè)范圍并且包含索引本身。例如索引值包含 2，4，9，14 四個(gè)值，其gap鎖的區間如下：
（-∞,2]，(2,4]，(4,9]，(9,14]，(14,+∞）
本文著(zhù)重從主鍵,唯一鍵、非唯一索引,不存在值訪(fǎng)問(wèn)四個(gè)方面來(lái)闡述RR模式下鎖的表現。
三 測試案例
3.1 主鍵/唯一鍵
CREATE TABLE `lck_primarkey` (
`id` int(11) NOT NULL,
val int(11) not null default 0,
primary key (`id`),
key idx_val(val)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into lck_primarkey values(2,3),(4,5),(9,8),(14,13)
會(huì )話(huà)1
[session1] >select * from lck_primarkey;
+----+-----+
| id | val |
+----+-----+
| 2 | 3 |
| 4 | 5 |
| 9 | 8 |
| 14 | 13 |
+----+-----+
4 rows in set (0.00 sec)
[session1] >begin;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_primarkey where id=9 for update;
+----+-----+
| id | val |
+----+-----+
| 9 | 8 |
+----+-----+
1 row in set (0.00 sec)
會(huì )話(huà)2
[session2] >begin;
Query OK, 0 rows affected (0.00 sec)
[session2] >insert into lck_primarkey values(7,6);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(5,5);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(13,13);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(10,9);
Query OK, 1 row affected (0.00 sec)
分析
從例子看，當訪(fǎng)問(wèn)表的where字段是主鍵或者唯一鍵的時(shí)候，session2中的插入操作并未被 session1 中的id=8 影響。官方表述
“Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:
select * from tab where id=100 for update”
就是說(shuō)當語(yǔ)句通過(guò)主鍵或者唯一鍵訪(fǎng)問(wèn)數據的時(shí)候，Innodb會(huì )使用Record lock鎖住記錄本身，而不是使用間隙鎖鎖定范圍。
需要注意以下兩種情況:
1 通過(guò)主鍵或則唯一索引訪(fǎng)問(wèn)不存在的值，也會(huì )產(chǎn)生GAP鎖。
[session1] >begin;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_primarkey where id=7 for update;
Empty set (0.00 sec)
[session2] >insert into lck_primarkey values(8,13);
^CCtrl-C -- sending "KILL QUERY 303042481" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(5,13);
^CCtrl-C -- sending "KILL QUERY 303042481" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(3,13);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(10,13);
Query OK, 1 row affected (0.00 sec)
2 通過(guò)唯一索引中的一部分字段來(lái)訪(fǎng)問(wèn)數據，比如unique key(a,b,c) ，select * from tab where a=x and b=y; 讀者朋友可以自己做這個(gè)例子。
3.2 非唯一鍵
CREATE TABLE `lck_secondkey` (
`id` int(11) NOT NULL,
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into lck_secondkey values(2),(4),(9),(14)
會(huì )話(huà)1
[session1] >begin ;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_secondkey;
+----+
| id |
+----+
| 2 |
| 3 |
| 4 |
| 9 |
| 14 |
+----+
5 rows in set (0.00 sec)
[session1] >select * from lck_secondkey where id=9 for update;
+----+
| id |
+----+
| 9 |
+----+
1 row in set (0.00 sec)
會(huì )話(huà)2
[session2] >begin;
Query OK, 0 rows affected (0.00 sec)
[session2] >insert into lck_secondkey values(3);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_secondkey values(4);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(5);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(6);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(7);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(8);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(9);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(10);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(11);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(12);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(13);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_secondkey values(14);
Query OK, 1 row affected (0.00 sec)
分析
事務(wù)1 對id=9進(jìn)行for update 訪(fǎng)問(wèn),session2 插入[4,13]的值都是失敗的。根據MySQL的鎖原理，Innodb 范圍索引或者表是通過(guò)Next-key locks 算法，RR事務(wù)隔離級別下，通過(guò)非唯一索引訪(fǎng)問(wèn)數據行并不是鎖定唯一的行，而是一個(gè)范圍。從例子上可以看出來(lái)MySQL對 [4,9] 和(9,14]之間的記錄加上了鎖，防止其他事務(wù)對4-14范圍中的值進(jìn)行修改?？赡苡凶x者對其中 id=4 不能修改，但是id=14的值去可以插入有疑問(wèn)？可以看接下來(lái)的例子
[session1] >select * from lck_primarkey;
+----+-----+
| id | val |
+----+-----+
| 2 | 3 |
| 4 | 5 |
| 9 | 8 |
| 14 | 13 |
+----+-----+
4 rows in set (0.00 sec)
[session1] >begin;
Query OK, 0 rows affected (0.00 sec)
[session1] >select * from lck_primarkey where val=8 for update;
+----+-----+
| id | val |
+----+-----+
| 9 | 8 |
+----+-----+
1 row in set (0.00 sec)
會(huì )話(huà)2
[session2] >begin;
Query OK, 0 rows affected (0.00 sec)
[session2] >insert into lck_primarkey values(3,5);
Query OK, 1 row affected (0.00 sec)
[session2] >insert into lck_primarkey values(15,13);
Query OK, 1 row affected (0.00 sec)
[session2] >select * from lck_primarkey;
+----+-----+
| id | val |
+----+-----+
| 2 | 3 |
| 3 | 5 |
| 4 | 5 |
| 9 | 8 |
| 14 | 13 |
| 15 | 13 |
+----+-----+
6 rows in set (0.00 sec)
[session2] >insert into lck_primarkey values(16,12);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(16,6);
^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
[session2] >insert into lck_primarkey values(16,5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[session2] >
[session2] >insert into lck_primarkey values(1,5);
Query OK, 1 row affected (0.00 sec)
分析
因為session1 對非唯一鍵val=8 加上了gap鎖 [4,5] -[14,13],非此區間的記錄都可以插入表中。記錄(1,5),(15,13)不在此gap鎖區間，記錄(16,12),(16,6),(16,5)中的val值在被鎖的范圍內，故不能插入。
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng )、來(lái)自本網(wǎng)站內容采集于網(wǎng)絡(luò )互聯(lián)網(wǎng)轉載等其它媒體和分享為主，內容觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如侵犯了原作者的版權，請告知一經(jīng)查實(shí)，將立刻刪除涉嫌侵權內容，聯(lián)系我們QQ:712375056，同時(shí)歡迎投稿傳遞力量。
Copyright ? 2009-2022 56dr.com. All Rights Reserved. 特網(wǎng)科技 特網(wǎng)云 版權所有 特網(wǎng)科技 粵ICP備16109289號
域名注冊服務(wù)機構:阿里云計算有限公司（萬(wàn)網(wǎng)） 域名服務(wù)機構：煙臺帝思普網(wǎng)絡(luò )科技有限公司（DNSPod） CDN服務(wù)：阿里云計算有限公司 百度云 中國互聯(lián)網(wǎng)舉報中心 增值電信業(yè)務(wù)經(jīng)營(yíng)許可證B2
建議您使用Chrome、Firefox、Edge、IE10及以上版本和360等主流瀏覽器瀏覽本網(wǎng)站
