MySql筆記，筆記分為四個部分：
1.MySQL架構(gòu)與SQL執(zhí)行流程
2.MySQL索引原理與使用原則
3.MySQL事務(wù)與鎖詳解
4.MySQL性能優(yōu)化總結(jié)
本節(jié)目標：

1、掌握事務(wù)的特性與事務(wù)并發(fā)造成的問題
2、事務(wù)讀一致性問題的解決方案
3、MVCC 的原理
4、鎖的分類、行鎖的原理、行鎖的算法

一，什么是數(shù)據(jù)庫的事務(wù)？

1. 事務(wù)的典型場景

   在項目里面，什么地方會開啟事務(wù)，或者配置了事務(wù)？無論是在方法上加注解，還是配置切面。

   <tx:advice id="txAdvice" transaction-manager="transactionManager">
       <tx:attributes>
           <tx:method name="save*" rollback-for="Throwable" />
           <tx:method name="add*" rollback-for="Throwable" />
           <tx:method name="send*" rollback-for="Throwable" />
           <tx:method name="insert*" rollback-for="Throwable" />
       </tx:attributes>
   </tx:advice>
   

   比如下單，會操作訂單表，資金表，物流表等等，這個時候我們需要讓這些操作都在一個事務(wù)里面完成。當(dāng)一個業(yè)務(wù)流程涉及多個表的操作的時候，我們希望它們要么是全部成功的，要么都不成功，這個時候我們會啟用事務(wù)。

   在金融的系統(tǒng)里面事務(wù)配置是很常見的，比如行內(nèi)轉(zhuǎn)賬的這種操作，如果我們把它簡單地理解為一個賬戶的余額增加，另一個賬戶的余額減少的情況（當(dāng)然實際上要比這 復(fù)雜），那么這兩個動作一定是同時成功或者同時失敗的，否則就會造成銀行的會計科目不平衡。

2. 事務(wù)的定義

   什么是事務(wù)？

   維基百科的定義：事務(wù)是數(shù)據(jù)庫管理系統(tǒng)（DBMS）執(zhí)行過程中的一個邏輯單位，由一個有限的數(shù)據(jù)庫操作序列構(gòu)成。
   這里面有兩個關(guān)鍵點，第一個，它是數(shù)據(jù)庫最小的工作單元，是不可以再分的。第二個，它可能包含了一個或者一系列的 DML 語句，包括 insert delete update。
   （單條 DDL（create drop）和 DCL（grant revoke）也會有事務(wù)）

3. 哪些存儲引擎支持事務(wù)

   一是InnoDB 支持事務(wù)，這個也是它成為默認的存儲引擎的一個重要原因：
   https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
   另一個是 NDB。

4. 事務(wù)的四大特性：ACID

   • 第一個，原子性，Atomicity，也就是我們剛才說的不可再分，也就意味著我們對數(shù)據(jù)庫的一系列的操作，要么都是成功，要么都是失敗，不可能出現(xiàn)部分成功或者部分失敗的情況。以轉(zhuǎn)賬的場景為例，一個賬戶的余額減少，對應(yīng)一個賬戶的增加，這兩個一定是同時成功或者同時失敗的。

     全部成功比較簡單，問題是如果前面一個操作已經(jīng)成功了，后面的操作失敗了，怎么讓它全部失敗呢？這個時候我們必須要回滾。
     原子性，在 InnoDB 里面是通過 undo log 來實現(xiàn)的，它記錄了數(shù)據(jù)修改之前的值（邏輯日志），一旦發(fā)生異常，就可以用 undo log 來實現(xiàn)回滾操作。

   • 第二個，一致性，consistent，指的是數(shù)據(jù)庫的完整性約束沒有被破壞，事務(wù)執(zhí)行的前后都是合法的數(shù)據(jù)狀態(tài)。比如主鍵必須是唯一的，字段長度符合要求。
     除了數(shù)據(jù)庫自身的完整性約束，還有一個是用戶自定義的完整性。
     比如說轉(zhuǎn)賬的這個場景，A 賬戶余額減少 1000，B 賬戶余額只增加了 500，這個時候因為兩個操作都成功了，按照我們對原子性的定義，它是滿足原子性的， 但是它沒有滿足一致性，因為它導(dǎo)致了會計科目的不平衡。
     還有一種情況，A 賬戶余額為 0，如果這個時候轉(zhuǎn)賬成功了，A 賬戶的余額會變成-1000，雖然它滿足了原子性的，但是我們知道，借記卡的余額是不能夠小于 0 的，所以也違反了一致性。用戶自定義的完整性通常要在代碼中控制。

   • 第三個，隔離性，Isolation，我們有了事務(wù)的定義以后，在數(shù)據(jù)庫里面會有很多的事務(wù)同時去操作我們的同一張表或者同一行數(shù)據(jù)，必然會產(chǎn)生一些并發(fā)或者干擾的操作， 那么我們對隔離性的定義，就是這些很多個的事務(wù)，對表或者行的并發(fā)操作，應(yīng)該是透明的，互相不干擾的。通過這種方式，我們最終也是保證業(yè)務(wù)數(shù)據(jù)的一致性。

   • 最后一個叫做持久性，Durable，事務(wù)的持久性是什么意思呢？我們對數(shù)據(jù)庫的任意的操作，增刪改，只要事務(wù)提交成功，那么結(jié)果就是永久性的，不可能因為我們系統(tǒng)宕機或者重啟了數(shù)據(jù)庫的服務(wù)器，它又恢復(fù)到原來的狀態(tài)了。這個就是事務(wù)的持久性。

   持久性怎么實現(xiàn)呢？數(shù)據(jù)庫崩潰恢復(fù)（crash-safe）是通過什么實現(xiàn)的？

   持久性是通過 redo log 和 double write 雙寫緩沖來實現(xiàn)的，我們操作數(shù)據(jù)的時候，會先寫到內(nèi)存的 buffer pool 里面，同時記錄 redo log，如果在刷盤之前出現(xiàn)異常，在重啟后就可以讀取 redo log 的內(nèi)容，寫入到磁盤，保證數(shù)據(jù)的持久性。

   當(dāng)然，恢復(fù)成功的前提是數(shù)據(jù)頁本身沒有被破壞，是完整的，這個通過雙寫緩沖 （double write）保證。

   原子性，隔離性，持久性，最后都是為了實現(xiàn)一致性。

