小編給大家分享一下MySQL中鎖及加鎖規(guī)則是什么，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
長樂網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)公司！從網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、響應(yīng)式網(wǎng)站設(shè)計(jì)等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營維護(hù)。創(chuàng)新互聯(lián)公司于2013年創(chuàng)立到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)公司。
MySQL 引入鎖的目的是為了解決并發(fā)寫的問題，比如兩個(gè)事務(wù)同時(shí)對(duì)同一條記錄進(jìn)行寫操作，如果允許它們同時(shí)進(jìn)行，那就會(huì)產(chǎn)生臟寫的問題，這是任何一種隔離級(jí)別都不允許發(fā)生的異常情況，而鎖的作用就是讓兩個(gè)并發(fā)寫操作按照一定的順序執(zhí)行，避免臟寫問題。
首先申明本文中所使用到的示例
CREATE TABLE `user` ( `id` int(12) NOT NULL AUTO_INCREMENT, `name` varchar(36) NULL DEFAULT NULL, `age` int(12) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `age`(`age`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1;insert into user values (5,'重塑',5),(10,'達(dá)達(dá)',10),(15,'刺猬',15);復(fù)制代碼
本文所述示例都是在 MySQL InnoDB 存儲(chǔ)引擎以及可重復(fù)讀（Repeatable Read）隔離級(jí)別下。
從鎖的粒度來看，MySQL 中的鎖可以分為全局鎖、表級(jí)鎖和行鎖三種。
全局鎖會(huì)將整個(gè)數(shù)據(jù)庫都加上鎖，此時(shí)數(shù)據(jù)庫將處于只讀狀態(tài)，任何修改數(shù)據(jù)庫的語句，包括 DDL（Data Definition Language）及增刪改的 DML（Data Manipulation Language）語句都將被阻塞，直到數(shù)據(jù)庫全局鎖釋放。
最常使用到全就鎖的地方就是進(jìn)行全庫備份，我們可以通過以下的語句實(shí)現(xiàn)全局鎖的加鎖與釋放鎖操作：
-- 加全局鎖flush tables with read lock;-- 釋放全局鎖unlock table;復(fù)制代碼
若客戶端鏈接斷開，也會(huì)自動(dòng)釋放全局鎖。
表級(jí)鎖會(huì)將整張表加上鎖，MySQL 中的表級(jí)鎖有：表鎖、元數(shù)據(jù)鎖（Meta Data Lock）、意向鎖（Intention Lock）和自增鎖（AUTO-INC Lock）。
表鎖的加鎖和釋放鎖方式：
重慶分公司
重慶分公司
lock table tableName read/write;unlock table;需要注意的是，表鎖的加鎖也限制了同一個(gè)客戶端鏈接的操作權(quán)限，如加了表級(jí)讀鎖（lock table user read），那么在同一個(gè)客戶端鏈接中在釋放表級(jí)讀鎖以前，對(duì)同一張表（user 表）也只能進(jìn)行讀操作，無法進(jìn)行寫操作，而其他客戶端鏈接對(duì)該表（user 表）只能進(jìn)行讀操作，無法進(jìn)行寫操作。
如加了表級(jí)寫鎖（lock table user write），在同一個(gè)客戶端鏈接中可對(duì)表進(jìn)行讀寫操作，而其他客戶端鏈接既無法進(jìn)行讀操作也無法進(jìn)行寫操作。
第二種表級(jí)鎖是元數(shù)據(jù)鎖（MDL, Meta Data Lock），元數(shù)據(jù)鎖會(huì)在客戶端訪問表的時(shí)候自動(dòng)加鎖，在客戶端提交事務(wù)時(shí)釋放鎖，它防止了以下場景出現(xiàn)的問題：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user; | |
| alter table user add column birthday datetime; | |
| select * from user; |
如上表，sessionA 開啟了一個(gè)事務(wù)，并進(jìn)行一次查詢，在這之后另外一個(gè)客戶端 sessionB 給 user 表新增了一個(gè) birthday 字段，然后 sessionA 再進(jìn)行一次查詢，如果沒有元數(shù)據(jù)鎖，就可能會(huì)出現(xiàn)在同一個(gè)事務(wù)中，前后兩次查詢到的記錄，表字段列數(shù)不一致的情況，這顯然是需要避免的。
DDL 操作對(duì)表加的是元數(shù)據(jù)寫鎖，對(duì)其他事務(wù)的元數(shù)據(jù)讀寫鎖都不兼容；DML 操作對(duì)表加的是元數(shù)據(jù)讀鎖，可與其他事務(wù)的元數(shù)據(jù)讀鎖共享，但與其他事務(wù)的元數(shù)據(jù)寫鎖不兼容。
第三種表級(jí)鎖是意向鎖，它表示事務(wù)想要獲取一張表中某幾行的鎖（共享鎖或排它鎖）。
意向鎖是為了避免在表中已經(jīng)存在行鎖的情況下，另一個(gè)事務(wù)去申請(qǐng)表鎖而掃描表中的每一行是否存在行鎖的系統(tǒng)消耗。
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id=5 for update; | |
| flush table user read; |
例如，sessionA 開啟了一個(gè)事務(wù)，并對(duì) id=5 這一行加上了行級(jí)排它鎖，此時(shí) sessionB 將對(duì) user 表加上表級(jí)排它鎖（只要 user 表中有一行被其他事務(wù)持有讀鎖或?qū)戞i即加鎖失敗）。
如果沒有意向鎖，sessionB 將掃描 user 表中的每一行，判斷它們是否被其他事務(wù)加鎖，然后才能得出 sessionB 的此次表級(jí)排它鎖加鎖是否成功。
而有了意向鎖之后，在 sessionB 將對(duì) user 表加鎖時(shí)，會(huì)直接判斷 user 表是否被其他事務(wù)加上了意向鎖，若有則加鎖失敗，若無則可以加上表級(jí)排它鎖。
意向鎖的加鎖規(guī)則：
第四種表級(jí)鎖是自增鎖，這是一種特殊的表級(jí)鎖，只存在于被設(shè)置為 AUTO_INCREMENT 自增列，如 user 表中的 id 列。
自增鎖會(huì)在 insert 語句執(zhí)行完成后立即釋放。同時(shí)，自增鎖與其他事務(wù)的意向鎖可共享，與其他事務(wù)的自增鎖、共享鎖和排它鎖都是不兼容的。
行鎖是由存儲(chǔ)引擎實(shí)現(xiàn)的，從行鎖的兼容性來看，InnoDB 實(shí)現(xiàn)了兩種標(biāo)準(zhǔn)行鎖：共享鎖（Shared Locks，簡稱S鎖）和排它鎖（Exclusive Locks，簡稱X鎖）。
這兩種行鎖的兼容關(guān)系與上面元數(shù)據(jù)鎖的兼容關(guān)系是一樣的，可以用下面的表格表示。
| 事務(wù)A\事務(wù)B | 共享鎖（S鎖） | 排它鎖（X鎖） |
|---|---|---|
| 共享鎖（S鎖） | 兼容 | 沖突 |
| 排它鎖（X鎖） | 沖突 | 沖突 |
而從行鎖的粒度繼續(xù)細(xì)分，又可以分為記錄鎖（Record Lock）、間隙鎖（Gap Lock）、Next-key Lock。
我們一般所說的行鎖都是指記錄鎖，它會(huì)把數(shù)據(jù)庫中的指定記錄行加上鎖。
假設(shè)事務(wù)A中執(zhí)行以下語句（未提交）：
begin;update user set name='達(dá)聞西' where id=5;復(fù)制代碼
InnoDB 至少會(huì)在 id=5 這一行上加一把行級(jí)排它鎖（X鎖），不允許其他事務(wù)操作 id=5 這一行。
需要注意的是，這把鎖是加在 id 列的主鍵索引上的，也就是說行級(jí)鎖是加在索引上的。
假設(shè)現(xiàn)在有另一個(gè)事務(wù)B想要執(zhí)行一條更新語句：
update user set name='大波浪' where id=5;復(fù)制代碼
這時(shí)候，這條更新語句將被阻塞，直到事務(wù)A提交以后，事務(wù)B才能繼續(xù)執(zhí)行。
間隙鎖，顧名思義就是給記錄之間的間隙加上鎖。
需要注意的是，間隙鎖只存在于可重復(fù)讀（Repeatable Read）隔離級(jí)別下。
不知道大家還記不記得幻讀？
幻讀是指在同一事務(wù)中，連續(xù)執(zhí)行兩次同樣的查詢語句，第二次的查詢語句可能會(huì)返回之前不存在的行。
間隙鎖的提出正是為了防止幻讀中描述的幻影記錄的插入而提出的，舉個(gè)例子。
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where age=5;(N1) | |
| insert into user values(2, '大波浪', 5) | |
| update user set name='達(dá)聞西' where age=5; | |
| select * from user where age=5;(N2) |
sessionA 中有兩處查詢N1和N2，它們的查詢條件都是 age=5，唯一不同的是在N2處的查詢前有一條更新語句。
照理說在 RR 隔離級(jí)別下，同一個(gè)事務(wù)中兩次查詢相同的記錄，結(jié)果應(yīng)該是一樣的。但是在經(jīng)過更新語句的當(dāng)前讀查詢后（更新語句的影響行數(shù)是2），N1和N2的查詢結(jié)果并不相同，N2的查詢將 sessionB 插入的數(shù)據(jù)也查出來了，這就是幻讀。
而如果在 sessionA 中的兩次次查詢都用上間隙鎖，比如都改為select * from user where age=5 for update。那么 sessionA 中的當(dāng)前讀查詢語句至少會(huì)將id在(-∞, 5)和(5, 10)之間的間隙加上間隙鎖，不允許其他事務(wù)插入主鍵id屬于這兩個(gè)區(qū)間的記錄，即會(huì)將 sessionB 的插入語句阻塞，直到 sessionA 提交之后，sessionB 才會(huì)繼續(xù)執(zhí)行。
也就是說，當(dāng)N2處的查詢執(zhí)行時(shí)，sessionB 依舊是被阻塞的狀態(tài)，所以N1和N2的查詢結(jié)果是一樣的，都是(5,重塑,5)，也就解決了幻讀的問題。
Next-key Lock 其實(shí)就是記錄鎖與記錄鎖前面間隙的間隙鎖組合的產(chǎn)物，它既阻止了其他事務(wù)在間隙的插入操作，也阻止了其他事務(wù)對(duì)記錄的修改操作。
不知道大家有沒有注意到，我在行鎖部分描述記錄鎖、間隙鎖加鎖的具體記錄時(shí)，用的是「至少」二字，并沒有詳細(xì)說明具體加鎖的是哪些記錄，這是因?yàn)橛涗涙i、間隙鎖和 Next-key Lock 的加鎖規(guī)則是十分復(fù)雜的，這也是本文主要討論的內(nèi)容。
關(guān)于加鎖規(guī)則的敘述將分為三個(gè)方面：唯一索引列、普通索引列和普通列，每一方面又將細(xì)分為等值查詢和范圍查詢兩方面。
需要注意的是，這里加的鎖都是指排它鎖。
在開始之前，先來回顧一下示例表以及表中可能存在的行級(jí)鎖。
mysql> select * from user; +----+--------+------+| id | name | age | +----+--------+------+| 5 | 重塑 | 5 | | 10 | 達(dá)達(dá) | 10 | | 15 | 刺猬 | 15 | +----+--------+------+3 rows in set (0.00 sec)復(fù)制代碼
表中可能包含的行級(jí)鎖首先是每一行的記錄鎖——(5,重塑,5),(10,達(dá)達(dá),5),(15,刺猬,15)。
假設(shè) user 表的索引值有最大值 maxIndex 和最小值 minIndex，user 表還可能存在間隙鎖(minIndex,5),(5,10),(10,15),(15,maxIndex)。
共三個(gè)記錄鎖和四個(gè)間隙鎖。
首先來說唯一索引列的等值查詢，這里的等值查詢可以分為兩種情況：命中與未命中。
當(dāng)唯一索引列的等值查詢命中時(shí)：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id=5 for update; | |
| insert into user values(1,'斯斯與帆',1),(6,'夏日陽光',6),(11,'告五人',11),(16,'面孔',16); | |
| update user set age=18 where id=5;（Blocked） | |
| update user set age=18 where id=10; | |
| update user set age=18 where id=15; |
上表中 sessionB 的執(zhí)行結(jié)果是除了 id=5 行的更新語句被阻塞，其他語句都正常執(zhí)行。
sessionB 中的 insert 語句是為了檢查間隙鎖，update 語句是為了檢查記錄鎖（行鎖）。執(zhí)行結(jié)果表明 user 表的所有間隙都沒有被上鎖，記錄鎖中只有 id=5 這一行被上鎖了。
所以，當(dāng)唯一索引列的等值查詢命中時(shí)，只會(huì)給命中的記錄加鎖。
當(dāng)唯一索引列的等值查詢未命中時(shí)：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id=3 for update; | |
| insert into user values (2,'反光鏡',2);（Blocked） | |
| update user set age=18 where id=5; | |
| insert into user values (6,'夏日陽光',6); | |
| update user set age=18 where id=10; | |
| insert into user values (11,'告五人',11); | |
| update user set age=18 where id=15; | |
| insert into user values (16,'面孔',16); |
上表的執(zhí)行結(jié)果是 sessionB 中 id=2 的記錄插入被阻塞，其他語句正常執(zhí)行。
根據(jù)執(zhí)行結(jié)果可以知道 sessionA 給 user 表加的鎖是間隙鎖(1,5)。
所以，當(dāng)唯一索引列的等值查詢未命中時(shí)，會(huì)給id值所在的間隙加上間隙鎖。
范圍查詢比等值查詢要更復(fù)雜一些，它需要考慮到邊界值存在于表中，以及是否命中邊界值。
首先來看邊界值存在于表中，但未命中的情況：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id<10 for update; | |
| insert into user values (1,'斯斯與帆',1);（Blocked） | |
| update user set age=18 where id=5;（Blocked） | |
| insert into user values (6,'夏日陽光',6);（Blocked） | |
| update user set age=18 where id=10;（Blocked） | |
| insert into user values (11,'告五人',11); | |
| update user set age=18 where id=15; | |
| insert into user values (16,'面孔',16) ; |
此時(shí) sessionA 給 user 表加上的鎖是記錄鎖 id=5,id=10 以及間隙鎖(minIndex,5),(5,10)。
我們知道間隙鎖+記錄鎖就是 Next-key Lock，所以上述的加鎖情況可以看作是兩條 Next-key Lock：(minIndex, 5],(5,10]，即 Next-key Lock —— (minIndex,10]。
當(dāng)邊界值存在于表中，同時(shí)命中的情況：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id<=10 for update; | |
| insert into user values (1,'斯斯與帆',1);（Blocked） | |
| update user set age=18 where id=5;（Blocked） | |
| insert into user values (6,'夏日陽光',6);（Blocked） | |
| update user set age=18 where id=10;（Blocked） | |
| insert into user values (11,'告五人',11);（Blocked） | |
| update user set age=18 where id=15;（Blocked） | |
| insert into user values (16,'面孔',16) ; |
此時(shí) sessionA 給 user 表加上的鎖是Next-key Lock —— (minIndex,15]。
當(dāng)邊界值不存在于表中時(shí)，不可能命中，故只有未命中一種情況：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id<=9 for update; | |
| insert into user values (1,'斯斯與帆',1);（Blocked） | |
| update user set age=18 where id=5;（Blocked） | |
| insert into user values (6,'夏日陽光',6);（Blocked） | |
| update user set age=18 where id=10;（Blocked） | |
| insert into user values (11,'告五人',11); | |
| update user set age=18 where id=15; | |
| insert into user values (16,'面孔',16) ; |
此時(shí) sessionA 給 user 表加上的鎖是 Next-key Lock —— (minIndex,10]，與第一種情況一樣。
綜上所述，在對(duì)唯一索引進(jìn)行范圍查詢時(shí)：
Next-key Lock 加到第一個(gè)邊界之外的記錄上）需要注意的是，第一條中所說的間隙指的是，邊界值所在的間隙，如間隙為(5,10)，查詢條件為 id>7 時(shí)，這個(gè)間隙鎖就是(5,10)，而不是(7,10)。
第二條舉例1：查詢條件為 id<10，第一個(gè)邊界之外的記錄是 id=10，所以
Next-key Lock鎖會(huì)加到 id=10 的記錄上，被鎖住的范圍是(minIndex,10]。
第二條舉例2：查詢條件為 id<=10，第一個(gè)邊界之外的記錄是 id=15，所以
Next-key Lock鎖會(huì)加到 id=15 的記錄上，被鎖住的范圍是(minIndex,15]。
第二條舉例3：查詢條件為 id>10，第一個(gè)邊界之外的記錄是 id=10，
Next-key Lock鎖會(huì)加到 id=10 的記錄上，由于Next-key Lock鎖指的是記錄以左的部分，所以被鎖住的范圍是(5,maxIndex]。
普通索引與唯一索引的區(qū)別就在于唯一索引可以根據(jù)索引列確定唯一性，所以等值查詢的加鎖規(guī)則也有不同之處。
給 user 表再加一條記錄:
INSERT INTO user VALUES (11, '達(dá)達(dá)2.0', 10);復(fù)制代碼
這時(shí) user 表的索引 age 結(jié)構(gòu)如下圖所示：
在索引 age 中可能存在的行鎖是4個(gè)記錄鎖以及5個(gè)間隙鎖。
先來看索引 age 上的加鎖情況：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where age=10 for update; | |
| insert into user values (2,'達(dá)達(dá)',2); | |
| update user set name='痛仰' where age=5; | |
| insert into user values (6,'達(dá)達(dá)',6);（Blocked） | |
| update user set name='痛仰' where age=10 and id=10;（Blocked） | |
| update user set name='痛仰' where age=10 and id=16;）（Blocked） | |
| insert into user values (17,'達(dá)達(dá)',10);（Blocked） | |
| insert into user values (11,'達(dá)達(dá)',11);（Blocked） | |
| update user set name='痛仰' where age=15; | |
| insert into user values (16,'面孔',16) ; |
由上表的語句及執(zhí)行結(jié)果來看，索引 age 上的加鎖情況是：
即索引 age 上的加鎖區(qū)域?yàn)?5, 15)。
由于普通索引無法確定記錄的唯一性，所以普通索引列等值查詢中，為索引 age 加鎖時(shí)，會(huì)找到第一個(gè)age小于10的值（即5）和第一個(gè)age大于10的值（即15），在這個(gè)范圍內(nèi)的間隙加上間隙鎖，記錄加上記錄鎖。
這是索引 age 上的加鎖情況，由于查詢語句是查詢記錄的所有列，根據(jù)查詢規(guī)則，會(huì)通過索引 age 上對(duì)應(yīng)的 id 值到主鍵索引樹上進(jìn)行回表操作，得到所有列，所以主鍵索引上也會(huì)加鎖。在這里，滿足 age=10 的記錄的主鍵id分別是10和16，所以在主鍵索引上這兩行也會(huì)被加上排它鎖。
即，普通索引列等值查詢如果需要回表，滿足條件的記錄對(duì)應(yīng)的主鍵也會(huì)被加上記錄鎖。
這里如果把
sessionA中的查詢改為select id from user where age=10 lock in share mode;，則會(huì)因?yàn)楦采w索引優(yōu)化而不進(jìn)行回表操作，所以主鍵索引上也不會(huì)加鎖。
這里需要額外提一提 limit 這個(gè)語法，它的加鎖范圍（只討論普通索引）要更小一些，請(qǐng)看示例：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where age=10 limit 1 for update; | |
| insert into user values (2,'達(dá)達(dá)',2); | |
| update user set name='痛仰' where age=5; | |
| insert into user values (6,'達(dá)達(dá)',6);（Blocked） | |
| update user set name='痛仰' where age=10 and id=10;（Blocked） | |
| update user set name='痛仰' where age=10 and id=16;） | |
| insert into user values (17,'達(dá)達(dá)',10); | |
| insert into user values (11,'達(dá)達(dá)',11); | |
| update user set name='痛仰' where age=15; | |
| insert into user values (16,'面孔',16) ; |
可以看到，與沒有加 limit 相比，多了兩條 insert 語句順利執(zhí)行了。
由上表的語句及執(zhí)行結(jié)果來看，索引 age 上的加鎖情況是：
由此可見：limit 語法只會(huì)將鎖加到滿足條件的記錄，能夠減小加鎖范圍。
接下來看普通索引列上的范圍查詢（這里只討論索引 age 的加鎖范圍，主鍵索引的加鎖如果存在回表會(huì)鎖住對(duì)應(yīng)的id值）：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where age>8 and age<=12 for update; | |
| insert into user values (2,'達(dá)達(dá)',2); | |
| update user set name='痛仰' where age=5; | |
| insert into user values (6,'達(dá)達(dá)',6);（Blocked） | |
| update user set name='痛仰' where age=10 and id=10;（Blocked） | |
| update user set name='痛仰' where age=10 and id=16;（Blocked） | |
| insert into user values (17,'達(dá)達(dá)',10);（Blocked） | |
| insert into user values (11,'達(dá)達(dá)',11);（Blocked） | |
| update user set name='痛仰' where age=15;（Blocked） | |
| insert into user values (16,'面孔',16) ; |
與普通索引列等值查詢不同的是，范圍查詢比等值查詢多了一個(gè) age=15 的記錄鎖。
這個(gè)邊界值與唯一索引列范圍查詢的原理是一樣的，可以參照上文所述來理解，這里不多加贅述了。
《MySQL實(shí)戰(zhàn)45講》的作者丁奇認(rèn)為這是一個(gè) BUG，但并未被官方接收，如果要深究這個(gè)邊界值的原理，可能就需要看 MySQL 的源碼了。
select * from user where age=10 for update;select * from user where age>=10 and age<11 for update;select id from user where age>=10 and age<11 for update;復(fù)制代碼
以上是MySQL中鎖及加鎖規(guī)則是什么的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
