MySQL-15.鎖

C-15.鎖

事務的隔離性由本章講述的鎖來實現。

1.概述

---

鎖是計算機協調多個程序或執行緒併發訪問某一資源的機制。在程式開發中會存在多執行緒同步的問題，當多個執行緒併發訪問某個資料的時候，尤其是針對一些敏感的資料(比如訂單，金額等)，我們就需要保證這個資料在任何時刻最多隻有一個執行緒在訪問，保證資料的完整性和一致性。在開發過程中加鎖是為了保證資料的一致性，這個思想在資料庫領域中同樣很重要。

在資料庫中，除傳統的計算資源（如CPU，RAM，I/O等）的爭用以外，資料也是一種供許多使用者共享的資源。為保證資料的一致性，需要對併發操作進行控制，因此產生了鎖。同時鎖機制也為實現MySQL的各個隔離級別提供了保證。鎖衝突也是影響資料庫併發訪問效能的一個重要因素。所以鎖對資料庫而言顯得尤其重要，也更加複雜。

2.MySQL併發事務訪問相同記錄

---

事務併發訪問相同記錄的情況大致可以劃分為3種：

2.1 讀-讀情況

讀-讀情況，即併發事務相繼讀取相同的記錄。讀取操作本身不會對記錄有任何影響，並不會引起什麼問題，所以允許這種情況的發生。

2.2 寫-寫情況

寫-寫情況，即併發事務相繼對相同的記錄做出改動。注意，這裡的寫，包括增刪改。

在這種情況下會發生髒寫的問題，任何一種隔離級別都不允許這種問題的發生。所以在多個未提交事務相繼對一條記錄做改動時，需要讓它們排隊執行，這個排隊的過程其實是透過鎖來實現的。這個所謂的鎖其實是一個記憶體中的結構，在事務執行前本來是沒有鎖的，也就是一開始是沒有鎖結構和記錄進行關聯的，如圖所示：

當一個事務相對這條記錄做改動時，首先會看看記憶體中有沒有與這條記錄關聯的鎖結構，當沒有的時候就會在記憶體中生成一個鎖結構與之關聯。比如，事務T1要對這條記錄做改動，就需要生成一個鎖結構與之關聯：

在鎖結構裡有很多資訊，為了簡化理解，只把兩個比較重要的屬性拿了出來：

• trx資訊：代表這個鎖結構是那個事務生成的。
• is_waiting：代表當前事務是否在等待。

當事務T1改動了這條記錄後，就生成了一個鎖結構與該記錄關聯，因為之前沒有別的事務為這條記錄加鎖，所以is_waiting屬性就是false，我們把這個場景就稱之為獲取鎖成功，或者加鎖成功，然後就可以繼續執行操作了。

在事務T1提交之前，另一個事務T2也相對該記錄做改動，那麼先看看有沒有鎖結構與這條記錄關聯。發現有一個鎖結構與之關聯後，然後也生成了一個鎖結構與這條記錄關聯，不過鎖結構的is_waiting屬性值為true，表示當前事務需要等待，我們把這個場景，就稱之為獲取鎖失敗，或者加鎖失敗，圖示：

在事務T1提交之後，就會把該事務生成的鎖結構釋放掉，然後看看還有沒有別的事務在等待獲取鎖，發現了事務T2還在等待獲取鎖，所以把事務T2對應的鎖結構的is_waiting屬性設定為false，然後把該事務對應的執行緒喚醒，讓它繼續執行，此時事務T2就算獲取到鎖了。效果圖就是這樣：

小結幾種說法：

• 不加鎖：意思就是不需要再記憶體中生成對應的鎖結構，可以直接執行操作。
• 獲取鎖成功，或者加鎖成功：意思就是在記憶體中生成了對應的鎖結構，而且鎖結構的is_waiting屬性為false，也就是事務可以繼續執行操作。
• 獲取鎖失敗，或者加鎖失敗，或者沒有獲取到鎖：意思是在記憶體中生成了對應的鎖結構，不過鎖結構的is_waiting屬性為true，也就是事務需要等待，不可以繼續執行操作。

2.3 讀-寫或寫-讀的情況

讀-寫或寫-讀，即一個事務進行讀取操作，另一個進行改動操作。這種情況下可能發生髒讀，不可重複讀，幻讀的問題。

各個資料庫廠商對SQL標準的支援都可能不一樣。比如MySQL在REPEATABLE READ隔離級別上就已經解決了幻讀問題。

2.4 併發問題的解決方案

怎麼解決髒讀、不可重複讀、幻讀這些問題呢？其實有兩種可選的解決方案：

• 方案一：讀操作利用多版本併發控制（MVCC，下章講解），寫操作進行加鎖。

所謂的MVCC，就是生成了一個ReadView，透過ReadView找到符合條件的記錄版本（歷史版本由undo日誌構建）。查詢語句只能讀到在生成ReadView之前已提交事務所做的更改，在生成ReadView之前，未提交的事務或者之後才開啟的事務所做的更改是看不到的。而寫操作肯定針對的是最新版本的記錄，讀記錄的歷史版本和改動記錄的最新版本本身並不衝突，也就是採用MVCC時，讀-寫操作並不衝突。

普通的SELECT語句在READ COMMITTED和REPEATABLE READ隔離級別下會使用到MVCC讀取記錄。

• 在READ COMMITTED隔離級別下，一個事務在執行過程中每次執行SELECT操作時都會生成一個ReadView，ReadView的存在本身就保證了事務不可以讀取到未提交的事務所做的更改，也就是避免了髒讀現象；
• 在REPEATABLE READ隔離級別下，一個事務在執行過程中只有第一次執行SELECT操作才會生成一個ReadView，之後的SELECT操作都複用這個ReadView，這樣也就避免了不可重複讀和幻讀的問題。

• 方案二：讀、寫操作都採用加鎖的方式。

如果我們的一些業務場景不允許讀取記錄的舊版本，而是每次都必須去讀取記錄的最新版本。比如，在銀行存款的事務中，需要先把賬戶的餘額讀出來，然後將其加上本次存款的數額，最後再寫到資料庫中。在將賬戶餘額讀取出來後，就不想讓別的事務再訪問該餘額，直到本次存款事務執行完成，其他事務才可以訪問賬戶的餘額。這樣在讀取記錄的時候就需要對其進行加鎖操作，這樣也就意味著讀操作和寫操作也像寫-寫操作那樣排隊執行。

髒讀的產生是因為當前事務讀取了另一個未提交事務寫的一條記錄，如果另一個事務在寫記錄的時候就給這條記錄加鎖，那麼當前事務就無法繼續，讀取該記錄了，所以也就不會由髒讀問題的產生了。

不可重複讀的產生是因為當前事務先讀取一條記錄，另外一個事務對該記錄做了改動之後並提交之後，當前再次讀取時會獲取到不同的值，如果在當前事務讀取記錄時，就給該記錄加鎖，那麼另一個事務就無法修改該記錄，自然也不會產生不可重複讀了。

幻讀問題的產生是因為當前事務讀取了一個範圍的記錄，然後另外的事務向該範圍內插入了新紀錄，當前事務再次讀取該範圍的記錄時發現了新插入的新記錄。採用加鎖的方式解決幻讀問題就有一些麻煩，因為當前事務在第一次讀取記錄時，幻影記錄並不存在，所以讀取時加鎖，就很尷尬（未知的記錄，無法加鎖。因為，只有查出新的記錄時，才知道那些記錄時幻影記錄，但當查出記錄時，也就已經出現了幻讀問題）。

• 小結對比發現
  • 採用MVCC方式的話，讀-寫操作彼此並不衝突，效能更高。
  • 採用加鎖方式的話，讀-寫操作彼此需要排隊執行，影響效能。
• 一般情況下我們當然願意採用MVCC來解決讀-寫操作併發執行的問題，但是業務在某些特殊情況下，要求必須採用加鎖的方式執行。下面講解MySQL中不同的鎖。

3.鎖的不同角度分類

---

鎖的分類圖，如下：

3.1 從資料操作的型別劃分：讀鎖、寫鎖

對於資料庫中併發事務的讀-讀情況並不會引起什麼問題。對於寫-寫、讀-寫或寫-讀這些情況可能會引起一些問題，需要使用MVCC或者加鎖的方式來解決它們。在使用加鎖的方式解決問題時，由於既要允許讀-讀情況不受影響，又要使寫-寫、讀-寫或寫-讀情況中的操作相互阻塞，所以MySQL實現一個由兩種型別的鎖組成的鎖系統來解決。這兩種型別的鎖通常被稱為共享鎖(Shared Lock，S Lock)和排他鎖(Exclusive Lock，X Lock)，也叫讀鎖（read lock）和寫鎖（write lock）。

• 讀鎖：也稱為共享鎖，英文用S表示。針對同一份資料，多個事務的讀操作可以同時進行而不會互相影響，相互不阻塞的。
• 寫鎖：也稱為排它鎖，英文用X表示。當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。這樣就能確保在給定的時間裡，只有一個事務能執行寫入，並防止其他使用者讀取正在寫入的同一資源。

注意，對於InnoDB引擎來說，讀鎖和寫鎖可以加在表上，也可以加在行上。

