MySQL 數(shù)據(jù)庫 鎖

MySQL8.0 InnoDb存儲引擎

• 鎖

  1. 樂觀鎖與悲觀鎖
  2. 共享鎖與排他鎖
  3. 死鎖
  4. 間隙鎖與行鎖升級為表鎖

  鎖	innodb支持	加鎖速度	粒度	開銷	并發(fā)度	死鎖
  * 行鎖	是	慢	小	大	高	是
  頁鎖	BDB引擎 否	中	中	中	中	是
  表鎖	是	快	大	小	低	否

  樂觀鎖：總是認(rèn)為不會產(chǎn)生并發(fā)問題械姻，每次去取數(shù)據(jù)的時候總認(rèn)為不會有其他線程對數(shù)據(jù)進行修改，因此不會上鎖，但是在更新時會判斷其他線程在這之前有沒有對數(shù)據(jù)進行修改植袍，一般會使用版本號機制或CAS操作實現(xiàn)。

  悲觀鎖：總是假設(shè)最壞的情況，每次取數(shù)據(jù)時都認(rèn)為其他線程會修改，所以都會加鎖（讀鎖纬傲、寫鎖、行鎖等）肤频，當(dāng)其他線程想要訪問數(shù)據(jù)時叹括，都需要阻塞掛起。

  共享鎖：又稱為讀鎖宵荒，簡稱（S）鎖领猾，顧名思義，共享鎖就是多個事務(wù)對于同一數(shù)據(jù)可以共享一把鎖骇扇，都能訪問到數(shù)據(jù)，但是只能讀不能修改面粮。

  排他鎖：又稱為寫鎖少孝，簡稱（X）鎖，顧名思義熬苍，排他鎖就是不能與其他所并存稍走，如一個事務(wù)獲取了一個數(shù)據(jù)行的排他鎖袁翁，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖婿脸，但是獲取排他鎖的事務(wù)是可以對數(shù)據(jù)就行讀取和修改粱胜。

  <a title="死鎖">死鎖</a>：是指兩個或兩個以上的進程在執(zhí)行過程中,因爭奪資源而造成的一種互相等待的現(xiàn)象,若無外力作用，它們都將無法推進下去.此時稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖狐树，這些永遠在互相等待的進程稱為死鎖進程焙压。
  https://blog.csdn.net/weixin_44337261/article/details/108970710

  間隙鎖：當(dāng)我們采用范圍條件查詢數(shù)據(jù)時，InnoDB 會對這個范圍內(nèi)的數(shù)據(jù)進行加鎖抑钟。比如有 id 為：1涯曲、3、5在塔、7 的 4 條數(shù)據(jù)幻件，我們查找 1-7 范圍的數(shù)據(jù)。那么 1-7 都會被加上鎖蛔溃。2绰沥、4、6 也在 1-7 的范圍中贺待，但是不存在這些數(shù)據(jù)記錄徽曲，這些 2、4狠持、6 就被稱為間隙疟位。

  當(dāng)索引失效的時候，行鎖會升級成表鎖喘垂，索引失效的其中一個方法是對索引自動 or 手動的換型甜刻。a 字段本身是 integer，我們加上引號正勒，就變成了 String得院，這個時候索引就會失效了。

  Navicat 客戶端中演示

  CREATE TABLE `user` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '名字',
    `age` int DEFAULT '0' COMMENT '年齡',
    `gender` tinyint(1) DEFAULT '1' COMMENT '性別',
    `version` int DEFAULT '0' COMMENT '版本',
    PRIMARY KEY (`id`),
    KEY `index_name` (`name`),
    KEY `index_age` (`age`)
  ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  

  -- 樂觀鎖
  set @id = (SELECT version FROM `user` WHERE id = 1);
  
  -- 在此更新之前章贞，其他的事務(wù)有改變此記錄 (版本自增了)【另一個窗口中打開】
  -- UPDATE `user` SET version = version + 1;
  
  -- 延時操作
  select SLEEP(5);
  
  UPDATE `user` SET gender = gender + 1 WHERE id = 1 AND version = (SELECT @id);
  

  	讀鎖	寫鎖
  讀鎖	YES	NO
  寫鎖	NO	NO

  ### 驗證讀寫鎖優(yōu)先級 ###
  BEGIN;
  SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
  
  -- SELECT * FROM `user` WHERE id = 1 lock in SHARE mode;
  -- update `user` SET version = version + 1 WHERE id  = 1;
  
  SELECT SLEEP(5);
  -- COMMIT;
  
  ### A會話加鎖之后祥绞，A會話可以寫，其他會話無法執(zhí)行 寫 操作
  UPDATE `user` SET version = version + 1 WHERE id  = 1;
  DELETE FROM `user` WHERE id = 1;
  

  ### 死鎖 ###
  -- 先執(zhí)行
  BEGIN;
  SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
  
  SELECT SLEEP(5);
  
  SELECT * FROM `user` WHERE id = 2 FOR UPDATE;
  
  COMMIT;
  
  -- 后執(zhí)行 ERROR 1213 - Deadlock found when trying to get lock; try restarting transaction [死鎖]
  BEGIN;
  SELECT * FROM `user` WHERE id = 2 FOR UPDATE;
  
  SELECT SLEEP(5);
  
  SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
  
  COMMIT;
  
  ### 間隙鎖 ###
  BEGIN;
  SELECT * FROM `user` WHERE id = 20 FOR UPDATE;
  -- COMMIT;
  
  -- id 20 - 30 區(qū)間會加上間隙鎖
  BEGIN;
  SELECT * FROM `user` WHERE id = 30 FOR UPDATE;
  -- commit;
  
  
  insert into `user` VALUES(32, '張三', 18, 0, 0);
  

