深入了解MySQL中的鎖（全域鎖、表級鎖、行鎖）

這篇文章帶大家了解MySQL中的鎖，介紹一下MySQL的全域鎖、表格級鎖和行鎖，希望對大家有幫助！

根據加鎖的範圍，MySQL裡面的鎖定大致可以分成全域鎖定、表格層級鎖定和行鎖三類

##全域鎖定

全域鎖定就是對整個資料庫實例加鎖。 MySQL提供了一個加上全域讀鎖定的方法，指令是Flush tables with read lock。當需要讓整個庫處於唯讀狀態的時候，可以使用這個指令，之後其他執行緒的以下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括建表、修改表結構等）和更新類別事務的提交語句。 【相關推薦：mysql教學(影片)】

全域鎖定的典型使用場景是，做全庫邏輯備份。也就是把整庫每個表都select出來存成文字

但是讓整個庫都只讀，可能出現以下問題：

• 如果在主庫上備份，那麼在備份期間都無法執行更新，業務基本上就得停擺
• 如果在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的binlog，會導致主從延遲

在可重複讀取隔離等級下開啟一個交易能夠拿到一致性視圖

官方自帶的邏輯備份工具是mysqldump。當mysqldump使用參數–single-transaction的時候，導資料之前就會啟動一個事務，來確保拿到一致性視圖。而由於MVCC的支持，這個過程中數據是可以正常更新的。 single-transaction只適用於所有的表使用事務引擎的函式庫

#1.既然要全庫只讀，為什麼不使用set global readonly=true的方式？

• 在有些系統中，readonly的值會被用來做其他邏輯，例如用來判斷一個函式庫是主函式庫還是備庫。因此修改global變數的方式影響面更大
• 在異常處理機制上有差異。如果執行Flush tables with read lock指令之後因為客戶端發生異常斷開，那麼MySQL會自動釋放這個全域鎖，整個函式庫回到可以正常更新的狀態。而將整個庫設定為readonly之後，如果客戶端發生異常，則資料庫會一直保持readonly狀態，這會導致整個庫長時間處於不可寫入狀態，風險較高

二、表級鎖定

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的操作完成後才能進行更新

1、兩階段鎖定協定

# 事務A所持有的兩個記錄的行鎖都是在commit的時候才釋放的，事務B的update語句會被阻塞，直到事務A執行commit之後，事務B才能繼續執行

在InnoDB事務中，行鎖定是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖定協定

如果事務中需要鎖多個行，要把最可能造成鎖定衝突、最可能影響並發度的鎖定盡量往後放

#假設要實現一個電影票線上交易業務，顧客A要在戲院B購買電影票。業務需要涉及以下操作：

1.從顧客A帳戶餘額中扣除電影票價

2.給戲院B的帳戶餘額增加這部電影票價

#3.記錄一筆交易日誌

為了保證交易的原子性，要把這三個操作放在一個交易中。如何安排這三個語句在事務中的順序呢？

如果同時有另外一個顧客C要在戲院B買票，那麼這兩個事務衝突的部分就是語句2了。因為它們要更新同一個戲院帳戶的餘額，需要修改同一行資料。根據兩階段鎖定協議，所有的操作需要的行鎖都是在事務提交的時候才釋放的。所以，如果把語句2安排在最後，例如依照3、1、2這樣的順序，那麼戲院帳戶餘額這一行的鎖定時間就最少。這就最大程度地減少了事務之間的鎖定等待，提升了並發度

2、死鎖和死鎖檢測

在並發系統中不同執行緒出現循環資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖

事務A在等待事務B釋放id=2的行鎖，而事務B在等待事務A釋放id=1的行鎖。事務A和事務B在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以後，有兩種策略：

• 一種策略是，直接進入等待，直到逾時。這個超時時間可以透過參數innodb_lock_wait_timeout來設定
• 另一個策略是，發起死鎖偵測，發現死鎖後，主動回滾死鎖鏈中的某一個事務，讓其他事務得以繼續執行。將參數innodb_deadlock_detect設為on，表示開啟這個邏輯