舉例（行級讀寫鎖）：如果以一個事務T1已經獲得了某個行r的讀鎖，那麼此時另外的一個事務T2是可以去獲得這個行r的讀鎖的，因為讀操作並沒有改變行r的資料；但是，如果某個事務T3想獲得行r的寫鎖，則它必須等待事務T1，T2釋放掉行r上的讀鎖才行。

總結：這裡的相容是指對同一張表或記錄的鎖的相容性情況。獲取寫鎖，必須得目標資料上的鎖，都已經釋放掉才可以。

	X鎖	S鎖
X鎖	不相容	不相容
S鎖	不相容	相容

1.鎖定讀

在採用加鎖方式解決髒讀、不可重複讀、幻讀這些問題時，讀取一條記錄時需要獲取該記錄的S鎖，其實是不嚴謹的，有時候需要在讀取記錄時就獲取記錄的X鎖，來禁止別的事務讀寫該記錄，為此MySQL提出了兩種比較特殊的SELECT語句格式：

• 對讀取的記錄加S鎖：

SELECT ... LOCK IN SHARE MODE;
#或
SELECT ... FOR SHARE;#(8.0新增語法)

在普通的SELECT語句後邊加LOCK IN SHARE MODE，如果當前事務執行了該語句，那麼它會為讀取到的記錄加S鎖，這樣允許別的事務繼續獲取這些記錄的S鎖（比如，別的事務也使用SELECT ... LOCK IN SHARE MODE語句來讀取這些記錄），但是不能獲取這些記錄的X鎖（比如，使用SELECT ... FOR UPDATE語句來讀取這些記錄，或者直接修改這些記錄）。如果別的事務想要獲取這些記錄的X鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄上的S鎖是否掉。

• 對讀取的記錄加X鎖：

SELECT ... FOR UPDATE;

在普通的SELECT語句後邊加FOR UPADTE，如果當前事務執行了該語句，那麼它會為讀取到的記錄加X鎖，這樣既不允許別的事務獲取這些記錄的S鎖（比分說別的事務使用SELECT ... LOCK IN SHARE MODE語句來讀取這些記錄），也不允許獲取這些記錄的X鎖（比如使用SELECT ... FOR UPDATE語句來讀取這些記錄，或者直接修改這些記錄）。如果別的事務想要獲取這些記錄的S鎖或者X鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄上的X鎖釋放掉。

MySQL8.0新特性：

在5.7及之前的版本，SELECT ... FOR UPDATE，如果獲取不到鎖，會一直等待，直到innodb_lock_await_timeout超時。在8.0版本中，SELECT ... FOR UPDATE，SELECT ... FOR SHARE新增NOWAIT、SKIP LOCKED語句，跳過鎖等待，或者跳過鎖定。

• 透過新增NOWAIT，SKIP LOCKED語法，能夠立刻返回。如果查詢的行已經加鎖：
  • 那麼NOWAIT會立即報錯返回。
  • 而SKIP LOCKED也會立即返回，只是返回的結果中不包含被鎖定(有share鎖，也算被鎖定)的行。

注意，使用了NOWAIT和SKIP LOCKED關鍵字，也會為目標語句加鎖，見例1，例2。

這裡，有個小點需要注意，在同一個事務裡，即使別的事務已經提交了，也就是釋放了同一張表的某些行資料的鎖，但是執行相同的查詢語句(包括SKIP LOCKED)時，也會得到相同的結果，這不是錯誤，而是MySQL預設的事務隔離級別，可重複讀(REPEATABLE-READ)，決定的。見例3

#完整的表記錄
mysql> select * from a;
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    6 |    6 |
+------+------+
6 rows in set (0.00 sec)

例1

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a where f1 = 6 for share;
+------+------+
| f1   | f2   |
+------+------+
|    6 |    6 |
+------+------+
1 row in set (0.00 sec)


#session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

#session3
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a for update skip locked;
Empty set (0.00 sec)

mysql> select * from a for update skip locked; #該條語句是在session2提交之後執行的結果
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
5 rows in set (0.00 sec)
#從上述，兩條語句結果可以看出，for update nowait，雖然會執行報錯，但是也會為目標資料加上X鎖

例2

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a where f1 = 6 for share;
+------+------+
| f1   | f2   |
+------+------+
|    6 |    6 |
+------+------+
1 row in set (0.00 sec)

#session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a for update skip locked;
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
5 rows in set (0.00 sec)

#session3
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a for update skip locked;
Empty set (0.00 sec)
#從該語句執行結果看，for update skip locked也會為目標語句加X鎖

例3

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a where f1 = 6 for share;
+------+------+
| f1   | f2   |
+------+------+
|    6 |    6 |
+------+------+
1 row in set (0.00 sec)

#session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a for update skip locked;
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from a for update skip locked; #該語句在session1 commit後執行
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
5 rows in set (0.01 sec)

2.寫操作

平常所用到的寫操作無非是DELETE、UPDATE、INSERT這三種：

• DELETE:

對一條記錄做DELETE操作的過程其實是先在B+樹中定位到這條記錄的位置，然後獲取這條記錄的X鎖，再執行delete mark操作。也可以把這個，定位刪除記錄在B+樹中位置的過程，看成是一個獲取X鎖的鎖定讀。我的理解，就是當去表中設定delete mark為1的行記錄時，需要先進行查詢，但是此查詢過程，不允許別的事務，再來讀取這些行資料了，也就是排他了，所以叫一個獲取X鎖的鎖定讀。

• UPDATE：在對一條記錄做修改操作時分為三種情況：

①情況1：未修改該記錄的鍵值(主鍵值)，並且被更新的列佔用的空間在修改前後未發生變化。

則先在B+樹中定位到該記錄的位置，然後再獲取一下記錄的X鎖，最後在原記錄的位置進行修改操作。我們也可以把這個定位待修改記錄在B+樹中位置的過程看成是一個獲取X鎖的鎖定讀。

②情況2：未修改該記錄的鍵值，並且至少有一個被更新的列佔用的空間在修改前後發生變化。

則先在B+樹中定位到這條記錄的位置，然後獲取一下記錄的X鎖，將該記錄徹底刪除掉（就是把記錄徹底移入垃圾連結串列），最後再插入一條新的記錄。這個定位待修改記錄在B+樹中位置的過程看成是一個獲取X鎖的鎖定讀，新插入的記錄由INSERT操作提供的隱式鎖進行保護。

③情況3：修改了該記錄的鍵值，則相當於再原記錄上做DELETE操作之後再來一次INSERT操作，加鎖操作，就需要按照DELETE和INSERT的規則進行了。

• INSERT:

一般情況下，新插入一條記錄的操作並不加鎖，透過一種稱之為隱式鎖的結構來保護這條新插入的記錄在本事務提交前不被別的事務訪問。

3.2 從資料操作的粒度劃分：表級鎖，頁級鎖，行鎖

為了儘可能提高資料庫的併發度，每次鎖定的資料範圍越小越好，理論上每次只鎖定操作的資料的方案會得到最大的併發度，但是管理鎖是很耗資源的事情（涉及獲取、檢查、釋放鎖等動作）。因此資料庫系統需要在高併發響應和系統效能兩方面進行平衡，這樣就產生了“鎖粒度（Lock granularity）”的概念。

對一條記錄加鎖影響的也只是這條記錄而已，我們就說這個鎖的粒度比較細；其實一個事務也可以在表級別進行加鎖，自然就被稱之為表級鎖或者表鎖，對一個表加鎖影響整個表中的記錄，我們就說這個鎖的粒度比較粗。鎖的粒度主要分為表級鎖、頁級鎖和行鎖。

1.表鎖(Table Lock)

該鎖會鎖定整張表，它是MySQL中最基本的鎖策略，並不依賴於儲存引擎（不管你是MySQL的什麼儲存引擎，對於表鎖的策略都是一樣的），並且表鎖是系統開銷最小的策略（因為粒度比較大）。由於表級鎖一次會將整個表鎖定，所以可以很好的避免死鎖問題。當然，鎖的粒度大所帶來最大的負面影響就是出現鎖資源爭用的機率也會最高，導致併發率大打折扣。

①表級別的S鎖、X鎖

在對某個表執行SELECT、INSERT、DELETE、UPDATE語句時，InnoDB儲存引擎是不會為這個表新增表級別的S鎖或者X鎖的。在對某個表執行一些諸如ALTER TABLE、DROP TABLE這類的DDL語句時，其他事務對這個表併發執行諸如SELECT、INSERT、DELETE、UPDATE的語句會發生阻塞。同理，某個事務中對某個表執行SELECT、INSERT、DELETE、UPDATE語句時，在其他會話中對這個表執行DDL語句也會發生阻塞。這個過程其實是透過在server層使用一種稱之為後設資料鎖 (英文名: Metadata Locks，簡稱MDL）結構來實現的。

一般情況下，不會使用InnoDB儲存引擎提供的姜級別的S鎖和X鎖。只會在一些特殊情況下，比方說崩潰恢復過程中用到。比如，在系統變數autocommit=0，innodb_table_locks = 1時，手動獲取InnoDB儲存引擎提供的表t的S鎖或者X鎖可以這麼寫:

• LOCK TABLES t READ：InnoDB儲存引擎會對錶t加表級別的S鎖。
• LOCK TABLES t WRITE：InnoDB儲存引擎會對錶t加表級別的X鎖。

不過儘量避免在使用InnoDB儲存引擎的表上使用LOCK TABLES這樣的手動鎖表語句，它們並不會提供什麼額外的保護，只是會降低併發能力而已。InnoDB的厲害之處還是實現了更細粒度的行鎖，關於InnoDB表級別的S鎖和X鎖大家瞭解一下就可以了。
舉例：**下面講解MyISAM引擎下的表鎖。

步驟1：建立表並新增資料

CREATE TABLE mylock(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20)
)ENGINE MYISAM;