• 什么是事務(wù)

  1. 事務(wù)是數(shù)據(jù)庫系統(tǒng)區(qū)別于其他一切文件系統(tǒng)的重要特性之一
  2. 事務(wù)是一組具有原子性的SQL語句鸭限，或是一個獨立的工作單元

  • 一蜕径、事務(wù)的基本要素（ACID）

  1. 原子性（Atomicity）：事務(wù)開始后所有操作，要么全部做完败京，要么全部不做兜喻，不可能停滯在中間環(huán)節(jié)。事務(wù)執(zhí)行過程中出錯赡麦，會回滾到事務(wù)開始前的狀態(tài)朴皆，所有的操作就像沒有發(fā)生一樣帕识。也就是說事務(wù)是一個不可分割的整體，就像化學(xué)中學(xué)過的原子遂铡，是物質(zhì)構(gòu)成的基本單位肮疗。
  2. 一致性（Consistency）：事務(wù)開始前和結(jié)束后，數(shù)據(jù)庫的完整性約束沒有被破壞 扒接。比如A向B轉(zhuǎn)賬伪货，不可能A扣了錢，B卻沒收到珠增。
  3. 隔離性（Isolation）：同一時間超歌，只允許一個事務(wù)請求同一數(shù)據(jù)，不同的事務(wù)之間彼此沒有任何干擾蒂教。比如A正在從一張銀行卡中取錢巍举，在A取錢的過程結(jié)束前，B不能向這張卡轉(zhuǎn)賬凝垛。
  4. 持久性（Durability）：事務(wù)完成后懊悯，事務(wù)對數(shù)據(jù)庫的所有更新將被保存到數(shù)據(jù)庫，不能回滾梦皮。

  • 二炭分、事務(wù)的并發(fā)問題

  1. 臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作剑肯，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)
  2. 不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)捧毛，事務(wù) B 在事務(wù)A多次讀取的過程中，對數(shù)據(jù)作了更新并提交让网，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時呀忧，結(jié)果 不一致。
  3. 幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫中所有學(xué)生的成績從具體分?jǐn)?shù)改為ABCDE等級溃睹，但是系統(tǒng)管理員B就在這個時候插入了一條具體分?jǐn)?shù)的記錄而账，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣因篇，這就叫幻讀泞辐。

• 事務(wù)的隔離級別

查看系統(tǒng)變量配置
    mysqld --verbose --help

• 修改隔離級別

-- set session transaction isolation level  隔離級別參數(shù)
-- set session transaction isolation level read uncommitted; -- 讀未提交
-- set session transaction isolation level read committed; -- 不可重復(fù)讀
-- set session transaction isolation level repeatable read; -- 可重復(fù)讀

show variables like '%isolation'; -- 查看當(dāng)前會話隔離級別

• MVCC

MVCC琅关，全稱Multi-Version Concurrency Control煮岁，即多版本并發(fā)控制。MVCC是一種并發(fā)控制的方法涣易，一般在數(shù)據(jù)庫管理系統(tǒng)中画机，實現(xiàn)對數(shù)據(jù)庫的并發(fā)訪問，在編程語言中實現(xiàn)事務(wù)內(nèi)存新症。

