一澈魄、鎖相關(guān)的知識

1.1 MVCC

Innodb中是基于MVCC實(shí)現(xiàn)的景鼠，MVCC(Multi-Version Concurrency Control) ，多版本并發(fā)控制協(xié)議痹扇。MVCC最大的好處铛漓，相信也是耳熟能詳：讀不加鎖，讀寫不沖突鲫构。MVCC中分為快照讀和當(dāng)前讀浓恶。快照讀结笨，讀取的是記錄的可見版本 (有可能是歷史版本)包晰，不用加鎖。當(dāng)前讀炕吸，讀取的是記錄的最新版本伐憾，并且，當(dāng)前讀返回的記錄赫模，都會加上鎖树肃，保證其他事務(wù)不會再并發(fā)修改這條記錄。

1.1.1 快照讀

• 簡單的select語句是走的快照讀瀑罗，如select * from table where

• 是根據(jù)undo日志來實(shí)現(xiàn)的胸嘴，記錄了多個快照版本雏掠。不同事務(wù)隔離級別下選擇快照版本的方法是不一樣的，RC級別下選擇的是最新的快照版本劣像，RR級別下選擇的是事務(wù)剛開始時的那個快照版本乡话，RR級別下事務(wù)中多次簡單的查詢結(jié)果是一致的。

1.1.2 當(dāng)前讀

• 除了簡單的select語句驾讲，其他的sql都是走的當(dāng)前讀

  • select * from table where ? lock in share mode;

  • select * from table where ? for update;

  • insert into table values (…);

  • update table set ? where ?;

  • delete from table where ?;

• 所有以上的語句蚊伞，都屬于當(dāng)前讀，讀取記錄的最新版本吮铭。并且时迫，讀取之后，還需要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄谓晌，對讀取記錄加鎖掠拳。其中，除了第一條語句纸肉，對讀取記錄加S鎖 (共享鎖)外溺欧，其他的操作，都加的是X鎖 (排它鎖)柏肪。

  
  
  122894569.jpg
  
  

  從圖中姐刁，可以看到，一個Update操作的具體流程烦味。當(dāng)Update SQL被發(fā)給MySQL后聂使，MySQL Server會根據(jù)where條件，讀取第一條滿足條件的記錄谬俄，然后InnoDB引擎會將第一條記錄返回柏靶，并加鎖 (current read)沪编。待MySQL Server收到這條加鎖的記錄之后雏搂，會再發(fā)起一個Update請求象颖，更新這條記錄冯遂。一條記錄操作完成，再讀取下一條記錄叶摄，直至沒有滿足條件的記錄為止笙瑟。因此述吸，Update操作內(nèi)部算灸，就包含了一個當(dāng)前讀返吻。同理，Delete操作也一樣乎婿。Insert操作會稍微有些不同，簡單來說街佑，就是Insert操作可能會觸發(fā)Unique Key的沖突檢查谢翎，也會進(jìn)行一個當(dāng)前讀捍靠。

1.2 鎖模式
討論鎖的話，先確定幾點(diǎn)森逮。鎖存在的前提是事務(wù)開啟或者AUTOCOMMIT=0榨婆，否則都不存在鎖。鎖是加在索引上褒侧，如果表沒有設(shè)置任何索引良风，最終會鎖在InnoDb自帶的隱式主鍵。索引的實(shí)現(xiàn)不在這里細(xì)說闷供。

講鎖之前烟央，先拋一個重要的論點(diǎn)，用大白話講歪脏，鎖就是鎖住一些滿足條件的資源疑俭，防止其他操作改變這些資源的狀態(tài)。記錄鎖分為S鎖(共享鎖)和X鎖(排他鎖)

1.2.1 S鎖

共享鎖婿失，允許事務(wù)讀一行數(shù)據(jù)钞艇。一個事務(wù)擁有了某條記錄的共享鎖之后，其他事務(wù)也可以申請這條記錄的共享鎖豪硅。

1.2.2 X鎖

排他鎖哩照，允許事務(wù)修改一行數(shù)據(jù)。一個事務(wù)擁有某條記錄排它鎖后懒浮，其他事務(wù)不能申請任何鎖飘弧。排它鎖與任何鎖都是互斥的。