5. 數(shù)據(jù)庫什么時候會出現(xiàn)事務(wù)

   無論是我們在 Navicat 的這種工具里面去操作，還是在我們的 Java 代碼里面通過 API 去操作，還是加上@Transactional 的注解或者 AOP 配置，其實最終都是發(fā)送一個指令到數(shù)據(jù)庫去執(zhí)行，Java 的 JDBC 只不過是把這些命令封裝起來了。

   我們先來看一下我們的操作環(huán)境。版本（5.7），存儲引擎（InnnoDB），事務(wù)隔離 級別（RR）。

   select version();
   show variables like '%engine%';
   show global variables like "tx_isolation";
   

   執(zhí)行這樣一條更新語句的時候，它有事務(wù)嗎？

   update student set sname = '丑牛 javacoo' where id=1
   

   實際上，它自動開啟了一個事務(wù)，并且提交了，所以最終寫入了磁盤。

   這個是開啟事務(wù)的第一種方式，自動開啟和自動提交。

   InnoDB 里面有一個 autocommit 的參數(shù)（分成兩個級別， session 級別和 global 級別）。

   show variables like 'autocommit';
   

   它的默認值是 ON。autocommit 這個參數(shù)是什么意思呢？是否自動提交。如果它的 值是 true/on 的話，我們在操作數(shù)據(jù)的時候，會自動開啟一個事務(wù)，和自動提交事務(wù)。

   否則，如果我們把 autocommit 設(shè)置成 false/off，那么數(shù)據(jù)庫的事務(wù)就需要我們手動地去開啟和手動地去結(jié)束。

   手動開啟事務(wù)也有幾種方式，一種是用 begin；一種是用 start transaction。

   那么怎么結(jié)束一個事務(wù)呢？我們結(jié)束也有兩種方式，第一種就是提交一個事務(wù)， commit；還有一種就是 rollback，回滾的時候，事務(wù)也會結(jié)束。還有一種情況，客戶端的連接斷開的時候，事務(wù)也會結(jié)束。

   后面我們會講到，當(dāng)我們結(jié)束一個事務(wù)的時候，事務(wù)持有的鎖就會被釋放，無論是提交還是回滾。 我們用 begin 手工開啟一個事務(wù)，執(zhí)行第二個 update，但是數(shù)據(jù)沒有寫入磁盤，因為事務(wù)還沒有提交，這個時候 commit 一下，再刷新一下，OK，寫入了。 這個就是我們開啟和結(jié)束事務(wù)的兩種方式。

6. 事務(wù)并發(fā)會帶來什么問題？

當(dāng)很多事務(wù)并發(fā)地去操作數(shù)據(jù)庫的表或者行的時候，如果沒有我們剛才講的事務(wù)的 Isolation 隔離性的時候，會帶來哪些問題呢？

事務(wù)并發(fā)-臟讀.png

我們有兩個事務(wù)，一個是 Transaction A，一個是 Transaction B，在第一個事務(wù)里面，它首先通過一個 where id=1 的條件查詢一條數(shù)據(jù)，返回 name=dy，age=16 的 這條數(shù)據(jù)。然后第二個事務(wù)，它同樣地是去操作 id=1 的這行數(shù)據(jù)，它通過一個 update 的語句，把這行 id=1 的數(shù)據(jù)的 age 改成了 18，但是注意，它沒有提交。

這個時候，在第一個事務(wù)里面，它再次去執(zhí)行相同的查詢操作，發(fā)現(xiàn)數(shù)據(jù)發(fā)生了變化，獲取到的數(shù)據(jù) age 變成了 18。那么，這種在一個事務(wù)里面，由于其他的時候修改了數(shù)據(jù)并且沒有提交，而導(dǎo)致了前后兩次讀取數(shù)據(jù)不一致的情況，這種事務(wù)并發(fā)的問題， 我們把它定義成什么？

這個叫做臟讀。

如果在轉(zhuǎn)賬的案例里面，我們第一個事務(wù)基于讀取到的第二個事務(wù)未提交的余額進行了操作，但是第二個事務(wù)進行了回滾，這個時候就會導(dǎo)致數(shù)據(jù)不一致。 這種讀取到其他事務(wù)未提交的數(shù)據(jù)的情況，我們把它叫做臟讀。

我們再來看第二個。

事務(wù)并發(fā)-不可重復(fù)讀.png

同樣是兩個事務(wù)，第一個事務(wù)通過 id=1 查詢到了一條數(shù)據(jù)。然后在第二個事務(wù)里面執(zhí)行了一個 update 操作，這里大家注意一下，執(zhí)行了 update 以后它通過一個 commit 提交了修改。然后第一個事務(wù)讀取到了其他事務(wù)已提交的數(shù)據(jù)導(dǎo)致前后兩次讀取數(shù)據(jù)不 一致的情況，就像這里，age 到底是等于 16 還是 18，那么這種事務(wù)并發(fā)帶來的問題， 我們把它叫做什么？ 這種一個事務(wù)讀取到了其他事務(wù)已提交的數(shù)據(jù)導(dǎo)致前后兩次讀取數(shù)據(jù)不一致的情 況，我們把它叫做不可重復(fù)讀。

事務(wù)并發(fā)-幻讀.png