• InnoDB是一個多版本的存儲引擎步氏。它保留有關(guān)已更改行的舊版本的信息以支持事務(wù)功能，例如并發(fā)和回滾徒爹。該信息以稱為回滾段的數(shù)據(jù)結(jié)構(gòu)存儲在撤消表空間中荚醒。請參閱第 15.6.3.4 節(jié)，“撤消表空間”隆嗅。 InnoDB使用回滾段中的信息來執(zhí)行事務(wù)回滾所需的撤消操作界阁。它還使用這些信息來構(gòu)建行的早期版本以實現(xiàn)一致的讀取。請參閱 第 15.7.2.3 節(jié)胖喳，“一致的非鎖定讀取”泡躯。在內(nèi)部，InnoDB為存儲在數(shù)據(jù)庫中的每一行添加三個字段：

• 一個 6 字節(jié)DB_TRX_ID字段指示插入或更新行的最后一個事務(wù)的事務(wù)標(biāo)識符丽焊。此外较剃，刪除在內(nèi)部被視為更新，其中設(shè)置了行中的特殊位以將其標(biāo)記為已刪除粹懒。

• DB_ROLL_PTR稱為滾動指針的 7 字節(jié)字段重付。回滾指針指向?qū)懭牖貪L段的撤消日志記錄凫乖。如果該行被更新确垫，撤消日志記錄包含在更新前重建該行內(nèi)容所需的信息。

• 一個 6 字節(jié)的DB_ROW_ID字段包含一個行 ID帽芽，隨著插入新行而單調(diào)增加删掀。如果 InnoDB自動生成聚集索引，則該索引包含行 ID 值导街。否則披泪，該 DB_ROW_ID列不會出現(xiàn)在任何索引中。

• 1.1 InnDB 中的 MVCC
  InnDB 中每個事務(wù)都有一個唯一的事務(wù) ID搬瑰，記為 transaction_id款票。它在事務(wù)開始時向 InnDB 申請控硼，按照時間先后嚴(yán)格遞增。而每行數(shù)據(jù)其實都有多個版本艾少，這就依賴 undo log 來實現(xiàn)了卡乾。每次事務(wù)更新數(shù)據(jù)就會生成一個新的數(shù)據(jù)版本，并把 transaction_id 記為 row trx_id缚够。同時舊的數(shù)據(jù)版本會保留在 undo log 中幔妨，而且新的版本會記錄舊版本的回滾指針，通過它直接拿到上一個版本谍椅。
  所以误堡，InnDB 中的 MVCC 其實是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的。一列是事務(wù) ID：trx_id雏吭；另一列是回滾指針：roll_pt锁施。

• 2、undo log
  回滾日志保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個版本思恐，可以用于回滾沾谜，同時可以提供多版本并發(fā)控制下的讀（MVCC），也即非鎖定讀胀莹。
  根據(jù)操作的不同基跑，undo log 分為兩種： insert undo log 和 update undo log。

• 2.1 insert undo log
  insert 操作產(chǎn)生的 undo log描焰，因為 insert 操作記錄沒有歷史版本只對當(dāng)前事務(wù)本身可見媳否，對于其他事務(wù)此記錄不可見，所以 insert undo log 可以在事務(wù)提交后直接刪除而不需要進行 purge 操作荆秦。
  purge 的主要任務(wù)是將數(shù)據(jù)庫中已經(jīng) mark del 的數(shù)據(jù)刪除篱竭，另外也會批量回收 undo pages

  所以，插入數(shù)據(jù)時步绸。它的初始狀態(tài)是這樣的：

  
  
  mvcc-1.png

• 2.2 update undo log
  UPDATE 和 DELETE 操作產(chǎn)生的 Undo log 都屬于同一類型：update_undo掺逼。（update 可以視為 insert 新數(shù)據(jù)到原位置，delete 舊數(shù)據(jù)瓤介，undo log 暫時保留舊數(shù)據(jù)）吕喘。事務(wù)提交時放到 history list 上，沒有事務(wù)要用到這些回滾日志刑桑，即系統(tǒng)中沒有比這個回滾日志更早的版本時氯质，purge 線程將進行最后的刪除操作。
  一個事務(wù)修改當(dāng)前數(shù)據(jù)：

  
  
  mvcc-2.png

  另一個事務(wù)修改數(shù)據(jù)：

  
  
  mvcc-3.png

  這樣的同一條記錄在數(shù)據(jù)庫中存在多個版本祠斧，就是上面提到的多版本并發(fā)控制 MVCC闻察。

  另外，借助 undo log 通過回滾可以回到上一個版本狀態(tài)。比如要回到 V1 只需要順序執(zhí)行兩次回滾即可辕漂。