1.2.3 鎖的兼容性

1.3 意向鎖

如果只有X鎖和S鎖的話嵌溢，會存在性能問題眯牧。考慮以下場景：

事務(wù)B先對表dealbasic中某一行n加了X鎖赖草，事務(wù)A再向表dealbasic加X鎖学少，

那么這種情況下事務(wù)A即使持有了dealbasic的表鎖，但是表中的數(shù)據(jù)行n仍然會被其他事務(wù)所修改秧骑，同理事務(wù)B即使持有了行n的X鎖版确，但是行n的數(shù)據(jù)仍然會被其他事務(wù)所修改。

為了防止這種情況乎折，事務(wù)A在向表加X鎖前就必需檢查表中的每一行是否有被其他事務(wù)加鎖绒疗，這一過程十分耗性能。

因此innoDB中提供了意向鎖機(jī)制骂澄，意向鎖僅有表級別的鎖粒度吓蘑，innoDB中提供了兩種意向鎖：意向共享鎖(IS) 和 意向排他鎖(IX)，意向鎖與X鎖及S鎖的兼容性如下：

上述場景中，事務(wù)B對dealbasic加行級X鎖前需要先對表加IX鎖磨镶，之后事務(wù)A再向dealbasic加X鎖時由于X鎖與IX鎖不兼容溃蔫，因此加鎖不會成功

1.4 鎖算法

以下以first表為例，表結(jié)構(gòu)為 first( primary id, uniq un, key num, name);