在第一個事務(wù)里面我們執(zhí)行了一個范圍查詢，這個時候滿足條件的數(shù)據(jù)只有一條。 在第二個事務(wù)里面，它插入了一行數(shù)據(jù)，并且提交了。重點：插入了一行數(shù)據(jù)。在第一個事務(wù)里面再去查詢的時候，它發(fā)現(xiàn)多了一行數(shù)據(jù)。這種情況，我們把它叫做什么呢？ 一個事務(wù)前后兩次讀取數(shù)據(jù)數(shù)據(jù)不一致，是由于其他事務(wù)插入數(shù)據(jù)造成的，這種情況我們把它叫做幻讀。 不可重復(fù)讀和幻讀，的區(qū)別在那里呢？ 不可重復(fù)讀是修改或者刪除，幻讀是插入。

小結(jié)：我們剛才講了事務(wù)并發(fā)帶來的三大問題，現(xiàn)在來給大家總結(jié)一下。無論是臟讀，還是不可重復(fù)讀，還是幻讀，它們都是數(shù)據(jù)庫的讀一致性的問題，都是在一個事務(wù)里面前后兩次讀取出現(xiàn)了不一致的情況。

讀一致性的問題，必須要由數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決。就像我們?nèi)ワ埖瓿燥?，基本的設(shè)施和衛(wèi)生保證都是飯店提供的。那么我們使用數(shù)據(jù)庫，隔離性的問題也必須由數(shù)據(jù)庫幫助我們來解決。

7. SQL92 標準

   所以，就有很多的數(shù)據(jù)庫專家聯(lián)合制定了一個標準，也就是說建議數(shù)據(jù)庫廠商都按照這個標準，提供一定的事務(wù)隔離級別，來解決事務(wù)并發(fā)的問題，這個就是 SQL92 標準。

   我們來看一下 SQL92 標準的官網(wǎng)。 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

   這里面有一張表格（搜索_iso），里面定義了四個隔離級別，右邊的 P1 P2 P3 就是代表事務(wù)并發(fā)的3個問題，臟讀，不可重復(fù)讀，幻讀。Possible 代表在這個隔離級別下， 這個問題有可能發(fā)生，換句話說，沒有解決這個問題。Not Possible 就是解決了這個問題。

   我們詳細地分析一下這 4 個隔離級別是怎么定義的。

   第一個隔離級別叫做：Read Uncommitted（未提交讀），一個事務(wù)可以讀取到其他事務(wù)未提交的數(shù)據(jù)，會出現(xiàn)臟讀，所以叫做 RU，它沒有解決任何的問題。

   第二個隔離級別叫做：Read Committed（已提交讀），也就是一個事務(wù)只能讀取到其他事務(wù)已提交的數(shù)據(jù)，不能讀取到其他事務(wù)未提交的數(shù)據(jù)，它解決了臟讀的問題， 但是會出現(xiàn)不可重復(fù)讀的問題。

   第三個隔離級別叫做：Repeatable Read (可重復(fù)讀)，它解決了不可重復(fù)讀的問題， 也就是在同一個事務(wù)里面多次讀取同樣的數(shù)據(jù)結(jié)果是一樣的，但是在這個級別下，沒有定義解決幻讀的問題。

   最后一個就是：Serializable（串行化），在這個隔離級別里面，所有的事務(wù)都是串行執(zhí)行的，也就是對數(shù)據(jù)的操作需要排隊，已經(jīng)不存在事務(wù)的并發(fā)操作了，所以它解決 了所有的問題。

   這個是 SQL92 的標準，但是不同的數(shù)據(jù)庫廠商或者存儲引擎的實現(xiàn)有一定的差異， 比如 Oracle 里面就只有兩種 RC（已提交讀）和 Serializable（串行化）。那么 InnoDB 的實現(xiàn)又是怎么樣的呢？

8. MySQL InnoDB 對隔離級別的支持

   在 MySQL InnoDB 里面，不需要使用串行化的隔離級別去解決所有問題。那我們來 看一下 MySQL InnoDB 里面對數(shù)據(jù)庫事務(wù)隔離級別的支持程度是什么樣的。

   事務(wù)隔離級別	臟讀	不可重復(fù)讀	幻讀
   Read Uncommitted（未提交讀）	可能	可能	可能
   Read Committed（已提交讀	不可能	可能	可能
   Repeatable Read (可重復(fù)讀)	不可能	不可能	InnoDB 不可能
   Serializable（串行化）	不可能	不可能	不可能

   InnoDB 支持的四個隔離級別和 SQL92 定義的基本一致，隔離級別越高，事務(wù)的并發(fā)度就越低。唯一的區(qū)別就在于，InnoDB 在 RR 的級別就解決了幻讀的問題。這個也是 InnoDB 默認使用 RR 作為事務(wù)隔離級別的原因，既保證了數(shù)據(jù)的一致性，又支持較高的并發(fā)度。