INSERT INTO mylock(NAME) VALUES('a');

SELECT * FROM mylock;

步驟2：檢視錶上加過的鎖

SHOW OPEN TABLES;
#或者
SHOW OPEN TABLES where In_use > 0;

mysql> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| atguigudb3         | mylock                                               |      0 |           0 |
| sys                | x$wait_classes_global_by_avg_latency                 |      0 |           0 |
| sys                | x$user_summary_by_statement_type                     |      0 |           0 |
....
| performance_schema | events_waits_summary_global_by_event_name            |      0 |           0 |
+--------------------+------------------------------------------------------+--------+-------------+
362 rows in set (0.00 sec)

mysql> lock tables mylock read;#給mylock表加讀鎖
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW OPEN TABLES where In_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb3 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)

mysql> unlock tables;#解鎖
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW OPEN TABLES where In_use > 0;
Empty set (0.00 sec)

mysql> lock tables mylock write;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW OPEN TABLES where In_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb3 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW OPEN TABLES where In_use > 0;
Empty set (0.00 sec)

步驟3：測試表級別，讀鎖和寫鎖的關係

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables mylock read;#對錶加讀鎖
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;#當前事務可讀
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> update mylock sete name = 'a1' where id = 1;#當前事務不可修改
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name = 'a1' where id = 1' at line 1

mysql> select * from user1;#當前事務不可讀別的表
ERROR 1100 (HY000): Table 'user1' was not locked with LOCK TABLES

mysql> show open tables where in_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb3 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)


mysql> unlock tables;#執行後，session2的mylock表的修改語句才能執行
Query OK, 0 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)


#session2
mysql> show tables;
+----------------------+
| Tables_in_atguigudb3 |
+----------------------+
| a                    |
| b                    |
| book                 |
| class                |
| mylock               |
| student              |
| t1                   |
| t2                   |
| type                 |
| user1                |
+----------------------+
10 rows in set (0.00 sec)