CREATE TABLE `first` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主鍵',
  `name` varchar(40) DEFAULT NULL COMMENT '名字',
  `num` int(10) unsigned DEFAULT '0',
  `un` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un` (`un`),
  KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第一張表'

表中記錄如下:

122919469.png

1.4.1 record lock

記錄鎖琳猫，就是某一條記錄上的鎖伟叛，分為S鎖和X鎖。 比如說上圖中id=20那一行

122898795.png

SELECT * FROM first WHERE id = 20 --不會加任何鎖
SELECT * FROM first WHERE id = 20  LOCK IN SHARE MODE -- 對id=20加record lock S鎖
SELECT * FROM first WHERE id = 20 FOR UPDATE -- 對id=20加record lock X鎖

1.4.2 gap lock

間歇鎖脐嫂，是鎖住一個范圍统刮，不允許新插入數(shù)據(jù)。目標(biāo)是保證不能插入新的滿足查詢條件的數(shù)據(jù)账千。間歇鎖不是X鎖侥蒙，可以同一區(qū)間的多個間歇鎖共存。如上圖中16到20這個區(qū)間蕊爵，不包括16和20這兩條記錄

122954129.png

間隙鎖只有在事務(wù)級別是RR時存在辉哥，間隙鎖存在的所有目的都是為了防止幻讀

如 SELECT * FROM first WHERE id > 16 AND id < 20 FOR UPDATE 會加上述(16,20)的gap lock

其他事務(wù)無法在這個范圍內(nèi)插入數(shù)據(jù)，因此保證同一事務(wù)再次執(zhí)行上述SQL時不會幻讀

1.4.3 next key lock

N鎖攒射，gap lock + record lock醋旦，鎖住一個范圍，并且鎖定記錄本身会放。如上面兩個例子的結(jié)合饲齐。所謂next是指滿足條件的記錄范圍內(nèi)最右記錄加上記錄鎖，左開右閉咧最。上圖中可以產(chǎn)生的N鎖有(6,15] (15,16] (16,20]捂人。和next key lock對應(yīng)的是previous key lock，左閉右開矢沿。[6,15) [15,16) [16,20)滥搭。 InnoDB用的是N鎖

1.5 事務(wù)、鎖相關(guān)的表

• 事務(wù)和鎖相關(guān)信息表所在位置
  use information_schema;
• 查看隔離級別
  select @@tx_isolation;

  mysql> select @@tx_isolation;
  +-----------------+
  | @@tx_isolation  |
  +-----------------+
  | REPEATABLE-READ |
  +-----------------+
  1 row in set (0.01 sec)
  

• 事務(wù)表
  select * from INNODB_TRX \G

mysql> select * from INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 44877
                 trx_state: LOCK WAIT
               trx_started: 2019-01-04 10:39:40
     trx_requested_lock_id: 44877:109:3:2
          trx_wait_started: 2019-01-04 10:39:40
                trx_weight: 2
       trx_mysql_thread_id: 13
                 trx_query: select * from first where num < 13 for update
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 44876
                 trx_state: RUNNING
               trx_started: 2019-01-04 10:38:48
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 12
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 26
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)

• 事務(wù)鎖信息表

select * from INNODB_LOCKS \G

mysql> select * from INNODB_LOCKS\G;
*************************** 1. row ***************************
    lock_id: 44877:109:3:2
lock_trx_id: 44877
  lock_mode: X
  lock_type: RECORD
 lock_table: `zkl`.`first`
 lock_index: PRIMARY
 lock_space: 109
  lock_page: 3
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 44876:109:3:2
lock_trx_id: 44876
  lock_mode: X
  lock_type: RECORD
 lock_table: `zkl`.`first`
 lock_index: PRIMARY
 lock_space: 109
  lock_page: 3
   lock_rec: 2
  lock_data: 1
2 rows in set, 1 warning (0.00 sec)

• 鎖等待表
  select * from INNODB_LOCK_WAITS \G

mysql> select * from INNODB_LOCK_WAITS\G;
*************************** 1. row ***************************
requesting_trx_id: 44877
requested_lock_id: 44877:109:3:2
blocking_trx_id: 44876
blocking_lock_id: 44876:109:3:2
1 row in set, 1 warning (0.00 sec)

二捣鲸、InnoDB隔離級別瑟匆、鎖解決的問題

2.1 MySQL/InnoDB定義的4種隔離級別

Read Uncommited
可以讀取未提交記錄。此隔離級別栽惶，不會使用愁溜，忽略。

Read Committed (RC)
快照讀忽略外厂，本文不考慮冕象。
針對當(dāng)前讀，RC隔離級別保證對讀取到的記錄加鎖 (記錄鎖)汁蝶，存在幻讀現(xiàn)象渐扮。

Repeatable Read (RR)
快照讀忽略，本文不考慮。

針對當(dāng)前讀墓律，RR隔離級別保證對讀取到的記錄加鎖 (記錄鎖)意荤，同時保證對讀取的范圍加鎖，新的滿足查詢條件的記錄不能夠插入 (間隙鎖)只锻，不存在幻讀現(xiàn)象。

Serializable

從MVCC并發(fā)控制退化為基于鎖的并發(fā)控制紫谷。不區(qū)別快照讀與當(dāng)前讀齐饮，所有的讀操作均為當(dāng)前讀，讀加讀鎖 (S鎖)笤昨，寫加寫鎖 (X鎖)祖驱。

Serializable隔離級別下，讀寫沖突瞒窒，因此并發(fā)度急劇下降捺僻，在MySQL/InnoDB下不建議使用。

2.2 RR級別下幻讀解決

幻讀是什么崇裁？事務(wù)中同一查詢語句查出的結(jié)果不一致或者插入不存在記錄時寫入沖突的現(xiàn)象

InnoDB下業(yè)務(wù)可以通過加鎖避免幻讀現(xiàn)象匕坯，并不是InnoDB本身解決了這個問題

普通select由于是快照讀，兩次查詢結(jié)果是一致的

當(dāng)前讀的話拔稳，會對滿足條件的記錄和范圍加N鎖葛峻，這種情況下其他事務(wù)插入不了數(shù)據(jù)，從而避免了幻讀

三巴比、具體sql和鎖分析

分析sql產(chǎn)生的鎖之前需要先確定一些條件术奖，如隔離級別、sql中用到的查詢條件是什么索引轻绞，以下都以RR級別為例分析當(dāng)前讀采记，快照讀無需分析

3.0 加鎖說明

InnoDB下使用的next key lock，會對滿足條件的范圍加gap鎖政勃，記錄加record lock

InnoDB下也做了優(yōu)化唧龄，如果查詢的索引含有唯一屬性時，會降級為record lock稼病，只對滿足條件的記錄加record lock

當(dāng)InnoDB引擎查找不到滿足條件的記錄時选侨，只會加gap lock，gap lock不是X鎖然走，多個gap lock可以共存

3.1 主鍵索引

根據(jù)主鍵索引當(dāng)前讀查詢時援制，因?yàn)橹麈I對應(yīng)的記錄只有一條記錄，那么只會對主鍵索引加record lock芍瑞，其他事務(wù)對該記錄當(dāng)前讀時會被阻塞晨仑，但不影響對其他記錄的當(dāng)前讀

select * from first where id = 20 for update \G

122919469.png

122898795.png

122968805.png

3.2 唯一索引

根據(jù)唯一索引當(dāng)前讀時，會對唯一索引加record lock，同時也會對該記錄的主鍵索引加record lock

select * from first where un = 20 for update \G

122969572.png

3.3 普通索引

根據(jù)普通索引當(dāng)前讀時洪己，除了對查詢記錄的普通索引加record lock妥凳，還會在普通索引前后加gap lock，另外也會對查詢記錄上主鍵索引加record lock, 唯一索引上加uniq key

select * from first where num = 15 for update;

這條語句加的鎖有 num=15的record lock答捕，id=15的record lock逝钥，un=15的record lock，num處于(0拱镐，15)的gap lock艘款，num處于(15，16)的gap lock

123024868.png

3.4 非索引

非索引的情況下沃琅，會對全表做掃描

select  * from first where name = '20'

此時會對所有的記錄加X鎖哗咆，對所有間隙加gap鎖，本例中共25個X鎖和26個gap鎖益眉，相當(dāng)于鎖表晌柬。

3.5 記錄不存在vs記錄存在

3.5.1 主鍵索引不存在

會對主鍵索引值所在的前后區(qū)間加一個gap鎖

select * from first where id = 14 for update;   -- 產(chǎn)生的gap區(qū)間是(6,15)

123025619.png

3.5.2 唯一索引、普通索引等
大家自行測試下

3.6 查詢范圍sql
3.6.1 主鍵索引查詢
會對滿足條件的記錄都會加上record lock郭脂，同時會在各個記錄之間加上gap lock年碘，滿足條件的記錄之前和之后都加上gap鎖。 甚至第一個不滿足條件的記錄還會被加上record lock

select * from first where id > 14 for update;  -- 所有查詢出記錄的record lock朱庆， gap lock盛泡，(6, 15) （52，+∞)  id = 6 沒有加上record lock

123006501.png

select * from first where id < 14 for update; 
 -- 產(chǎn)生的鎖有1娱颊、2傲诵、3、4箱硕、5拴竹、6主鍵索引的record lock，(0剧罩，1）（6栓拜，15）  id = 15也加上了record lock, why?

123080092.png

3.6.2 唯一索引、普通索引查詢等

大家自行測試下

3.7 多條件的復(fù)雜sql

分析時需要對where的條件進(jìn)行拆分惠昔，得出用哪個條件進(jìn)行查詢幕与，哪個條件進(jìn)行篩選。會對InnoDB引擎根據(jù)查詢條件找到的記錄加record lock镇防，即使最終可能會被篩選條件過濾掉啦鸣。where條件拆分可以參考這位大神的博客 http://hedengcheng.com/?p=577

select * from first where id > 16 and id < 20 for update;

122994581.png

四、死鎖

死鎖的原因是来氧，兩個事務(wù)中加鎖的順序不當(dāng)诫给，導(dǎo)致兩個事務(wù)互相等待香拉，無外力作用，無法進(jìn)行下去的情況中狂。最終InnoDB會回滾其中一個凫碌，而讓另外一個可以正常執(zhí)行

• 例1

  
  
  123039030.png

• 例2
  比較經(jīng)典的問題，修改數(shù)據(jù)的話胃榕，直接去刪除然后再插入會出現(xiàn)死鎖的問題盛险。 解決方法是先查詢處理再修改。

  
  
  123061747.png
  
  
  123054357.png