9. 兩大實現(xiàn)方案

   那么大家想一下，如果要解決讀一致性的問題，保證一個事務(wù)中前后兩次讀取數(shù)據(jù)結(jié)果一致，實現(xiàn)事務(wù)隔離，應(yīng)該怎么做？我們有哪一些方法呢？你的思路是什么樣的呢？ 總體上來說，我們有兩大類的方案。

   • LBCC

     第一種，我既然要保證前后兩次讀取數(shù)據(jù)一致，那么我讀取數(shù)據(jù)的時候，鎖定我要操作的數(shù)據(jù)，不允許其他的事務(wù)修改就行了。這種方案我們叫做基于鎖的并發(fā)控制 Lock Based Concurrency Control（LBCC）。

     如果僅僅是基于鎖來實現(xiàn)事務(wù)隔離，一個事務(wù)讀取的時候不允許其他時候修改，那就意味著不支持并發(fā)的讀寫操作，而我們的大多數(shù)應(yīng)用都是讀多寫少的，這樣會極大地 影響操作數(shù)據(jù)的效率。

   • MVCC

   所以我們還有另一種解決方案，如果要讓一個事務(wù)前后兩次讀取的數(shù)據(jù)保持一致， 那么我們可以在修改數(shù)據(jù)的時候給它建立一個備份或者叫快照，后面再來讀取這個快照就行了。這種方案我們叫做多版本的并發(fā)控制 Multi Version Concurrency Control （MVCC）。

   MVCC 的核心思想是： 我可以查到在我這個事務(wù)開始之前已經(jīng)存在的數(shù)據(jù)，即使它在后面被修改或者刪除了。在我這個事務(wù)之后新增的數(shù)據(jù)，我是查不到的。

   問題：這個快照什么時候創(chuàng)建？讀取數(shù)據(jù)的時候，怎么保證能讀取到這個快照而不是最新的數(shù)據(jù)？這個怎么實現(xiàn)呢？

   InnoDB 為每行記錄都實現(xiàn)了兩個隱藏字段：

   DB_TRX_ID，6 字節(jié)：插入或更新行的最后一個事務(wù)的事務(wù) ID，事務(wù)編號是自動遞增的（我們把它理解為創(chuàng)建版本號，在數(shù)據(jù)新增或者修改為新數(shù)據(jù)的時候，記錄當(dāng)前事 務(wù) ID）。

   DB_ROLL_PTR，7 字節(jié)：回滾指針（我們把它理解為刪除版本號，數(shù)據(jù)被刪除或記錄為舊數(shù)據(jù)的時候，記錄當(dāng)前事務(wù) ID）。

   我們把這兩個事務(wù)ID理解為版本號。

   MVCC 演示圖:

   MVCC.png

第一個事務(wù)，初始化數(shù)據(jù)（檢查初始數(shù)據(jù)）

ts1.png

此時的數(shù)據(jù)，創(chuàng)建版本是當(dāng)前事務(wù) ID，刪除版本為空

ts2.png

第二個事務(wù)，執(zhí)行第 1 次查詢，讀取到兩條原始數(shù)據(jù)，這個時候事務(wù) ID 是 2：

ts3.png

第三個事務(wù)，插入數(shù)據(jù)：

ts4.png

ts5.png

第二個事務(wù)，執(zhí)行第 2 次查詢：

ts6.png

MVCC 的查找規(guī)則：只能查找創(chuàng)建時間小于等于當(dāng)前事務(wù) ID 的數(shù)據(jù)，和刪除時間大于當(dāng)前事務(wù) ID 的行（或未刪除）。 也就是不能查到在我的事務(wù)開始之后插入的數(shù)據(jù)，tom 的創(chuàng)建 ID 大于 2，所以還是 只能查到兩條數(shù)據(jù)。

第四個事務(wù)，刪除數(shù)據(jù)，刪除了 id=2 jack 這條記錄：

ts7.png

此時的數(shù)據(jù)，jack 的刪除版本被記錄為當(dāng)前事務(wù) ID，4，其他數(shù)據(jù)不變：

ts8.png

在第二個事務(wù)中，執(zhí)行第 3 次查詢:

ts9.png

查找規(guī)則：只能查找創(chuàng)建時間小于等于當(dāng)前事務(wù) ID 的數(shù)據(jù)，和刪除時間大于當(dāng)前事務(wù) ID 的行（或未刪除）。

也就是，在我事務(wù)開始之后刪除的數(shù)據(jù)，所以 jack 依然可以查出來。所以還是這兩條數(shù)據(jù)。

第五個事務(wù)，執(zhí)行更新操作，這個事務(wù)事務(wù) ID 是 5：

ts10.png

此時的數(shù)據(jù)，更新數(shù)據(jù)的時候，舊數(shù)據(jù)的刪除版本被記錄為當(dāng)前事務(wù) ID 5（undo）， 產(chǎn)生了一條新數(shù)據(jù)，創(chuàng)建 ID 為當(dāng)前事務(wù) ID 5：

ts11.png

第二個事務(wù)，執(zhí)行第 4 次查詢：

查找規(guī)則：只能查找創(chuàng)建時間小于等于當(dāng)前事務(wù) ID 的數(shù)據(jù)，和刪除時間大于當(dāng)前事 務(wù) ID 的行（或未刪除）。

因為更新后的數(shù)據(jù) penyuyan 創(chuàng)建版本大于 2，代表是在事務(wù)之后增加的，查不出 來。

而舊數(shù)據(jù) qingshan 的刪除版本大于 2，代表是在事務(wù)之后刪除的，可以查出來。

通過以上演示我們能看到，通過版本號的控制，無論其他事務(wù)是插入、修改、刪除， 第一個事務(wù)查詢到的數(shù)據(jù)都沒有變化。

在 InnoDB 中，MVCC 是通過 Undo log 實現(xiàn)的。

Oracle、Postgres 等等其他數(shù)據(jù)庫都有 MVCC 的實現(xiàn)。

需要注意，在 InnoDB 中，MVCC 和鎖是協(xié)同使用的，這兩種方案并不是互斥的。

第一大類解決方案是鎖，鎖又是怎么實現(xiàn)讀一致性的呢？

二，MySQL InnoDB 鎖的基本類型