mysql> select * from mylock;#別的事務可讀
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> update mylock set name = 'a2' where id = 1;#別的事務不可修改，被阻塞，等待表級鎖釋放後，才能修改
Query OK, 1 row affected (59.44 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a2   |
+----+------+
1 row in set (0.00 sec)

mysql> update mylock set name = 'a3' where id= 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user1;
ERROR 1100 (HY000): Table 'user1' was not locked with LOCK TABLES

mysql> unlock tables;#1
Query OK, 0 rows affected (0.01 sec)

mysql> lock tables mylock write;#加鎖
Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;#2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;#出現了不可重複讀問題，注意這是在myisam儲存引擎的表上
+----+------+
| id | name |
+----+------+
|  1 | a4   |
+----+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


#session2
mysql> select * from mylock;#被阻塞，直到session1中的unlock tables(1)執行完成後，才執行
+----+------+
| id | name |
+----+------+
|  1 | a3   |
+----+------+
1 row in set (6.97 sec)

mysql> update mylock set name = 'a4' where id = 1;#被阻塞，直到session1中的unlock tables(2)執行完成後，才執行
Query OK, 1 row affected (3.23 sec)
Rows matched: 1  Changed: 1  Warnings: 0

總結：

MyISAM在執行查詢語句(SELECT）前，會給涉及的所有表加讀鎖，在執行增刪改操作前，會給涉及的表加寫鎖。InnoDB儲存引擎是不會為這個表新增表級別的讀鎖或者寫鎖的。
MySQL的表級鎖有兩種模式：(以MylSAM表進行操作的演示)

• 表共享讀鎖(Table Read Lock)
• 表獨佔寫鎖(Table Write Lock)

鎖型別	自己可讀	自己可寫	自己可操作其他表	他人可讀	他人可寫
讀鎖	是	否	否	是	否，等
寫鎖	是	是	否	否，等	否，等

②意向鎖（intention lock）

InnoDB支援多粒度鎖(multiple granularity locking)，它允許行級鎖與表級鎖共存，而意向鎖就是其中的一種表鎖。

1、意向鎖的存在是為了協調行鎖和表鎖的關係，支援多粒度（表鎖與行鎖)的鎖並存。

2、意向鎖是一種不與行級鎖衝突的表級鎖，這一點非常重要。

3、表明“某個事務正在某些行持有了鎖或該事務準備去持有鎖”。

意向鎖分為兩種:

• 意向共享鎖（intention shared lock，IS）：事務有意向對錶中的某些行加共享鎖（S鎖）

-- 事務要獲取某些行的S鎖，必須先獲得表的IS鎖
SELECT column FROM table ... LOCK IN SHARE MODE; 
#或
SELECT column FROM table ... FOR SHARE;//8.0之後 

• 意向排他鎖（intention exclusive lock，IX）：事務有意向對錶中的某些行加排他鎖（X鎖）

-- 事務要獲取某些行的X鎖，必須先獲得表的IX鎖
SELECT column FROM table ... FOR UPDATE;

即：意向鎖是由儲存引擎自己維護的，使用者無法手動操作意向鎖，在為資料行加共享/排他鎖之前，lnooDB會先獲取該資料行所在資料表的對應意向鎖。

1.意向鎖要解決的問題

現在有兩個事務，分別是T1和T2，其中T2試圖在該表級別上應用共享或排它鎖，如果沒有意向鎖存在，那麼T2就需要去檢查各個頁或行是否存在鎖;如果存在意向鎖，那麼此時就會受到由T1控制的表級別意向鎖的阻塞。T2在鎖定該表前不必檢查各個頁或行鎖，而只需檢查表上的意向鎖。簡單來說就是給更大一級別的空間示意裡面是否已經上過鎖。

在資料表的場景中，如果我們給某一行資料加上了排它鎖，資料庫會自動給更大一級的空間，比如資料頁或資料表加上意向鎖，告訴其他人這個資料頁或資料表已經有人上過排它鎖了，這樣當其他人想要獲取資料表排它鎖的時候，只需要瞭解是否有人已經獲取了這個資料表的意向排他鎖即可。

• 如果事務想要獲得資料表中某些記錄的共享鎖，就需要在資料表上新增意向共享鎖。
• 如果事務想要獲得資料表中某些記錄的排他鎖，就需要在資料表上新增意向排他鎖。

這時，意向鎖會告訴其他事務已經有人鎖定了表中的某些記錄。

舉例：建立表teacher，插入6條資料，事務的隔離級別預設為Repeatable-Read，如下所示。

CREATE TABLE `teacher`(
	`id` int NOT NULL,
    `name` varchar(255) NOT NULL,
    primary key(`id`)
)ENGINE=InnoDB DEFAULT CHARSET = utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `teacher` VALUES(1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu'),(5,'wind'),(6,'breeze');

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

假設事務A獲取了某一行的排他鎖，並未提交，語句如下所示。

begin;

SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

事務B想要獲取teacher表的表讀鎖，語句如下。

begin;

LOCK TABLES teacher READ;

因為共享鎖與排他鎖互斥，所以事務B在試圖對teacher表加共享鎖的時候，必須保證兩個條件。

(1）當前沒有其他事務持有teacher 表的排他鎖。

(2）當前沒有其他事務持有teacher表中任意一行的排他鎖。

為了檢測是否滿足第二個條件，事務B必須在確保teacher表不存在任何排他鎖的前提下，去檢測表中的每一行是否存在排他鎖。很明顯這是一個效率很差的做法，但是有了意向鎖之後，情況就不一樣了。

意向鎖是怎麼解決這個問題的呢?首先，我們需要知道意向鎖之間的相容互斥性，如下所示。

	意向共享鎖（IS）	意向排他鎖（IX）
意向共享鎖（IS）	相容	相容
意向排他鎖（IX）	相容	相容

即意向鎖之間是互相相容的，雖然意向鎖和自家兄弟互相相容，但是它會與普通的排他/共享鎖互斥。

	意向共享鎖（IS）	意向排他鎖（IX）
共享鎖（S）	相容	互斥
排他鎖（X）	互斥	互斥

注意，這裡的排他 / 共享鎖，指的是表鎖，意向鎖不會與行級的共享 / 排他鎖互斥。回到剛才teacher表的例子。

事務A獲取了某一行的排他鎖，並未提交：

begin;

SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

此時teacher表存在兩把鎖，teacher表上的意向排他鎖與id為6的資料行上的排他鎖。事務B想要獲取teacher表的共享鎖。

begin;

LOCK TABLES teacher READ;

此時事務B檢測事務A持有teacher表的意向排他鎖，就可以得知事務A必然持有該表中某些資料行的排他鎖，那麼事務B對teacher表的加鎖請求就會被排斥〈阻塞)，而無需去檢測表中的每一行資料是否存在排他鎖。

意向鎖的併發性

意向鎖不會與行級的共享/排他鎖互斥! 正因為如此，意向鎖並不會影響到多個事務對不同資料行加排他鎖時的併發性。(不然我們直接用普通的表鎖就行了)。

我們擴充套件一下上面teacher表的例子來概括一下意向鎖的作用（一條資料從被鎖定到被釋放的過程中，可能存在多種不同鎖，但是這裡我們只著重表現意向鎖)。

事務A先獲取了某一行的排他鎖，並未提交:

BEGIN;

SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

事務A獲取了teacher表上的意向排他鎖，事務A獲取了id為6的資料行上的排他鎖。之後事務B想要獲取teacher表的共享鎖。

begin;

LOCK TABLES teacher READ;

事務B檢測到事務A持有teacher表的意向排他鎖。事務B對teacher表的加鎖請求被阻塞（排斥)。最後事務C也想獲取teacher表中某一行的排他鎖。

BEGIN;

SELECT * FROM teacher WHERE id = 5 FOR UPDATE;

事務C申請teacher表的意向排他鎖。事務C檢測到事務A持有teacher表的意向排他鎖。因為意向鎖之間並不互斥，所以事務C獲取到了teacher表的意向排他鎖。因為id為5的資料行上不存在任何排他鎖，最終事務C成功獲取到了該資料行上的排他鎖。

從上面的案例可以得到如下結論：

• InnoDB支援多粒度鎖，特定場景下，行級鎖可以與表級鎖共存。
• 意向鎖之間互不排斥，但除了IS與S相容外，意向鎖會與 共享鎖 / 排他鎖 互斥。
• IX，IS是表級鎖，不會和行級的X，S鎖發生衝突。只會和表級的X，S發生衝突。
• 意向鎖在保證併發性的前提下，實現了行鎖和表鎖共存且滿足事務隔離性的要求。

③自增鎖（AUTO-INC鎖）瞭解

在使用MySQL過程中，我們可以為表的某個列新增AUTO_INCREMENT屬性。舉例

CRAETE TABLE `teacher`(
	`id` int not null AUTO_INCREMENT,
    `name` varchar(255) not null,
    primary key(`id`)
)engine=innodb default charset=utf8mb4 collate = utf8mb4_0900_ai_ci;

由於這個表的id欄位宣告瞭AUTO_INCREMENT，意味著在書寫插入語句時不需要為其賦值，SQL語句修改如下所示

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');

上邊的插入語句並沒有為id列顯式賦值，所以系統會自動為它賦上遞增的值，結果如下所示。

mysql> select * from teacher;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

現在我們看到的上面插入資料只是一種簡單的插入模式，所有插入資料的方式總共分為三類，分別是“Simple inserts”，“Bulk inserts”和“Mixed-mode inserts”。

1. “Simple inserts” （簡單插入）

可以預先確定要插入的行數（當語句被初始處理時）的語句。包括沒有巢狀子查詢的單行和多行INSERT...VALUES()和REPLACE語句。比如我們上面舉的例子就屬於該類插入，已經確定要插入的行數。

2. “Bulk inserts” （批次插入）

事先不知道要插入的行數（和所需自動遞增值的數量）的語句。比如INSERT ... SELECT，REPLACE... SELECT和LOAD DATA語句，但不包括純INSERT。 InnoDB在每處理一行，為AUTO_INCREMENT列分配一個新值。

3. “Mixed-mode inserts” （混合模式插入）

這些是“Simple inserts”語句但是指定部分新行的自動遞增值。例如INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');只是指定了部分id的值。另一種型別的“混合模式插入”是INSERT ... ON DUPLICATE KEY UPDATE。

對於上面資料插入的案例，MySQL中採用了自增鎖的方式來實現，AUTO-INC鎖是當向使用含有AUTO_INCREMENT列的表中插入資料時需要獲取的一種特殊的表級鎖，在執行插入語句時就在表級別加一個AUTO-INC鎖，然後為每條待插入記錄的AUTO_INCREMENT修飾的列分配遞增的值，在該語句執行結束後，再把AUTO-INC鎖釋放掉。一個事務在持有AUTO-INC鎖的過程中，其他事務的插入語句都要被阻塞，可以保證一個語句中分配的遞增值是連續的。也正因為此，其併發性顯然並不高，當我們向一個有AUTO_INCREMENT關鍵字的主鍵插入值的時候，每條語句都要對這個表鎖進行競爭，這樣的併發潛力其實是很低下的，所以innodb透過innodb_autoinc_lock_mode的不同取值來提供不同的鎖定機制，來顯著提高sQL語句的可伸縮性和效能。

innodb_autoinc_lock_mode有三種取值，分別對應與不同鎖定模式：

（1）innodb_autoinc_lock_mode = 0(“傳統”鎖定模式)

在此鎖定模式下，所有型別的insert語句都會獲得一個特殊的表級AUTO-INC鎖，用於插入具有AUTO_INCREMENT列的表。這種模式其實就如我們上面的例子，即每當執行insert的時候，都會得到一個表級鎖(AUTO-INC鎖)，使得語句中生成的auto_increment為順序，且在binlog中重放的時候，可以保證master與slave中資料的auto_increment是相同的。因為是表級鎖，當在同一時間多個事務中執行insert的時候，對於AUTO-INC鎖的爭奪會限制併發能力。

（2）innodb_autoinc_lock_mode = 1(“連續”鎖定模式)

在 MySQL 8.0 之前，連續鎖定模式是預設的。

在這個模式下，“bulk inserts”仍然使用AUTO-INC表級鎖，並保持到語句結束。這適用於所有INSERT ...SELECT，REPLACE ... SELECT和LOAD DATA語句。同一時刻只有一個語句可以持有AUTO-INC鎖。

對於“Simple inserts”（要插入的行數事先已知），則透過在mutex（輕量鎖）的控制下獲得所需數量的自動遞增值來避免表級AUTO-INC鎖， 它只在分配過程的持續時間內保持，而不是直到語句完成。不使用表級AUTO-INC鎖，除非AUTO-INC鎖由另一個事務保持。如果另一個事務保持AUTO-INC鎖，則“Simpleinserts”等待AUTO-INC鎖，如同它是一個“bulk inserts”。

（3）innodb_autoinc_lock_mode = 2(“交錯”鎖定模式)

從 MySQL 8.0 開始，交錯鎖模式是 預設 設定。

在這種鎖定模式下，所有類INSERT語句都不會使用表級AUTO-INC鎖，並且可以同時執行多個語句。這是最快和最可擴充套件的鎖定模式，但是當使用基於語句的複製或恢復方案時，從二進位制日誌重播SQL語句時，這是不安全的。

在此鎖定模式下，自動遞增值保證在所有併發執行的所有型別的insert語句中是唯一且單調遞增的。但是，由於多個語句可以同時生成數字（即，跨語句交叉編號)，為任何給定語句插入的行生成的值可能不是連續的。

如果執行的語句是“simple inserts”，其中要插入的行數已提前知道，為單個語句生成的數字不會有間隙。然而，當執行"bulk inserts"，或者"Mixed-mode inserts "時，在由任何給定語句分配的自動遞增值中可能存在間隙。

④後設資料鎖（MDL鎖）

MySQL5.5引入了meta data lock，簡稱MDL鎖，屬於表鎖範疇。MDL的作用是，保證讀寫的正確性。比如，如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，增加了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。

因此，當對一個表做增刪改查操作的時候，加MDL讀鎖; 當要對錶做結構變更操作的時候，加MDL寫鎖。

讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的用來保證變更表結構操作的安全性，解決了DML和DDL操作之間的一致性問題。不需要顯式使用，在訪問一個表的時候會被自動加上。

舉例：後設資料鎖的使用場景模擬

會話A：從表中查詢資料

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from teacher;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

會話B：改變表結構

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table teacher add age int after name;#阻塞

mysql> alter table teacher add age int after name;#會話A，提交後結果
Query OK, 0 rows affected (3 min 5.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

會話C：檢視當前MySQL的程序，從下圖中看到阻塞原因，metedata lock

透過會話C可以看出會話B被阻塞，這是由於會話A拿到了teacher表的後設資料讀鎖，會話B想申請teacher表的後設資料寫鎖，由於讀寫鎖互斥，會話B需要等待會話A釋放後設資料鎖才能執行。

後設資料鎖可能帶來的問題

SessionA	SessionB	SessionC
begin;select * from teacher;
	alter table teacher add age int;
		select * from teacher;

我們可以看到 session A會對錶teacher加一個MDL讀鎖，之後session B要加MDL寫鎖會被 blocked，因為session A的MDL讀鎖還沒有釋放，而session C要在表teacher上新申請MDL讀鎖的請求也會被session B阻塞。前面我們說了，所有對錶的增刪改查操作都需要先申請MDL讀鎖，就都被阻塞，等於這個表現在完全不可讀寫了。

2.InnoDB中的行鎖

行鎖（Row Lock)也稱為記錄鎖，顧名思義，就是鎖住某一行（某條記錄row)。需要的注意的是，MySQL伺服器層並沒有實現行鎖機制，行級鎖只在儲存引擎層實現。

優點：鎖定力度小，發生鎖衝突機率低，可以實現的併發度高。

