這篇文章帶大家了解MySQL中的鎖，介紹一下MySQL的全域鎖、表格級鎖和行鎖，希望對大家有幫助！
根據加鎖的範圍，MySQL裡面的鎖定大致可以分成全域鎖定、表格層級鎖定和行鎖三類
全域鎖定就是對整個資料庫實例加鎖。 MySQL提供了一個加上全域讀鎖定的方法，指令是Flush tables with read lock。當需要讓整個庫處於唯讀狀態的時候，可以使用這個指令，之後其他執行緒的以下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括建表、修改表結構等）和更新類別事務的提交語句。 【相關推薦：mysql教學(影片)】
全域鎖定的典型使用場景是，做全庫邏輯備份。也就是把整庫每個表都select出來存成文字
但是讓整個庫都只讀，可能出現以下問題：
在可重複讀取隔離等級下開啟一個交易能夠拿到一致性視圖
官方自帶的邏輯備份工具是mysqldump。當mysqldump使用參數–single-transaction的時候，導資料之前就會啟動一個事務，來確保拿到一致性視圖。而由於MVCC的支持，這個過程中數據是可以正常更新的。 single-transaction只適用於所有的表使用事務引擎的函式庫
#1.既然要全庫只讀，為什麼不使用set global readonly=true的方式？
MySQL裡面表格層級的鎖有兩種：一種是表鎖，一種是元資料鎖（meta data lock，MDL）
表格鎖的語法是lock tables … read/write。可以用unlock tables主動釋放鎖，也可以在客戶端斷開的時候自動釋放。 lock tables語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象
#如果在某個線程A中執行lock tables t1 read,t2 wirte;這個語句，則其他執行緒寫t1、讀寫t2的語句都會被阻塞。同時，執行緒A在執行unlock tables之前，也只能執行讀t1、讀寫t2的操作。連寫t1都不允許
另一類表級的鎖定是MDL。 MDL不需要明確使用，在存取一個表格的時候會被自動加上。 MDL的作用是，確保讀寫的正確性。如果一個查詢正在遍歷一個表中的數據，而執行期間另一個線程對這個表結構做了變更，刪了一列，那麼查詢線程拿到的結果跟表結構對不上，肯定不行
#在MySQL5.5版本引進了MDL，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對錶做結構變更操作的時候，加MDL寫鎖定
給一個表加字段，或者修改字段，或者加索引，需要掃描全表的資料。在對大表操作的時候，需要特別小心，以免對線上服務造成影響
session A先啟動，這時候會對錶t加一個MDL讀鎖定。由於session B需要的也是MDL讀鎖，因此可以正常執行。之後sesession C會被blocked，是因為session A的MDL讀鎖還沒有釋放，而session C需要MDL寫鎖，因此只能被阻塞。如果只有session C自己被阻塞還沒什麼關係，但是之後所有要在表t上新申請MDL讀鎖的請求也會被session C阻塞。所有對錶的增刪改查操作都需要先申請MDL讀鎖，就都被鎖住，等於這個表現在完全不可讀寫了
事務中的MDL鎖定，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放
1.如果安全地給小表加字段？
首先要解決長事務，事務不提交，就會一直佔DML鎖定。在MySQL的information_schema函式庫的innodb_trx表中，可以查到目前執行的交易。如果要做DDL變更的表剛好有長事務在執行，要考慮先暫停DDL，或者kill掉這個長事務
2.如果要變更的表是一個熱點表，雖然資料量不大，但是上面的請求很頻繁，而又不得不加個字段，該怎麼做？
在alter table語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到MDL寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。之後再透過重試指令重複這個過程
MySQL的行鎖是在引擎層由各個引擎自己實現的。但不是所有的引擎都支援行鎖，例如MyISAM引擎就不支援行鎖
行鎖就是針對資料表中行記錄的鎖定。例如事務A更新了一行，而這時候事務B也要更新同一行，則必須等事務A的操作完成後才能進行更新
# 事務A所持有的兩個記錄的行鎖都是在commit的時候才釋放的，事務B的update語句會被阻塞，直到事務A執行commit之後，事務B才能繼續執行
在InnoDB事務中，行鎖定是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖定協定
如果事務中需要鎖多個行，要把最可能造成鎖定衝突、最可能影響並發度的鎖定盡量往後放
#假設要實現一個電影票線上交易業務，顧客A要在戲院B購買電影票。業務需要涉及以下操作：
1.從顧客A帳戶餘額中扣除電影票價
2.給戲院B的帳戶餘額增加這部電影票價
#3.記錄一筆交易日誌
為了保證交易的原子性，要把這三個操作放在一個交易中。如何安排這三個語句在事務中的順序呢？
如果同時有另外一個顧客C要在戲院B買票，那麼這兩個事務衝突的部分就是語句2了。因為它們要更新同一個戲院帳戶的餘額，需要修改同一行資料。根據兩階段鎖定協議，所有的操作需要的行鎖都是在事務提交的時候才釋放的。所以，如果把語句2安排在最後，例如依照3、1、2這樣的順序，那麼戲院帳戶餘額這一行的鎖定時間就最少。這就最大程度地減少了事務之間的鎖定等待，提升了並發度
在並發系統中不同執行緒出現循環資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖
事務A在等待事務B釋放id=2的行鎖，而事務B在等待事務A釋放id=1的行鎖。事務A和事務B在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以後，有兩種策略：
在InnoDB中，innodb_lock_wait_timeout的預設值為50s，表示如果採用第一個策略，當出現死鎖以後，第一個被鎖住的執行緒要過50s才會逾時退出，然後其他執行緒才有可能繼續執行。對於線上服務來說，這個等待時間往往是無法接受的
正常情況下還是要採用主動死鎖檢查策略，而且innodb_deadlock_detect的預設值本身就是on。主動死鎖監測在發生死鎖的時候，是能夠快速發現並進行處理的，但是它有額外負擔的。每當一個事務被鎖的時候，就要看看它所依賴的線程有沒有被別人鎖住，如此循環，最後判斷是否出現了循環等待，也就是死鎖
如果所有事務都要更新同一行的場景，每個新來的被堵住的線程都要判斷會不會因為自己的加入導致死鎖，這是一個時間複雜度是O(n)的操作
#怎麼解決由這種熱點行更新所導致的效能問題？
1.如果確保這個業務一定不會出現死鎖，可以暫時把死鎖偵測關掉
#2.控制並發度
3.將一行改成邏輯上的多行來減少鎖定衝突。以戲院帳戶為例，可以考慮放在多筆記錄上，例如10筆記錄，戲院的帳戶總額等於這10個記錄的數值的總和。這樣每次要給戲院帳戶加金額的時候，隨機選其中一筆記錄來加。這樣每次衝突機率變成員原來的1/10，可以減少鎖等待個數，也就減少了死鎖檢測的CPU消耗
建構一個表，這個表有兩個字段id和c，並且在裡面插入了10萬行記錄
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
declare i int;
set i=1;
while(i<=100000) do
insert into t values(i,i);
set i=i+1;
end while;
ENDselect * from t3 where id=1;
查詢結果長時間不傳回，使用show processlist指令，檢視目前語句處於什麼狀態
1）、等MDL鎖定
#如下圖所示，使用show processlist;指令查看Waiting for table metadata lock的示意圖
##這個狀態表示現在有一個執行緒正在表t上請求或持有MDL寫鎖，把select語句堵住了
場景複現：
sessionA透過lock table指令持有表t的MDL寫鎖，而sessionB的查詢需要取得MDL讀鎖。所以，sessionB進入等待狀態
select blocking_pid from sys.schema_table_lock_waits;
2）、等flush##在表t上執行如下的SQL語句：
select * from information_schema.processlist where id=1;
查出來某個執行緒狀態為Waiting for table flush
這個狀態表示的是，現在有一個執行緒政要對錶t做flush操作。 MySQL裡面對錶做flush操作的用法，一般有以下兩個：
flush tables t with read lock;flush tables with read lock;
但是正常情況下這兩個語句執行起來都很快，除非它們被別的線程堵住了
所以，出現Waiting for table flush狀態的可能情況是：有一個flush tables指令被別的語句堵住了，然後它有堵住了select語句
場景復現： sessionA中，每行呼叫一次sleep(1)，這樣這個語句預設要執行10萬秒，在這段期間表t一直是被sessionA打開著。然後，sessionB的flush tables t再去關閉表t，就需要等sessionA的查詢結束。這樣sessionC要再查詢的話，就會被flush指令堵住了
select * from t where id=1 lock in share mode;
sessionA啟動了事務，佔有寫鎖，還不提交，是導致sessionB被堵住的原因
sessionA先用start transaction with consistent snapshot指令開啟一個事務，建立事務的一致性讀（又稱為快照讀。使用的是MVCC機制讀取undo log中的已經提交的資料。所以它的讀取是非阻塞的），之後sessionB執行update語句
sessionB執行完100萬次update語句後，產生100萬個回滾日誌
带lock in share mode的语句是当前读，因此会直接读到1000001这个结果，速度很快；而select * from t where id=1这个语句是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回
建表和初始化语句如下：
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);
这个表除了主键id外，还有一个索引c
为了解决幻读问题，InnoDB引入了间隙锁，锁的就是两个值之间的空隙
当执行select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录
行锁分成读锁和写锁
跟间隙锁存在冲突关系的是往这个间隙中插入一个记录这个操作。间隙锁之间不存在冲突关系
这里sessionB并不会被堵住。因为表t里面并没有c=7会这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们用共同的目标，保护这个间隙，不允许插入值。但它们之间是不冲突的
间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。因为+∞是开区间，在实现上，InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合都是前开后闭区间
间隙锁和next-key lock的引入，解决了幻读的问题，但同时也带来了一些困扰
间隙锁导致的死锁：
1.sessionA执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10)
2.sessionB执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突
3.sessionB试图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待
4.sessionA试图插入一行(9,9,9)，被sessionB的间隙锁挡住了
两个session进入互相等待状态，形成了死锁
间隙锁的引入可能会导致同样的语句锁住更大的范围，这其实是影响并发度的
在读提交隔离级别下，不存在间隙锁
表t的建表语句和初始化语句如下：
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);
这个规则只限于MySQL5.x系列<=5.7.24，8.0系列<=8.0.13
1.由于表t中没有id=7的记录，根据原则1，加锁单位是next-key lock，sessionA加锁范围就是(5,10]
2.根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)
所以，sessionB要往这个间隙里面插入id=8的记录会被锁住，但是sessionC修改id=10这行是可以的
1.根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock
2.c是普通索引，因此访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock
3.根据优化2，等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)
4.根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有任何锁，这就是为什么sessionB的update语句可以执行完成
锁是加在索引上的，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁，这样的话sessionB的update语句会被阻塞住。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段
1.开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁
2.范围查询就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]
所以，sessionA这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]
这次sessionA用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加上(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，因此最终sessionA加的锁是索引c上的(5,10]和(10,15]这两个next-key lock
sessionA是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了
但是实现上，InnoDB会扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上
所以，sessionB要更新id=20这一行是会被锁住的。同样地，sessionC要插入id=16的一行，也会被锁住
insert into t values(30,10,30);
新插入的这一行c=10，现在表里有两个c=10的行。虽然有两个c=10，但是它们的主键值id是不同的，因此这两个c=10的记录之间也是有间隙的
sessionA在遍历的时候，先访问第一个c=10的记录。根据原则1，这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。然后sessionA向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10)到(c=15,id=15)的间隙锁
也就是说，这个delete语句在索引c上的加锁范围，就是下图中蓝色区域覆盖的部分，这个蓝色区域左右两边都是虚线，表示开区间
加了limit 2的限制，因此在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，如下图所示：
再删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围
1.sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15)
2.sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待
3.然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB让sessionB回滚
sessionB的加next-key lock(5,10]操作，实际上分成了两步，先是加(5,10)间隙锁，加锁成功；然后加c=10的行锁，这时候才被锁住的
表t的建表语句和初始化语句如下：
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);
begin; select * from t where id>9 and id<12 order by id desc for update;
利用上面的加锁规则，这个语句的加锁范围是主键索引上的(0,5]、(5,10]和(10,15)。加锁单位是next-key lock，这里用到了优化2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁(10,15)
1.首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个id<12的值
2.这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙
3.然后根据order by id desc，再向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]
在执行过程中，通过树搜索的方式定位记录的时候，用的是等值查询的方法
begin; select id from t where c in(5,20,10) lock in share mode;
这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的
在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。执行c=10会这个逻辑的时候，加锁的范围是(5,10]和(10,15)，执行c=20这个逻辑的时候，加锁的范围是(15,20]和(20,25)
这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁
select id from t where c in(5,20,10) order by c desc for update;
由于语句里面是order by c desc，这三个记录锁的加锁顺序是先锁c=20，然后c=10，最后是c=5。这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁
表t和t2的表结构、初始化数据语句如下：
CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(null, 1,1); insert into t values(null, 2,2); insert into t values(null, 3,3); insert into t values(null, 4,4); create table t2 like t;
在可重复读隔离级别下，binlog_format=statement时执行下面这个语句时，需要对表t的所有行和间隙加锁
insert into t2(c,d) select c,d from t;
要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1，SQL语句如下：
insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
这个语句的加锁范围，就是表t索引c上的(3,4]和(4,supermum]这两个next-key lock，以及主键索引上id=4这一行
执行流程是从表t中按照索引c倒序吗，扫描第一行，拿到结果写入到表t2中，因此整条语句的扫描行数是1
但如果要把这一行的数据插入到表t中的话：
insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
explain结果中的Extra字段中Using temporary字段，表示这个语句用到了临时表
执行流程如下：
1.创建临时表，表里有两个字段c和d
2.按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表
3.由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中
这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据
需要临时表是因为这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符
sessionA执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁，sessionA持有索引c上的(5,10]共享next-key lock（读锁）
在sessionA执行rollback语句回滚的时候，sessionC几乎同时发现死锁并返回
1.在T1时刻，启动sessionA，并执行insert语句，此时在索引c的c=5上加了记录锁。这个索引是唯一索引，因此退化为记录锁
2.在T2时刻，sessionA回滚。这时候，sessionB和sessionC都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁
上面这个例子是主键冲突后直接报错，如果改写成
insert into t values(11,10,10) on duplicate key update d=100;
就会给索引c上(5,10]加一个排他的next-key lock（写锁）
insert into … on duplicate key update的语义逻辑是，插入一行数据，如果碰到唯一键约束，就继续执行后面的更新语句。如果有多个列违反了唯一性索引，就会按照索引的顺序，修改跟第一个索引冲突的行
表t里面已经有了(1,1,1)和(2,2,2)这两行，执行这个语句效果如下：
主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行
1、如果要删除一个表里面的前10000行数据，有以下三种方法可以做到：
delete from T limit 10000;
delete from T limit 500；
delete from T limit 500；
选择哪一种方式比较好？
参考答案：
第一种方式，单个语句占用时间长，锁的时间也比较长，而且大事务还会导致主从延迟
第三种方式，会人为造成锁冲突
第二种方式相对较好
更多编程相关知识，请访问：编程入门！！
以上是深入了解MySQL中的鎖（全域鎖、表級鎖、行鎖）的詳細內容。更多資訊請關注PHP中文網其他相關文章！