1. 鎖的粒度

   我們講到 InnoDB 里面既有行級別的鎖，又有表級別的鎖，我們先來分析一下這兩種鎖定粒度的一些差異。

   表鎖，顧名思義，是鎖住一張表；行鎖就是鎖住表里面的一行數(shù)據(jù)。鎖定粒度，表鎖肯定是大于行鎖的。

   那么加鎖效率，表鎖應(yīng)該是大于行鎖還是小于行鎖呢？大于。為什么？表鎖只需要直接鎖住這張表就行了，而行鎖，還需要在表里面去檢索這一行數(shù)據(jù)，所以表鎖的加鎖 效率更高。

   第二個沖突的概率？表鎖的沖突概率比行鎖大，還是??？ 大于，因為當(dāng)我們鎖住一張表的時候，其他任何一個事務(wù)都不能操作這張表。但是我們鎖住了表里面的一行數(shù)據(jù)的時候，其他的事務(wù)還可以來操作表里面的其他沒有被鎖定的行，所以表鎖的沖突概率更大。

   表鎖的沖突概率更大，所以并發(fā)性能更低，這里并發(fā)性能就是小于。

   InnoDB 里面我們知道它既支持表鎖又支持行鎖，另一個常用的存儲引擎 MyISAM 支持什么粒度的鎖？這是第一個問題。第二個就是 InnoDB 已經(jīng)支持行鎖了，那么它也可以通過把表里面的每一行都鎖住來實現(xiàn)表鎖，為什么還要提供表鎖呢？

   要搞清楚這個問題，我們就要來了解一下 InnoDB 里面的基本的鎖的模式（lock mode），這里面有兩個行鎖和兩個表鎖。

2. 共享鎖

   第一個行級別的鎖就是我們在官網(wǎng)看到的 Shared Locks （共享鎖），我們獲取了 一行數(shù)據(jù)的讀鎖以后，可以用來讀取數(shù)據(jù)，所以它也叫做讀鎖，注意不要在加上了讀鎖以后去寫數(shù)據(jù)，不然的話可能會出現(xiàn)死鎖的情況。而且多個事務(wù)可以共享一把讀鎖。那怎么給一行數(shù)據(jù)加上讀鎖呢？ 我們可以用 select …… lock in share mode; 的方式手工加上一把讀鎖。 釋放鎖有兩種方式，只要事務(wù)結(jié)束，鎖就會自動事務(wù)，包括提交事務(wù)和結(jié)束事務(wù)。 我們也來驗證一下，看看共享鎖是不是可以重復(fù)獲取。

   行鎖.png

3. 排它鎖

   第二個行級別的鎖叫做 Exclusive Locks（排它鎖），它是用來操作數(shù)據(jù)的，所以又叫做寫鎖。只要一個事務(wù)獲取了一行數(shù)據(jù)的排它鎖，其他的事務(wù)就不能再獲取這一行數(shù) 據(jù)的共享鎖和排它鎖。

   排它鎖的加鎖方式有兩種，第一種是自動加排他鎖。我們在操作數(shù)據(jù)的時候，包括增刪改，都會默認加上一個排它鎖。

   還有一種是手工加鎖，我們用一個 FOR UPDATE 給一行數(shù)據(jù)加上一個排它鎖，這個無論是在我們的代碼里面還是操作數(shù)據(jù)的工具里面，都比較常用。 釋放鎖的方式跟前面是一樣的。 排他鎖的驗證：

   排它鎖.png

這個是兩個行鎖，接下來就是兩個表鎖。

4. 意向鎖

   意向鎖是什么呢？我們好像從來沒有聽過，也從來沒有使用過，其實他們是由數(shù)據(jù)庫自己維護的。

   也就是說，當(dāng)我們給一行數(shù)據(jù)加上共享鎖之前，數(shù)據(jù)庫會自動在這張表上面加一個意向共享鎖。

   當(dāng)我們給一行數(shù)據(jù)加上排他鎖之前，數(shù)據(jù)庫會自動在這張表上面加一個意向排他鎖。

   反過來說： 如果一張表上面至少有一個意向共享鎖，說明有其他的事務(wù)給其中的某些數(shù)據(jù)行加上了共享鎖。

   如果一張表上面至少有一個意向排他鎖，說明有其他的事務(wù)給其中的某些數(shù)據(jù)行加上了排他鎖。

   select * from t2 where id =4 for update;
   TABLE LOCK table `gupao`.`t2` trx id 24467 lock mode IX
   RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table `gupao`.`t2` trx id 24467 lock_mode X locks rec but not
   gap
   

   那么這兩個表級別的鎖存在的意義是什么呢？

   第一個，我們有了表級別的鎖，在 InnoDB 里面就可以支持更多粒度的鎖。它的第二個作用，我們想一下，如果說沒有意向鎖的話，當(dāng)我們準備給一張表加上表鎖的時候，我們首先要做什么？是不是必須先要去判斷有沒其他的事務(wù)鎖定了其中了某些行？如果有的話，肯定不能加上表鎖。那么這個時候我們就要去掃描整張表才能確定能不能成功加上一個表鎖，如果數(shù)據(jù)量特別大，比如有上千萬的數(shù)據(jù)的時候，加表鎖的效率是不是很低？

   但是我們引入了意向鎖之后就不一樣了。我只要判斷這張表上面有沒有意向鎖，如果有，就直接返回失敗。如果沒有，就可以加鎖成功。所以 InnoDB 里面的表鎖，我們可以把它理解成一個標志。就像火車上廁所有沒有人使用的燈，是用來提高加鎖的效率的。

意向鎖.png

以上就是 MySQL 里面的 4 種基本的鎖的模式，或者叫做鎖的類型。

到這里我們要思考兩個問題，首先，鎖的作用是什么？

它跟 Java 里面的鎖是一樣的， 是為了解決資源競爭的問題，Java 里面的資源是對象，數(shù)據(jù)庫的資源就是數(shù)據(jù)表或者數(shù)據(jù)行。

所以鎖是用來解決事務(wù)對數(shù)據(jù)的并發(fā)訪問的問題的。

那么，鎖到底鎖住了什么呢？ 當(dāng)一個事務(wù)鎖住了一行數(shù)據(jù)的時候，其他的事務(wù)不能操作這一行數(shù)據(jù)，那它到底是鎖住了這一行數(shù)據(jù)，還是鎖住了這一個字段，還是鎖住了別的什么東西呢？

三，行鎖的原理