缺點：對於鎖的開銷比較大，加鎖會比較慢，容易出現死鎖情況。

InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）;二是採用了行級鎖。

首先我們建立表如下：

CREATE TABLE student1(
    id INT,
    `name` VARCHAR(20),
    class VARCHAR(10),
    PRIMARY KEY(id)
)ENGINE=INNODB CHARSET utf8;

向這個表裡插入幾條記錄：

INSERT INTO student1 
VALUES( 1,'張三','一班'),
( 3,'李四','一班'),
( 8,'王五','二班'),
( 15,'趙六','二班'),
(20,'錢七','三班');

mysql> select * from student1;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 張三   | 一班   |
|  3 | 李四   | 一班   |
|  8 | 王五   | 二班   |
| 15 | 趙六   | 二班   |
| 20 | 錢七   | 三班   |
+----+--------+--------+
5 rows in set (0.00 sec)

student1表中的聚簇索引的簡圖如下所示。

這裡把B+樹的索引結構做了一個超級簡化，只把索引中的記錄給拿了出來，下面看看都有哪些常用的行鎖型別。

①記錄鎖（Record Locks）

記錄鎖也就是僅僅把一條記錄鎖上，官方的型別名稱為: LOCK_REC_NOT_GAP。比如我們把id值為8的那條記錄加一個記錄鎖的示意圖如圖所示。僅僅是鎖住了id值為8的記錄，對周圍的資料沒有影響。

記錄鎖是有S鎖和X鎖之分的，稱之為S型記錄鎖和X型記錄鎖。

• 當一個事務獲取了一條記錄的S型記錄鎖後，其他事務也可以繼續獲取該記錄的S型記錄鎖，但不可以繼續獲取X型記錄鎖;
• 當一個事務獲取了一條記錄的X型記錄鎖後，其他事務既不可以繼續獲取該記錄的S型記錄鎖，也不可以繼續獲取X型記錄鎖。

和之前表級別的S鎖和X鎖類似，只是鎖的粒度變為了一行記錄，或者多行記錄，不再進行舉例。

②間隙鎖（Gap Locks）

MySQL在REPEATABLE READ隔離級別下是可以解決幻讀問題的，解決方案有兩種，可以使用MVCC方案解決，也可以採用加鎖方案解決。但是在使用加鎖方案解決時有個大問題，就是事務在第一次執行讀取操作時，那些幻影記錄尚不存在，我們無法給這些幻影記錄加上記錄鎖。InnoDB提出了一種稱之為Gap Locks的鎖，官方的型別名稱為: LOCK_GAP，我們可以簡稱為gap鎖。比如，把id值為s的那條記錄加一個gap鎖的示意圖如下。

圖中id值為8的記錄加了gap鎖，意味著不允許別的事務在id值為8的記錄前邊的間隙插入新記錄，其實就是id列的值(3,8)這個區間的新記錄是不允許立即插入的。比如，有另外一個事務再想插入一條id值為4的新記錄，它定位到該條新記錄的下一條記錄的id值為8，而這條記錄上又有一個gap鎖，所以就會阻塞插入操作，直到擁有這個gap鎖的事務提交了之後，id列的值在區間(3,8)中的新記錄才可以被插入。

gap鎖的提出僅僅是為了防止插入幻影記錄而提出的。雖然有共享gap鎖和獨佔gap鎖這樣的說法，但是它們起到的作用是相同的。而且如果對一條記錄加了gap鎖（不論是共享gap鎖還是獨佔gap鎖)，並不會限制其他事務對這條記錄加記錄鎖或者繼續加gap鎖。

舉例：

Session1	Session2
select * from student1 where id = 5 for share;
	select * from student1 where id=5 for update;

這裡session 2並不會被阻塞。因為表裡並沒有id=5這個記錄，因此session 1 加的是間隙鎖(3,8)。而session 2也是在這個間隙加的間隙鎖。它們有共同的目標，即:保護這個間隙，不允許插入值。但，它們之間是不衝突的。

注意，給一條記錄加了gap鎖只是不允許其他事務往這條記錄前邊的間隙插入新記錄，那對於最後一條記錄之後的間隙，也就是student 表中id值為20的記錄之後的間隙該咋辦呢？也就是說給哪條記錄加gap鎖才能阻止其他事務插入id值在(20，+oo)這個區間的新記錄呢?這時候我們在講資料頁時介紹的兩條偽記錄派上用場了:

• Infimum記錄，表示該頁面中最小的記錄。
• Supremum記錄，表示該頁面中最大的記錄。

為了實現阻止其他事務插入id值在(20, +oo)這個區間的新記錄，我們可以給索引中的最後一條記錄，也就是id值為2o的那條記錄所在頁面的Supremum記錄加上一個gap鎖，如圖所示。

mysql> select * from student1 where id>20 for share;#這樣就可以阻止其他事務插入id值在(20,+co)這個區間的新記錄。
Empty set (0.00 sec)

檢測

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139685974654544:1114:139685892562304
ENGINE_TRANSACTION_ID: 26227
            THREAD_ID: 58
             EVENT_ID: 30
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139685892562304
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139685974653688:1114:139685892556112
ENGINE_TRANSACTION_ID: 26221
            THREAD_ID: 56
             EVENT_ID: 40
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139685892556112
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139685974653688:55:4:3:139685892553120
ENGINE_TRANSACTION_ID: 26221
            THREAD_ID: 56
             EVENT_ID: 40
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139685892553120
            LOCK_TYPE: RECORD
            LOCK_MODE: S,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139685974653688:55:4:3:139685892553464
ENGINE_TRANSACTION_ID: 26221
            THREAD_ID: 56
             EVENT_ID: 41
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139685892553464
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139685974652832:1114:139685892549840
ENGINE_TRANSACTION_ID: 421160951363488
            THREAD_ID: 55
             EVENT_ID: 91
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139685892549840
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139685974652832:55:4:1:139685892546736
ENGINE_TRANSACTION_ID: 421160951363488
            THREAD_ID: 55
             EVENT_ID: 91
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139685892546736
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record #supermum記錄
6 rows in set (0.01 sec) 

間隙鎖的引入，可能會導致同樣的語句鎖住更大的範圍，這其實是影響了併發度的。下面的例子會產生死鎖。

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student1 where id = 5 for share;#id=5記錄不存在，在(3,8)這個區間加上間隙鎖
Empty set (0.00 sec)

mysql> insert into student1 values(6,'jerry','一班');#執行這個後，會發生死鎖，後面有解釋
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction




#session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student1 where id = 5 for share;#id=5記錄不存在，在(3,8)這個區間加上間隙鎖
Empty set (0.00 sec)

mysql> insert into student1 values(4,'tom','二班');#先執行這個，會阻塞
Query OK, 1 row affected (27.89 sec)

③臨鍵鎖（Next-Key Locks）

有時候我們既想鎖住某條記錄，又想阻止其他事務在該記錄前邊的間隙插入新記錄，所以InnoDB就提出了一種稱之為Next-Key Locks的鎖，官方的型別名稱為：LOCK_ORDINARY，我們也可以簡稱為next-key鎖。Next-KeyLocks是在儲存引擎innodb、事務級別在可重複讀的情況下使用的資料庫鎖,innodb預設的鎖就是Next-Key locks。比如，我們把id值為8的那條記錄加一個next-key鎖的示意圖如下:

next-key鎖的本質就是一個記錄鎖和一個gap鎖的合體，它既能保護該條記錄，又能阻止別的事務將新記錄插入被保護記錄前邊的間隙。

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student1;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三1   | 一班   |
|  3 | 李四    | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七    | 三班   |
+----+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from student1 where id <= 8 and id > 3 for update;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  8 | 王五   | 二班   |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student1 where id = 8 for share;#被阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into student1 values(7,'wind','三班');#被阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

④插入意向鎖（Insert Intention Locks）

我們說一個事務在插入一條記錄時需要判斷一下插入位置是不是被別的事務加了gap鎖(next-key鎖也包含gap鎖），如果有的話，插入操作需要等待，直到擁有gap鎖的那個事務提交。但是InnoDB規定事務在等待的時候也需要在記憶體中生成一個鎖結構，表明有事務想在某個間隙中插入新記錄，但是現在在等待。InnoDB就把這種型別的鎖命名為Insert Intention Locks，官方的型別名稱為：LOCK_INSERT_INTENTION，稱為插入意向鎖。插入意向鎖是一種Gap鎖，不是意向鎖，在insert操作時產生。

插入意向鎖是在插入一條記錄行前，由INSERT操作產生的一種間隙鎖。該鎖用以表示插入意向，當多個事務在同一區間（gap）插入位置不同的多條資料時，事務之間不需要互相等待。假設存在兩條值分別為4和7的記錄，兩個不同的事務分別試圖插入值為5和6的兩條記錄，每個事務在獲取插入行上獨佔的（排他）鎖前，都會獲取(4，7)之間的間隙鎖，但是因為資料行之間並不衝突，所以兩個事務之間並不會產生衝突（阻塞等待)。總結來說，插入意向鎖的特性可以分成兩部分:

(1）插入意向鎖是一種特殊的間隙鎖—―間隙鎖可以鎖定開區間內的部分記錄。

(2）插入意向鎖之間互不排斥，所以即使多個事務在同一區間插入多條記錄，只要記錄本身（主鍵、唯一索引）不衝突，那麼事務之間就不會出現衝突等待。

注意，雖然插入意向鎖中含有意向鎖三個字，但是它並不屬於意向鎖而屬於間隙鎖，因為意向鎖是表鎖而插入意向鎖是行鎖。

比如，把id值為8的那條記錄加一個插入意向鎖的示意圖如下：

比如，現在T1為id值為8的記錄加了一個gap鎖，然後T2和T3分別想向student表中插入id值分別為4，5的兩條記錄，所以現在為id值為8的記錄加的鎖的示意圖就如下所示:

從圖中可以看到，由於T1持有gap鎖，所以T2和T3需要生成一個插入意向鎖的鎖結構並且處於等待狀態。當T1提交後會把它獲取到的鎖都釋放掉，這樣T2和T3就能獲取到對應的插入意向鎖了（本質上就是把插入意向鎖對應鎖結構的is_waiting屬性改為false)，T2和T3之間也並不會相互阻塞，它們可以同時獲取到id值為s的插入意向鎖，然後執行插入操作。事實上插入意向鎖並不會阻止別的事務繼續獲取該記錄上任何型別的鎖。

3.頁鎖

頁鎖就是在頁的粒度上進行鎖定，鎖定的資料資源比行鎖要多，因為一個頁中可以有多個行記錄。當我們使用頁鎖的時候，會出現資料浪費的現象，但這樣的浪費最多也就是一個頁上的資料行。頁鎖的開銷介於表鎖和行鎖之間，會出現死鎖。鎖定粒度介於表鎖和行鎖之間，併發度一般。

每個層級的鎖數量是有限制的，因為鎖會佔用記憶體空間，鎖空間的大小是有限的。當某個層級的鎖數量超過了這個層級的閾值時，就會進行鎖升級。鎖升級就是用更大粒度的鎖替代多個更小粒度的鎖，比如InnoDB中行鎖升級為表鎖，這樣做的好處是佔用的鎖空間降低了，但同時資料的併發度也下降了。

3.3 從對待鎖的態度劃分：樂觀鎖、悲觀鎖

從對待鎖的態度來看鎖的話，可以將鎖分成樂觀鎖和悲觀鎖，從名字中也可以看出這兩種鎖是兩種看待資料併發的思維方式。需要注意的是，樂觀鎖和悲觀鎖並不是鎖，而是鎖的設計思想。

1.悲觀鎖（Pessimistic Locking）

悲觀鎖是一種思想，顧名思義，就是很悲觀，對資料被其他事務的修改持保守態度，會透過資料庫自身的鎖機制來實現，從而保證資料操作的排它性。

悲觀鎖總是假設最壞的情況，每次去拿資料的時候都認為別人會修改，所以每次在拿資料的時候都會上鎖，這樣別人想拿這個資料就會阻塞直到它拿到鎖（共享資源每次只給一個執行緒使用，其它執行緒阻塞，用完後再把資源轉讓給其它執行緒)。比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖，當其他執行緒想要訪問資料時，都需要阻塞掛起。Java中synchronized和ReentrantLock等獨佔鎖就是悲觀鎖思想的實現。

秒殺案例1

商品秒殺過程中，庫存數量的減少，避免出現超賣的情況。比如，商品表中有一個欄位為quantity表示當前該商品的庫存量。假設商品為華為mate40，id為1001，quantity=100個。如果不使用鎖的情況下，操作方法如下所示

#第1步:查出商品庫存
select quantity from items where id = 1001 ;
#第2步:如果庫存大於0，則根據商品資訊生產訂單
insert into orders (item_id)values( 1001) ;
#第3步:修改商品的庫存，num表示購買數量
update items set quantity = quantity-num where id = 1001;

這樣寫的話，在併發量小的公司沒有大的問題，但是如果在高併發環境下可能出現以下問題

	執行緒A	執行緒B
1	step1（查詢還有100部）	step1（查詢還有100部）
2		step2（生成訂單）
3	step2（生成訂單）
4		step3（減庫存1）
5	step3（減庫存2）

其中執行緒B此時已經下單並且減完庫存，這個時候執行緒A依然去執行step3，就造成了超賣。

我們使用悲觀鎖可以解決這個問題，商品資訊從查詢出來到修改，中間有一個生成訂單的過程，使用悲觀鎖的原理就是，當我們在查詢items資訊後就把當前的資料鎖定，直到我們修改完畢後再解鎖。那麼整個過程中，因為資料被鎖定了，就不會出現有第三者來對其進行修改了。而這樣做的前提是需要將要執行的SQL語句放在同一個事務中，否則達不到鎖定資料行的目的。

修改如下：

#第1步:查出商品庫存
select quantity from items where id = 1001 for update;

#第2步:如果庫存大於0，則根據商品資訊生產訂單
insert into orders (item_id) values (1001);

#第3步:修改商品的庫存，num表示購買數量
update items set quantity = quantity-num where id = 1001;

select .... for update是MySQL中悲觀鎖。此時在items表中，id為1001的那條資料就被我們鎖定了，其他的要執行select quantity from items where id = 1001 for update;語句的事務必須等本次事務提交之後才能執行。這樣我們可以保證當前的資料不會被其它事務修改。

注意，當執行select quantity from items where id = 1001 for update;語句之後，如果在其他事務中執行select quantity from items where id = 1001;語句（因為該語句沒有加鎖，只是查詢資料，所以不會阻塞），並不會受第一個事務的影響，仍然可以正常查詢出資料.

注意: select .. for update語句執行過程中所有掃描的行都會被鎖上，因此在MySQL中用悲觀鎖必須確定使用了索引，而不是全表掃描，否則將會把整個表鎖住。

悲觀鎖不適用的場景較多，它存在一些不足，因為悲觀鎖大多數情況下依靠資料庫的鎖機制來實現，以保證程式的併發訪問性，同時這樣對資料庫效能開銷影響也很大，特別是長事務而言，這樣的開銷往往無法承受，這時就需要樂觀鎖。

2.樂觀鎖（Optimistic Locking）

樂觀鎖認為對同一資料的併發操作不會總髮生，屬於小機率事件，不用每次都對資料上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個資料，也就是不採用資料庫自身的鎖機制，而是透過程式來實現。在程式上，我們可以採用版本號機制或者CAS機制實現。樂觀鎖適用於多讀的應用型別，這樣可以提高吞吐量。在Java中java.util.concurrent.atomic包下的原子變數類就是使用了樂觀鎖的一種實現方式:CAS實現的。

1.樂觀鎖的版本號機制

在表中設計一個版本欄位 version，第一次讀的時候，會獲取version欄位的取值。然後對資料進行更新或刪除操作時，會執行UPDATE ... SET version=version+1 WHERE version = version。此時如果已經有事務對這條資料進行了更改，修改就不會成功。

這種方式類似我們熟悉的SVN、CVS版本管理系統，當我們修改了程式碼進行提交時，首先會檢查當前版本號與伺服器上的版本號是否一致，如果一致就可以直接提交，如果不一致就需要更新伺服器上的最新程式碼然後再進行提交。

2.樂觀鎖的時間戳機制

時間戳和版本號機制一樣，也是在更新提交的時候，將當前資料的時間戳和更新之前取得的時間戳進行比較，如果兩者一致則更新成功，否則就是版本衝突。

你能看到樂觀鎖就是程式設計師自己控制資料併發操作的許可權，基本是透過給資料行增加一個戳（版本號或者時間戳)，從而證明當前拿到的資料是否最新。

秒殺案例2

#第1步:查出商品庫存
select quantity,version from items where id = 1001;

#第2步:如果庫存大於0，則根據商品資訊生產訂單
insert into orders (item_id) values (1001);

#第3步:修改商品的庫存，num表示購買數量
update items set quantity = quantity-num,version = version + 1 where id = 1001 and version = #{version};
-- version值，是第一步查出的

注意，如果資料表是讀寫分離的表,當matser表中寫入的資料沒有及時同步到slave表中時，會造成更新一直失財的問題。此時需要強制讀取master表中的資料〈即將select語句放到事務中即可，這時候查詢的就是master主庫了。)

如果我們對同一條資料進行頻繁的修改的話，那麼就會出現這麼一種場景，每次修改都只有一個事務能更新成功，在業務感知上面就有大量的失敗操作。我們把程式碼修改如下:

#第1步:查出商品庫存
select quantity,version from items where id = 1001;

#第2步:如果庫存大於0，則根據商品資訊生產訂單
insert into orders (item_id) values (1001);

#第3步:修改商品的庫存，num表示購買數量
update items set quantity = quantity-num where id = 1001 and quantity-num > 0;
-- version值，是第一步查出的

這樣就會每次修改都能成功，而且不會出現超賣的現象。

3.兩種鎖的適用場景

從這兩種鎖的設計思想中，我們總結一下樂觀鎖和悲觀鎖的適用場景：

1.樂觀鎖適合讀操作多的場景，相對來說寫的操作比較少。它的優點在於 程式實現，不存在死鎖問題，不過適用場景也會相對樂觀，因為它阻止不了除了程式以外的資料庫操作。