• 3呢灶、read-view
  read view 是 InnDB 在實現(xiàn) MVCC 時用到的一致性讀視圖，用于支持 RC（讀提交）以及 RR（可重復(fù)讀）隔離級別的實現(xiàn)钉嘹。read view 不是真實存在的填抬，只是一個概念，undo log 才是它的體現(xiàn)隧期。它主要是通過版本和 undolog 計算出來的。作用是決定事務(wù)能看到哪些數(shù)據(jù)赘娄。每個事務(wù)或者語句有自己的一致性視圖仆潮。普通查詢語句是一致性讀，一致性讀會根據(jù) row trx_id 和一致性視圖確定數(shù)據(jù)版本的可見性遣臼。

• 3.1 數(shù)據(jù)版本的可見性規(guī)則
  read view 中主要包含當(dāng)前系統(tǒng)中還有哪些活躍的讀寫事務(wù)性置，在實現(xiàn)上 InnDB 為每個事務(wù)構(gòu)造了一個數(shù)組，用來保存這個事務(wù)啟動瞬間揍堰，當(dāng)前正活躍（還未提交）的事務(wù)鹏浅。前面說了事務(wù) ID 隨時間嚴(yán)格遞增的，把系統(tǒng)中已提交的事務(wù) ID 的最大值記為數(shù)組的低水位屏歹，已創(chuàng)建過的事務(wù) ID + 1記為高水位隐砸。
  這個視圖數(shù)組和高水位就組成了當(dāng)前事務(wù)的一致性視圖（read view）

  這個數(shù)組畫個圖，長這樣：

  
  
  read-view.png

  規(guī)則如下：

  • 如果 trx_id 在灰色區(qū)域蝙眶，表明被訪問版本的 trx_id 小于數(shù)組中低水位的 id 值季希，也即生成該版本的事務(wù)在生成 read view 前已經(jīng)提交，所以該版本可見幽纷，可以被當(dāng)前事務(wù)訪問式塌。

  • 如果 trx_id 在橙色區(qū)域，表明被訪問版本的 trx_id 大于數(shù)組中高水位的 id 值友浸，也即生成該版本的事務(wù)在生成 read view 后才生成峰尝，所以該版本不可見，不能被當(dāng)前事務(wù)訪問收恢。

  • 如果在綠色區(qū)域武学，就會有兩種情況：

    • trx_id 在數(shù)組中，證明這個版本是由還未提交的事務(wù)生成的派诬，不可見

    • trx_id 不在數(shù)組中劳淆，證明這個版本是由已提交的事務(wù)生成的，可見

      落在綠色區(qū)域意味著是事務(wù) ID 在低水位和高水位這個范圍里面默赂，而真正是否可見沛鸵，看綠色區(qū)域是否有這個值。如果綠色區(qū)域沒有這個事務(wù) ID，則可見曲掰，如果有疾捍，則不可見。在這個范圍里面并不意味著這個范圍就有這個值栏妖，比如 [1,2,3,5]乱豆，4 在這個數(shù)組 1-5 的范圍里，卻沒在這個數(shù)組里面吊趾。

      • 參考：https://blog.csdn.net/weixin_52622200/article/details/119741701

• 當(dāng)前讀

像select lock in share mode(共享鎖), select for update ; update, insert ,delete(排他鎖)這些操作都是一種當(dāng)前讀宛裕，為什么叫當(dāng)前讀？就是它讀取的是記錄的最新版本论泛，讀取時還要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄揩尸，會對讀取的記錄進行加鎖。

• 快照度

像不加鎖的select操作就是快照讀屁奏，即不加鎖的非阻塞讀岩榆；快照讀的前提是隔離級別不是串行級別，串行級別下的快照讀會退化成當(dāng)前讀坟瓢；之所以出現(xiàn)快照讀的情況勇边，是基于提高并發(fā)性能的考慮，快照讀的實現(xiàn)是基于多版本并發(fā)控制折联，即MVCC,可以認(rèn)為MVCC是行鎖的一個變種粒褒，但它在很多情況下，避免了加鎖操作诚镰，降低了開銷怀浆；既然是基于多版本，即快照讀可能讀到的并不一定是數(shù)據(jù)的最新版本怕享，而有可能是之前的歷史版本执赡，保證了 ACID 中的 I 特性（隔離性)。