1. 沒有索引的表（假設(shè)鎖住記錄）

   首先我們有三張表，一張沒有索引的 t1，一張有主鍵索引的 t2，一張有唯一索引的 t3。 我們先假設(shè) InnoDB 的鎖鎖住了是一行數(shù)據(jù)或者一條記錄。

   我們先來看一下 t1 的表結(jié)構(gòu)，它有兩個字段，int 類型的 id 和 varchar 類型的 name。 里面有 4 條數(shù)據(jù)，1、2、3、4。

ts12.png

現(xiàn)在我們在兩個會話里面手工開啟兩個事務(wù)。 在第一個事務(wù)里面，我們通過 where id =1 鎖住第一行數(shù)據(jù)。

在第二個事務(wù)里面，我們嘗試給 id=3 的這一行數(shù)據(jù)加鎖，大家覺得能成功嗎？ 很遺憾，我們看到紅燈亮起，這個加鎖的操作被阻塞了。這就有點奇怪了，第一個事務(wù)鎖住了 id=1 的這行數(shù)據(jù)，為什么我不能操作 id=3 的數(shù)據(jù)呢？

我們再來操作一條不存在的數(shù)據(jù)，插入 id=5。它也被阻塞了。實際上這里整張表都被鎖住了。所以，我們的第一個猜想被推翻了，InnoDB 的鎖鎖住的應(yīng)該不是 Record。 那為什么在沒有索引或者沒有用到索引的情況下，會鎖住整張表？這個問題我們先留在這里。 我們繼續(xù)看第二個演示。

2. 有主鍵索引的表

   我們看一下 t2 的表結(jié)構(gòu)。字段是一樣的，不同的地方是 id 上創(chuàng)建了一個主鍵索引。 里面的數(shù)據(jù)是 1、4、7、10。

ts13.png

ts14.png

第一種情況，使用相同的 id 值去加鎖，沖突；使用不同的 id 加鎖，可以加鎖成功。 那么，既然不是鎖定一行數(shù)據(jù)，有沒有可能是鎖住了 id 的這個字段呢？

3. 唯一索引（假設(shè)鎖住字段）

   我們看一下 t3 的表結(jié)構(gòu)。字段還是一樣的， id 上創(chuàng)建了一個主鍵索引，name 上 創(chuàng)建了一個唯一索引。里面的數(shù)據(jù)是 1、4、7、10。

ts15.png

在第一個事務(wù)里面，我們通過 name 字段去鎖定值是 4 的這行數(shù)據(jù)。

在第二個事務(wù)里面，嘗試獲取一樣的排它鎖，肯定是失敗的，這個不用懷疑。

在這里我們懷疑 InnoDB 鎖住的是字段，所以這次我換一個字段，用 id=4 去給這行 數(shù)據(jù)加鎖，大家覺得能成功嗎？ 很遺憾，又被阻塞了，說明鎖住的是字段的這個推測也是錯的，否則就不會出現(xiàn)第 一個事務(wù)鎖住了 name，第二個字段鎖住 id 失敗的情況。

既然鎖住的不是 record，也不是 column，InnoDB 里面鎖住的到底是什么呢？在這 三個案例里面，我們要去分析一下他們的差異在哪里，也就是這三張表的結(jié)構(gòu)，是什么 區(qū)別導(dǎo)致了加鎖的行為的差異？其實答案就是索引。InnoDB 的行鎖，就是通過鎖住索引來實現(xiàn)的。

我們還有兩個問題沒有解決：

1、為什么表里面沒有索引的時候，鎖住一行數(shù)據(jù)會導(dǎo)致鎖表？ 或者說，如果鎖住的是索引，一張表沒有索引怎么辦？

所以，一張表有沒有可能沒有索引？

1）如果我們定義了主鍵(PRIMARY KEY)，那么 InnoDB 會選擇主鍵作為聚集索引。

2）如果沒有顯式定義主鍵，則 InnoDB 會選擇第一個不包含有 NULL 值的唯一索 引作為主鍵索引。

3）如果也沒有這樣的唯一索引，則 InnoDB 會選擇內(nèi)置 6 字節(jié)長的 ROWID 作為隱藏的聚集索引，它會隨著行記錄的寫入而主鍵遞增。

所以，為什么鎖表，是因為查詢沒有使用索引，會進行全表掃描，然后把每一個隱藏的聚集索引都鎖住了。

2、為什么通過唯一索引給數(shù)據(jù)行加鎖，主鍵索引也會被鎖?。?/p>

大家還記得在 InnoDB 里面，當(dāng)我們使用輔助索引的時候，它是怎么檢索數(shù)據(jù)的嗎？ 輔助索引的葉子節(jié)點存儲的是什么內(nèi)容？

在輔助索引里面，索引存儲的是二級索引和主鍵的值。比如name=4，存儲的是name 的索引和主鍵 id 的值 4。

而主鍵索引里面除了索引之外，還存儲了完整的數(shù)據(jù)。所以我們通過輔助索引鎖定 一行數(shù)據(jù)的時候，它跟我們檢索數(shù)據(jù)的步驟是一樣的，會通過主鍵值找到主鍵索引，然后也鎖定。

ts16.png

現(xiàn)在我們已經(jīng)搞清楚 4 個鎖的基本類型和鎖的原理了，在官網(wǎng)上，還有 3 種鎖，我們把它理解為鎖的算法。我們也來看下 InnoDB 在什么時候分別鎖住什么范圍。

四，鎖的算法

我們先來看一下我們測試用的表，t2，這張表有一個主鍵索引。 我們插入了 4 行數(shù)據(jù)，主鍵值分別是 1、4、7、10。 為了讓大家真正理解這三種行鎖算法的區(qū)別，我們需要了解一下三種范圍的概念。 因為我們用主鍵索引加鎖，我們這里的劃分標準就是主鍵索引的值。

ts17.png