2.悲觀鎖適合寫操作多的場景，因為寫的操作具有排它性。採用悲觀鎖的方式，可以在資料庫層面阻止其他事務對該資料的操作許可權，防止讀 - 寫和寫 - 寫的衝突。

3.4 按加鎖的方式劃分：顯示鎖，隱式鎖

1.隱式鎖

一個事務在執行INSERT操作時，如果即將插入的間隙已經被其他事務加了gap鎖，那麼本次INSERT操作會阻塞，並且當前事務會在該間隙上加一個插入意向鎖，否則一般情況下INSERT操作是不加鎖的。那如果一個事務首先插入了一條記錄（此時並沒有在記憶體生產與該記錄關聯的鎖結構)，然後另一個事務:

• 立即使用SELECT ... LOCK IN SHARE MODE語句讀取這條記錄，也就是要獲取這條記錄的S鎖，或者使用SELECT ... FOR UPDATE語句讀取這條記錄，也就是要獲取這條記錄的X鎖，怎麼辦？如果允許這種情況的發生，那麼可能產生髒讀問題。
• 立即修改這條記錄，也就是要獲取這條記錄的X鎖，怎麼辦？如果允許這種情況的發生，那麼可能產生髒寫問題。

這時候我們前邊提過的事務id又要起作用了。我們把聚簇索引和二級索引中的記錄分開看一下:

• 情景一：對於聚簇索引記錄來說，有一個trx_id隱藏列，該隱藏列記錄著最後改動該記錄的事務id。那麼如果在當前事務中新插入一條聚簇索引記錄後，該記錄的trx_id隱藏列代表的的就是當前事務的事務id，如果其他事務此時想對該記錄新增S鎖或者X鎖時，首先會看一下該記錄的trx_id隱藏列代表的事務是否是當前的活躍事務，如果是的話，那麼就幫助當前（trx_id代表的）事務建立一個X鎖（也就是為當前事務建立一個鎖結構，is_waiting屬性是false )，然後自己進入等待狀態（也就是為自己也建立一個鎖結構，is_waiting屬性是true)。
• 情景二：對於二級索引記錄來說，本身並沒有trx_id隱藏列，但是在二級索引頁面的Page Header部分有一個PAGE_MAX_TRX_ID屬性，該屬性代表對該頁面做改動的最大的事務id，如果PAGE_MAX_TRX_ID屬性值小於當前最小的活躍事務id，那麼說明對該頁面做修改的事務都已經提交了，否則就需要在頁面中定位到對應的二級索引記錄，然後回表找到它對應的聚簇索引記錄，然後再重複情景一的做法。

即:一個事務對新插入的記錄可以不顯式的加鎖（生成一個鎖結構)，但是由於事務id的存在，相當於加了一個隱式鎖。別的事務在對這條記錄加S鎖或者X鎖時，由於隱式鎖的存在，會先幫助當前事務生成一個鎖結構，然後自己再生成一個鎖結構後進入等待狀態。隱式鎖是一種延遲加鎖的機制，從而來減少加鎖的數量。

隱式鎖在實際記憶體物件中並不含有這個鎖資訊。只有當產生鎖等待時,隱式鎖轉化為顯式鎖。

InnoDB的 insert操作，對插入的記錄不加鎖，但是此時如果另一個執行緒進行當前讀，類似以下的用例，session 2會鎖等待session 1，那麼這是如何實現的呢?

#session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student1;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三1   | 一班   |
|  3 | 李四    | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七    | 三班   |
+----+---------+--------+
5 rows in set (0.01 sec)

mysql> insert into student1 values(2,'tom','一班');
Query OK, 1 row affected (0.00 sec)

#session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student1 for share;#被阻塞
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

#session3
mysql> SELECT * FROM performance_schema.data_lock_waits\G;#隱式鎖現身
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 140190716117232:55:4:9:140190618301032
REQUESTING_ENGINE_TRANSACTION_ID: 421665692827888
            REQUESTING_THREAD_ID: 48
             REQUESTING_EVENT_ID: 45
REQUESTING_OBJECT_INSTANCE_BEGIN: 140190618301032
         BLOCKING_ENGINE_LOCK_ID: 140190716116376:55:4:9:140190618294872
  BLOCKING_ENGINE_TRANSACTION_ID: 26655
              BLOCKING_THREAD_ID: 48
               BLOCKING_EVENT_ID: 45
  BLOCKING_OBJECT_INSTANCE_BEGIN: 140190618294872
1 row in set (0.00 sec)

ERROR: 
No query specified

隱式鎖的邏輯過程如下:

A. InnoDB的每條記錄中都一個隱含的trx_id欄位，這個欄位存在於聚簇索引的B+Tree中。

B.在操作一條記錄前，首先根據記錄中的trx_id檢查該事務是否是活動的事務(未提交或回滾)。如果是活動的事務，首先將隱式鎖轉換為顯式鎖(就是為該事務新增一個鎖)。

c.檢查是否有鎖衝突，如果有衝突，建立鎖，並設定為waiting狀態。如果沒有衝突不加鎖，跳到E。

D.等待加鎖成功，被喚醒，或者超時。

E.寫資料，並將自己的trx_id寫入trx_id欄位。

2.顯示鎖

透過特定的語句進行加鎖，我們一般稱之為顯示加鎖，例如：

顯示加共享鎖：

select .... lock in share mode;

顯示加排它鎖：

select .... for update;

3.5 其它鎖之：全域性鎖

全域性鎖就是對全部的資料庫例項加鎖。當你需要讓整個庫處於只讀狀態的時候，可以適用這個命令，之後其他執行緒的一下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括建表，修改表結構等）和更新事務的提交語句。全域性鎖的典型使用場景是：做全庫邏輯備份。

全域性鎖的命令：

#注意該指令鎖的粒度是當前MySQL服務中的所有資料庫例項，粒度超大，謹慎使用！！
FLUSH TABLES WITH read LOCK;
#解除指令  
unlock tables;

3.6 其他鎖之：死鎖

1.概念

兩個事務都持有對方需要的鎖，並且在等待對方釋放，並且雙方都不會釋放自己持有的鎖。

舉例1：

	事務1	事務2
1	begin; update account set money=100 where id =1;	begin;
2		update account set money=100 where id =2;
3	update account set money=200 where id =1;
4		update account set money=200 where id =1;

舉例2：

使用者A給使用者B轉賬100，在此同時，使用者B也給使用者A轉載100。這個過程，可能導致死鎖。

#事務1
update account set balance = balance - 100 where name = 'A';#操作1
update account set balance = balance + 100 where name = 'B';#操作3

#事務2
update account set balance = balance - 100 where name = 'B';#操作2
update account set balance = balance + 100 where name = 'A';#操作4

2.產生死鎖的必要條件

1.兩個或者兩個以上事務。

2.每個事務都已經持有鎖並且申請新的鎖。

3.鎖資源同時只能被同一個事務持有且不相容。

4.事務之間因為持有鎖和申請鎖導致彼此迴圈等待。

死鎖的關鍵在於：兩個(或以上)的Session加鎖的順序不一致。

3.如何處理死鎖

方式1：等待，直到超時( innodb_lock_wait_timeout=50s) 。

即當兩個事務互相等待時，當一個事務等待時間超過設定的閾值時，就將其回滾，另外事務繼續進行。這種方法簡單有效，在innodb中，引數innodb_lock_wait_timeout用來設定超時時間。

缺點:對於線上服務來說，這個等待時間往往是無法接受的。

那將此值修改短一些，比如1s，0.1s是否合適？不合適，容易誤傷到普通的鎖等待。

方式2：使用死鎖檢測進行死鎖處理

方式1檢測死鎖太過被動，innodb還提供了wait-for graph演算法來主動進行死鎖檢測，每當加鎖請求無法立即滿足需要並進入等待時，wait-for graph演算法都會被觸發。

這是一種較為主動的死鎖檢測機制，要求資料庫儲存鎖的資訊連結串列和事務等待連結串列兩部分資訊。

基於這兩個資訊，可以繪製wait-for graph （等待圖)

死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。

一旦檢測到迴路、有死鎖，這時候InnoDB儲存引擎會選擇回滾undo量最小的事務，讓其他事務繼續執行(innodb_deadlock_detect=on表示開啟這個邏輯)。

缺點:每個新的被阻塞的執行緒，都要判斷是不是由於自己的加入導致了死鎖，這個操作時間複雜度是o(n)。如果100個併發執行緒同時更新同一行，意味著要檢測100*100= 1萬次，1萬個執行緒就會有1千萬次檢測。

如何解決？

• 方式1：關閉死鎖檢測，但意味著可能會出現大量的超時，會導致業務有損。
• 方式2：控制併發訪問的數量。比如在中介軟體中實現對於相同行的更新，在進入引擎之前排隊，這樣在InnoDB內部就不會有大量的死鎖檢測工作。

進一步的思路：

可以考慮透過將一行改成邏輯上的多行來減少鎖衝突。比如，連鎖超市賬戶總額的記錄，可以考慮放到多條記錄上。賬戶總額等於這多個記錄的值的總和。

4.如何避免死鎖