在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;
END

1、第一類：查詢長時間不回傳

select * 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進入等待狀態

這類問題的處理方式，就是找到誰持有MDL寫鎖，然後把它kill掉。但由於show processlist的結果裡，sessionA的Command列是Sleep，導致查找起來很不方便，可以透過查詢sys.schema_table_lock_waits這張表直接找出造成阻塞的process id，把這個連接kill指令斷開即可（ MySQL啟動時需要設定performance_schema=on，相較於設定為off會有10%左右的效能損失）

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;

這兩個flush語句，如果指定表t的話，代表的是只關閉表t；如果沒有指定具體的表名，則表示關閉MySQL裡所有打開的表

但是正常情況下這兩個語句執行起來都很快，除非它們被別的線程堵住了

所以，出現Waiting for table flush狀態的可能情況是：有一個flush tables指令被別的語句堵住了，然後它有堵住了select語句

場景復現：

sessionA中，每行呼叫一次sleep(1)，這樣這個語句預設要執行10萬秒，在這段期間表t一直是被sessionA打開著。然後，sessionB的flush tables t再去關閉表t，就需要等sessionA的查詢結束。這樣sessionC要再查詢的話，就會被flush指令堵住了

#3）、等行鎖定

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

由於存取id= 1這個記錄時要加讀鎖，如果這時候已經有一個事務在這行記錄上持有一個寫鎖，select語句就會被堵住

場景復現：

sessionA啟動了事務，佔有寫鎖，還不提交，是導致sessionB被堵住的原因

2、第二類：查詢慢

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进入互相等待状态，形成了死锁

间隙锁的引入可能会导致同样的语句锁住更大的范围，这其实是影响并发度的

在读提交隔离级别下，不存在间隙锁

六、next-key lock

表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);

1、next-key lock加锁规则

• 原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间
• 原则2：查找过程中访问到的对象才会加锁
• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
• 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

这个规则只限于MySQL5.x系列

2、案例一：等值查询间隙锁

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这行是可以的

3、案例二：非唯一索引等值锁

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 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段

4、案例三：主键索引范围锁

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]

5、案例四：非唯一索引范围锁

这次sessionA用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加上(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，因此最终sessionA加的锁是索引c上的(5,10]和(10,15]这两个next-key lock

6、案例五：唯一索引范围锁bug

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的一行，也会被锁住

7、案例六：非唯一索引上存在等值的例子

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上的加锁范围，就是下图中蓝色区域覆盖的部分，这个蓝色区域左右两边都是虚线，表示开区间

8、案例七：limit语句加锁

加了limit 2的限制，因此在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，如下图所示：

再删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围

9、案例八：一个死锁的例子

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);

1、不等号条件里的等值查询

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

2.这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙

3.然后根据order by id desc，再向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]

在执行过程中，通过树搜索的方式定位记录的时候，用的是等值查询的方法

2、等值查询的过程

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。这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁

八、insert语句的锁为什么这么多？

1、insert … select语句

表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;

2、insert循环写入

要往表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。所以，这个语句执行期间，其他事务不能在这个表上插入数据

需要临时表是因为这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符

3、insert唯一键冲突

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都要等待对方的行锁，所以就出现了死锁

4、insert into … on duplicate key update

上面这个例子是主键冲突后直接报错，如果改写成

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;
• 第二种，在一个连接中循环执行20次delete from T limit 500；
• 第三种，在20个连接中同时执行delete from T limit 500；

选择哪一种方式比较好？

参考答案：

第一种方式，单个语句占用时间长，锁的时间也比较长，而且大事务还会导致主从延迟

第三种方式，会人为造成锁冲突

第二种方式相对较好

更多编程相关知识，请访问：编程入门！！

以上是深入了解MySQL中的鎖（全域鎖、表級鎖、行鎖）的詳細內容。更多資訊請關注PHP中文網其他相關文章！