這些數(shù)據(jù)庫里面存在的主鍵值，我們把它叫做 Record，記錄，那么這里我們就有 4 個 Record。

根據(jù)主鍵，這些存在的 Record 隔開的數(shù)據(jù)不存在的區(qū)間，我們把它叫做 Gap，間隙，它是一個左開右開的區(qū)間。

最后一個，間隙（Gap）連同它左邊的記錄（Record），我們把它叫做臨鍵的區(qū)間， 它是一個左開右閉的區(qū)間。

t2 的主鍵索引，它是整型的，可以排序，所以才有這種區(qū)間。如果我的主鍵索引不是整形，是字符怎么辦呢？字符可以排序嗎？ 用 ASCII 碼來排序。 我們已經(jīng)弄清楚了三個范圍的概念，下面我們就來看一下在不同的范圍下，行鎖是怎么表現(xiàn)的。

1. 記錄鎖

   第一種情況，當(dāng)我們對于唯一性的索引（包括唯一索引和主鍵索引）使用等值查詢，精準匹配到一條記錄的時候，這個時候使用的就是記錄鎖。 比如 where id = 1 4 7 10 。 這個演示我們在前面已經(jīng)看過了。我們使用不同的 key 去加鎖，不會沖突，它只鎖 住這個 record。

2. 間隙鎖

   第二種情況，當(dāng)我們查詢的記錄不存在，沒有命中任何一個 record，無論是用等值查詢還是范圍查詢的時候，它使用的都是間隙鎖。

   舉個例子，where id >4 and id <7，where id = 6。

ts18.png

重復(fù)一遍，當(dāng)查詢的記錄不存在的時候，使用間隙鎖。 注意，間隙鎖主要是阻塞插入 insert。相同的間隙鎖之間不沖突。

Gap Lock 只在 RR 中存在。如果要關(guān)閉間隙鎖，就是把事務(wù)隔離級別設(shè)置成 RC， 并且把 innodb_locks_unsafe_for_binlog 設(shè)置為 ON。 這種情況下除了外鍵約束和唯一性檢查會加間隙鎖，其他情況都不會用間隙鎖。

3. 臨鍵鎖

   第三種情況，當(dāng)我們使用了范圍查詢，不僅僅命中了 Record 記錄，還包含了 Gap 間隙，在這種情況下我們使用的就是臨鍵鎖，它是 MySQL 里面默認的行鎖算法，相當(dāng)于記錄鎖加上間隙鎖。

   其他兩種退化的情況： 唯一性索引，等值查詢匹配到一條記錄的時候，退化成記錄鎖。

   沒有匹配到任何記錄的時候，退化成間隙鎖。

   比如我們使用>5 <9， 它包含了記錄不存在的區(qū)間，也包含了一個 Record 7

ts19.png

臨鍵鎖，鎖住最后一個 key 的下一個左開右閉的區(qū)間。

select * from t2 where id >5 and id <=7 for update; -- 鎖住(4,7]和(7,10]
select * from t2 where id >8 and id <=10 for update; -- 鎖住 (7,10]，(10,+∞)

為什么要鎖住下一個左開右閉的區(qū)間？——就是為了解決幻讀的問題.

4. 小結(jié)：隔離級別的實現(xiàn)

   所以，我們再回過頭來看下這張圖片，為什么 InnoDB 的 RR 級別能夠解決幻讀的問題，就是用臨鍵鎖實現(xiàn)的。

   我們再回過頭來看下這張圖片，這個就是MySQL InnoDB里面事務(wù)隔離級別的實現(xiàn)。

   事務(wù)隔離級別	臟讀	不可重復(fù)讀	幻讀
   Read Uncommitted（未提交讀）	可能	可能	可能
   Read Committed（已提交讀	不可能	可能	可能
   Repeatable Read (可重復(fù)讀)	不可能	不可能	InnoDB 不可能
   Serializable（串行化）	不可能	不可能	不可能

   最后我們來總結(jié)一下四個事務(wù)隔離級別的實現(xiàn)：

   • Read Uncommited RU 隔離級別：不加鎖。
   • Serializable 所有的 select 語句都會被隱式的轉(zhuǎn)化為 select ... in share mode，會 和 update、delete 互斥。 這兩個很好理解，主要是 RR 和 RC 的區(qū)別？
   • Repeatable Read RR 隔離級別下，普通的 select 使用快照讀(snapshot read)，底層使用 MVCC 來實現(xiàn)。 加鎖的 select(select ... in share mode / select ... for update)以及更新操作 update, delete 等語句使用當(dāng)前讀（current read），底層使用記錄鎖、或者間隙鎖、 臨鍵鎖。
   • Read Commited RC 隔離級別下，普通的 select 都是快照讀，使用 MVCC 實現(xiàn)。 加鎖的 select 都使用記錄鎖，因為沒有 Gap Lock。

   除了兩種特殊情況——外鍵約束檢查(foreign-key constraint checking)以及重復(fù)鍵檢查(duplicate-key checking)時會使用間隙鎖封鎖區(qū)間。 所以 RC 會出現(xiàn)幻讀的問題。

五，事務(wù)隔離級別怎么選？

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

RU 和 Serializable 肯定不能用。為什么有些公司要用 RC，或者說網(wǎng)上有些文章推 薦有 RC？

RC 和 RR 主要有幾個區(qū)別：

1、 RR 的間隙鎖會導(dǎo)致鎖定范圍的擴大。

2、 條件列未使用到索引，RR 鎖表，RC 鎖行。

3、 RC 的“半一致性”（semi-consistent）讀可以增加 update 操作的并發(fā)性。