• 合理設計索引，使業務SQL儘可能透過索引定位更少的行，減少鎖競爭。
• 調整業務邏輯SQL執行順序，避免 update/delete 長時間持有鎖的SQL在事務前面。
• 避免大事務，儘量將大事務拆成多個小事務來處理，小事務縮短鎖定資源的時間，發生鎖衝突的機率也更小。
• 在併發比較高的系統中，不要顯式加鎖,特別是是在事務裡顯式加鎖。如 select ... for update 語句，如果是在事務裡執行了start transaction或設定了autocommit等於0，那麼就會鎖定所查詢到的記錄。
• 降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從RR調整為RC可以避免掉很多因為gap鎖造成的死鎖。

4.鎖的記憶體結構

---

對一條記錄加鎖的本質就是在記憶體中建立一個鎖結構與之關聯，那麼是不是一個事務對多條記錄加鎖，就要建立多個鎖結構呢？比如：

#事務T1
SELECT * FROM user FOR SHARE;# MySQL8.0之後的寫法，之前LOCK IN SHARE MODE;

理論上建立多個鎖結構沒問題，但是如果一個事務要獲取10000條記錄的鎖，生成10000個鎖結構也太崩潰了!所以決定在對不同記錄加鎖時，如果符合下邊這些條件的記錄會放到一個鎖結構中。

• 在同一個事務中進行加鎖操作
• 被加鎖的記錄在同一個頁面中
• 加鎖的型別是一樣的
• 等待狀態是一樣的

InnoDB儲存引擎中的鎖結構如下:

結構解析：

1.鎖所在的事務資訊：

不論是表鎖還是行鎖，都是在事務執行過程中生成的，哪個事務生成了這個鎖結構，這裡就記錄這個事務的資訊。此鎖所在的事務資訊在記憶體結構中只是一個指標，透過指標可以找到記憶體中關於該事務的更多資訊，比方說事務id等。

2.索引資訊：

對於行鎖來說，需要記錄一下加鎖的記錄是屬於哪個索引的。這裡也是一個指標。

3.表鎖／行鎖資訊：

表鎖結構和行鎖結構在這個位置的內容是不同的：

• 表鎖：

記載著是對哪個表加的鎖，還有其他的一些資訊。

• 行鎖：

記載了三個重要的資訊：

• Space ID：記錄所在表空間。
• Page Number：記錄所在頁號。
• n_bits：對於行鎖來說，一條記錄就對應著一個位元位，一個頁面中包含很多記錄，用不同的位元位來區分到底是哪一條記錄加了鎖。為此在行鎖結構的末尾放置了一堆位元位，這個n_bits屬性代表使用了多少位元位。

n_bits的值一般都比頁面中記錄條數多一些。主要是為了之後在頁面中插入了新記錄後也不至於重新分配鎖結構

4.type_mode

這是一個32位的數，被分成了lock_mode、lock_type和rec_lock_type三個部分，如圖所示：

• 鎖的模式（lock_mode），佔用低4位，可選的值如下：

  • LOCK_IS（十進位制的0）：表示共享意向鎖，也就是IS鎖。

  • LOCK_IX（十進位制的1）：表示獨佔意向鎖，也就是IX鎖。

  • LOCK_S（十進位制的2）：表示共享鎖，也就是S鎖。

  • LOCK_X（十進位制的3）：表示獨佔鎖，也就是X鎖。

  • LOCK_AUTO_INC（十進位制的4）：表示AUTO-INC鎖。

在InnoDB儲存引擎中，LOCK_IS，LOCK_IX，LOCK_AUTO_INC都算是表級鎖的模式，LOCK_S和LOCK_X既可以算是表級鎖的模式，也可以是行級鎖的模式。

• 鎖的型別（lock_type），佔用第5～8位，不過現階段只有第5位和第6位被使用：

  • LOCK_TABLE十進位制的16），也就是當第5個位元位置為1時，表示表級鎖。

  • LOCK_REC （十進位制的32），也就是當第6個位元位置為1時，表示行級鎖。

• 行鎖的具體型別（rec_lock_type），使用其餘的位來表示。只有在lock_type的值為LOCK_REC時，也就是隻有在該鎖為行級鎖時，才會被細分為更多的型別：

  • LOCK_ORDINARY（十進位制的0）：表示next-key鎖。
  • LOCK_GAP（十進位制的512）：也就是當第10個位元位置為1時，表示gap鎖。
  • LOCK_REC_NOT_GAP（十進位制的1024）：也就是當第11個位元位置為1時，表示正經記錄鎖。
  • LOCK_INSERT_INTENTION（十進位制的2048）：也就是當第12個位元位置為1時，表示插入意向鎖。其他的型別：還有一些不常用的型別我們就不多說了。

• is_waiting屬性呢？基於記憶體空間的節省，所以把is_waiting屬性放到了type_mode這個32位的數字中：

  • LOCK_WAIT（十進位制的256） ：當第9個位元位置為1時，表示is_waiting為true，也就是當前事務尚未獲取到鎖，處在等待狀態；當這個位元位為0時，表示is_waiting為false，也就是當前事務獲取鎖成功。

5.其他資訊：

為了更好的管理系統執行過程中生成的各種鎖結構而設計了各種雜湊表和連結串列。

6.一堆位元位：

如果是行鎖結構的話，在該結構末尾還放置了一堆位元位，位元位的數量是由上邊提到的n_bits屬性表示的。InnoDB資料頁中的每條記錄在記錄頭資訊中都包含一個heap_no屬性，偽記錄Infimum的heap_no值為0，Supremum的heap_no值為1，之後每插入一條記錄，heap_no值就增1。鎖結構最後的一堆位元位就對應著一個頁面中的記錄，一個位元位對映一個heap_no，即一個位元位對映到頁內的一條記錄。

5.鎖監控

---

關於MySQL鎖的監控，我們一般可以透過檢查 InnoDB_row_lock 等狀態變數來分析系統上的行鎖的爭奪情況。

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

對各個狀態量的說明如下：

• Innodb_row_lock_current_waits：當前正在等待鎖定的數量；
• Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；（等待總時長）
• Innodb_row_lock_time_avg：每次等待所花平均時間；（等待平均時長）
• Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
• Innodb_row_lock_waits：系統啟動後到現在總共等待的次數；（等待總次數）

對於這5個狀態變數，比較重要的3個見上面（橙色）。

其他監控方法：

MySQL把事務和鎖的資訊記錄在了information_schema庫中，涉及到的三張表分別是INNODB_TRX、INNODB_LOCKS和 INNODB_LOCK_WAITS。

MySQL5.7及之前，可以透過information_schema.INNODB_LOCKS檢視事務的鎖情況，但只能看到阻塞事務的鎖；如果事務並未被阻塞，則在該表中看不到該事務的鎖情況。

MySQL8.0刪除了information_schema.INNODB_LOCKS，新增了performance_schema.data_locks，可以透過performance_schema.data_locks檢視事務的鎖情況，和MySQL5.7及之前不同，performance_schema.data_locks不但可以看到阻塞該事務的鎖，還可以看到該事務所持有的鎖。同時，information_schema.INNODB_LOCK_WAITS也被 performance_schema.data_lock_waits所代替。

我們模擬一個鎖等待的場景，以下是從這三張表收集的資訊

鎖等待場景，我們依然使用記錄鎖中的案例，當事務2進行等待時，查詢情況如下：

（1）查詢正在被鎖阻塞的sql語句。

SELECT * FROM information_schema.INNODB_TRX\G;

（2）查詢鎖等待情況

mysql> SELECT * FROM performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 140443984842992:55:4:7:140443887153424
REQUESTING_ENGINE_TRANSACTION_ID: 27179 #被阻塞事務id
            REQUESTING_THREAD_ID: 48
             REQUESTING_EVENT_ID: 100
REQUESTING_OBJECT_INSTANCE_BEGIN: 140443887153424
         BLOCKING_ENGINE_LOCK_ID: 140443984842136:55:4:7:140443887147264
  BLOCKING_ENGINE_TRANSACTION_ID: 27178 #正在執行事務id 阻塞了27179
              BLOCKING_THREAD_ID: 47
               BLOCKING_EVENT_ID: 34
  BLOCKING_OBJECT_INSTANCE_BEGIN: 140443887147264
1 row in set (0.00 sec)

（3）查詢鎖的情況

mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140443984842992:1114:140443887156528
ENGINE_TRANSACTION_ID: 27179
            THREAD_ID: 48
             EVENT_ID: 100
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140443887156528
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140443984842136:1114:140443887150368
ENGINE_TRANSACTION_ID: 27178
            THREAD_ID: 47
             EVENT_ID: 34
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140443887150368
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140443984842136:55:4:1:140443887147264
ENGINE_TRANSACTION_ID: 27178
            THREAD_ID: 47
             EVENT_ID: 34
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140443887147264
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140443984842136:55:4:3:140443887147264
ENGINE_TRANSACTION_ID: 27178
            THREAD_ID: 47
             EVENT_ID: 34
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140443887147264
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3

ERROR: 
No query specified

從鎖的情況可以看出來，兩個事務分別獲取了IX鎖，我們從意向鎖章節可以知道，IX鎖互相時相容的。所以這裡不會等待，但是事務1同樣持有X鎖，此時事務2也要去同一行記錄獲取X鎖，他們之間不相容，導致等待的情況發生。

6.附錄（略）

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。