在 RC 中，一個 update 語句，如果讀到一行已經(jīng)加鎖的記錄，此時 InnoDB 返回記錄最近提交的版本，由 MySQL上層判斷此版本是否滿足 update 的 where 條件。若滿足(需要更新)，則 MySQL 會重新發(fā)起一次讀操作，此時會讀取行的最新版本(并加鎖)。 實際上，如果能夠正確地使用鎖（避免不使用索引去枷鎖），只鎖定需要的數(shù)據(jù)， 用默認的 RR 級別就可以了。 在我們使用鎖的時候，有一個問題是需要注意和避免的，我們知道，排它鎖有互斥的特性。一個事務(wù)或者說一個線程持有鎖的時候，會阻止其他的線程獲取鎖，這個時候會造成阻塞等待，如果循環(huán)等待，會有可能造成死鎖。

這個問題我們需要從幾個方面來分析，一個是鎖為什么不釋放，第二個是被阻塞了怎么辦，第三個死鎖是怎么發(fā)生的，怎么避免。

六，死鎖

1. 鎖的釋放與阻塞

   回顧：鎖什么時候釋放？ 事務(wù)結(jié)束（commit，rollback）；客戶端連接斷開。

   如果一個事務(wù)一直未釋放鎖，其他事務(wù)會被阻塞多久？會不會永遠等待下去？如果是，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會占用大量計算機資源，造成嚴重性能問題，甚至拖跨數(shù)據(jù)庫。

   [Err] 1205 - Lock wait timeout exceeded; try restarting transaction

   MySQL 有一個參數(shù)來控制獲取鎖的等待時間，默認是 50 秒。

   show VARIABLES like 'innodb_lock_wait_timeout';
   

   vr.png

對于死鎖，是無論等多久都不能獲取到鎖的，這種情況，也需要等待 50 秒鐘嗎？那不是白白浪費了 50 秒鐘的時間嗎？ 我們先來看一下什么時候會發(fā)生死鎖。

2. 死鎖的發(fā)生和檢測

   死鎖演示：

s1.png

s2.png

在第一個事務(wù)中，檢測到了死鎖，馬上退出了，第二個事務(wù)獲得了鎖，不需要等待 50 秒：

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

為什么可以直接檢測到呢？是因為死鎖的發(fā)生需要滿足一定的條件，所以在發(fā)生死鎖時，InnoDB 一般都能通過算法（wait-for graph）自動檢測到。

那么死鎖需要滿足什么條件？死鎖的產(chǎn)生條件：

因為鎖本身是互斥的，（1）同一時刻只能有一個事務(wù)持有這把鎖，（2）其他的事務(wù)需要在這個事務(wù)釋放鎖之后才能獲取鎖，而不可以強行剝奪，（3）當(dāng)多個事務(wù)形成等待環(huán)路的時候，即發(fā)生死鎖。

舉例：

理發(fā)店有兩個總監(jiān)。一個負責(zé)剪頭的 Tony 總監(jiān)，一個負責(zé)洗頭的 Kelvin 總監(jiān)。

Tony 不能同時給兩個人剪頭，這個就叫互斥。

Tony 在給別人在剪頭的時候，你不能讓他停下來幫你剪頭，這個叫不能強行剝奪。

如果Tony的客戶對Kelvin總監(jiān)說：你不幫我洗頭我怎么剪頭？Kelvin的客戶對Tony 總監(jiān)說：你不幫我剪頭我怎么洗頭？這個就叫形成等待環(huán)路。

如果鎖一直沒有釋放，就有可能造成大量阻塞或者發(fā)生死鎖，造成系統(tǒng)吞吐量下降， 這時候就要查看是哪些事務(wù)持有了鎖。

3. 查看鎖信息（日志）

SHOW STATUS 命令中，包括了一些行鎖的信息：

show status like 'innodb_row_lock_%';

ss.png

Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量；

Innodb_row_lock_time ：從系統(tǒng)啟動到現(xiàn)在鎖定的總時間長度，單位 ms；

Innodb_row_lock_time_avg ：每次等待所花平均時間；

Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花的時間；

Innodb_row_lock_waits ：從系統(tǒng)啟動到現(xiàn)在總共等待的次數(shù)。

SHOW 命令是一個概要信息。InnoDB 還提供了三張表來分析事務(wù)與鎖的情況：

select * from information_schema.INNODB_TRX; -- 當(dāng)前運行的所有事務(wù) ，還有具體的語句

ss2.png

select * from information_schema.INNODB_LOCKS; -- 當(dāng)前出現(xiàn)的鎖

ss3.png

select * from information_schema.INNODB_LOCK_WAITS; -- 鎖等待的對應(yīng)關(guān)系

ss4.png

找出持有鎖的事務(wù)之后呢？ 如果一個事務(wù)長時間持有鎖不釋放，可以 kill 事務(wù)對應(yīng)的線程 ID，也就是 INNODB_TRX 表中的 trx_mysql_thread_id，例如執(zhí)行 kill 4，kill 7，kill 8。

當(dāng)然，死鎖的問題不能每次都靠 kill 線程來解決，這是治標不治本的行為。我們應(yīng)該盡量在應(yīng)用端，也就是在編碼的過程中避免。 有哪些可以避免死鎖的方法呢？

4. 死鎖的避免

   1、 在程序中，操作多張表時，盡量以相同的順序來訪問（避免形成等待環(huán)路）；

   2、 批量操作單張表數(shù)據(jù)的時候，先對數(shù)據(jù)進行排序（避免形成等待環(huán)路）；

   3、 申請足夠級別的鎖，如果要操作數(shù)據(jù)，就申請排它鎖；

   4、 盡量使用索引訪問數(shù)據(jù)，避免沒有 where 條件的操作，避免鎖表；

   5、 如果可以，大事務(wù)化成小事務(wù)；

   6、 使用等值查詢而不是范圍查詢查詢數(shù)據(jù)，命中記錄，避免間隙鎖對并發(fā)的影響。

一些信息

路漫漫其修遠兮,吾將上下而求索
碼云：https://gitee.com/javacoo
QQ：164863067
作者/微信：javacoo
郵箱：xihuady@126.com