基礎概念
| 髒讀 | 一個事務中通路到了另外一個事務未送出的資料 |
| 不可重複讀 | 一個事務内根據同一個條件對行記錄進行多次查詢，傳回的結果不一緻 |
| 幻讀 | 同一個事務内多次查詢傳回的結果集不一樣（增加了或者減少） |
隔離級别
| 隔離級别 | 描述 | 鎖 |
| 讀未送出(read uncommit) | 一個事務還沒送出時，它做的變更就能被别的事務看到 | 任何操作都不會加鎖 |
| 讀送出(read commit) | 一個事務送出之後，它做的變更才會被其他事務看到 | 在RC級别中，資料的讀取都是不加鎖的，但是資料的寫入、修改和删除是需要加鎖的 |
| 可重複讀(repeatable read) | 一個事務執行過程中看到的資料，總是跟這個事務在啟動時看到的資料是一緻的 | 讀操作不需要加鎖，而寫操作需要加鎖。 |
| 串行化讀(serializable): | 當出現讀寫鎖沖突的時候，後通路的事務必須等前一個事務執行完成，才能繼續執行。 | “寫”會加“寫鎖”，“讀”會加“讀鎖” |
| 隔離級别 | 髒讀 | 不可重複讀 | 幻讀 |
| 讀未送出(read uncommit) | 可能 | 可能 | 可能 |
| 讀送出(read commit) | 不可能 | 可能 | 可能 |
| 可重複讀(repeatable read) | 不可能 | 不可能(MVCC實作) | 不可能（用next-key lock 保證） |
| 串行化讀(serializable) | 不可能 | 不可能 | 不可能 |
- mysql隔離級别有兩個作用域，一個是目前會話隔離級别，另一個是系統隔離級别。
- 讀取資料時，資料庫會建立視圖，通路的時候以視圖的邏輯結果為準
| 隔離級别 | 視圖 |
| 讀未送出(read uncommit) | 直接傳回記錄上的最新值，沒有視圖概念 |
| 讀送出(read commit) | 視圖是在每個 SQL 語句開始執行的時候建立的 |
| 可重複讀(repeatable read) | 視圖是在事務啟動時建立的，整個事務存在期間都用這個視圖 |
| 串行化讀(serializable) | 直接用加鎖的方式來避免并行通路。 |
快照讀（snapshot read）和目前讀（current read）
MySQL中的目前讀和快照讀是指在讀取資料時的不同方式。目前讀（Current Read）是指讀取最新的資料，而快照讀（Snapshot Read）則是指讀取某個特定時刻的資料快照。這兩種讀操作在不同的隔離級别下有不同的行為。
| 目前讀（Current Read） | 快照讀（Snapshot Read） | |
| 讀未送出(read uncommit) | 讀取到其他事務未送出的資料（髒讀） | 讀取到其他事務未送出的資料（髒讀） |
| 讀送出(read commit) | 目前讀操作會等待其他事務的鎖釋放，然後讀取已送出的最新版本的資料。 | 快照讀隻會讀取已經送出的資料。這意味着事務不會看到其他事務正在修改的資料。然而，同一事務内的多次讀操作可能會看到不同的資料版本，因為其他事務可能在此期間送出了修改。 |
| 可重複讀(repeatable read) | 目前讀仍然會等待其他事務的鎖釋放，然後讀取已送出的最新版本的資料。在目前讀（current read）的情況下，MySQL通過next-key lock來避免幻讀 | 在可重複讀隔離級别下，事務在開始時會建立一個快照，所有快照讀操作都會基于這個快照讀取資料。在快照讀（snapshot read）的情況下，MySQL通過MVCC（多版本并發控制）來避免幻讀 |
| 串行化讀(serializable) | 在串行化隔離級别下，所有事務都會串行執行，是以目前讀會讀取到已送出的最新版本的資料。 | 同目前讀 |
InnoDB 鎖機制
MySQL中的鎖有很多種,按照資源通路限制的不同程度,分為:
- 共享鎖（Shared Locks）：也稱為讀鎖（Read Locks），允許多個事務同時讀取相同的資料，但在共享鎖生效期間，其他事務不能對該資料進行修改。共享鎖的目的是保證在一個事務讀取資料時，其他事務不能修改這些資料。
- 排他鎖（Exclusive Locks）：也稱為寫鎖（Write Locks），當一個事務需要對資料進行修改時，會請求排他鎖。在排他鎖生效期間，其他事務既不能對該資料進行修改，也不能讀取這些資料。排他鎖的目的是保證在一個事務修改資料時，其他事務不能讀取或修改這些資料。
從鎖粒度的角度看,MySQL中有這麼幾種鎖：
- 表鎖（Table Locks）：表鎖是MySQL中最基本的鎖政策，适用于MyISAM、MEMORY等存儲引擎。當一個事務需要對表進行操作時，會鎖定整個表，防止其他事務在鎖定期間對表進行操作。表鎖的粒度較大，導緻鎖沖突的機率較高，是以在高并發場景下，表鎖的性能較低。
- 行鎖（Row Locks）：行鎖是MySQL中更進階的鎖政策，适用于InnoDB等存儲引擎。它允許對表中的單個行進行鎖定，減少鎖沖突的機率，提高并發性能。行鎖的粒度較小，但實作複雜，可能導緻死鎖（Deadlocks）等問題。
除了上述基本鎖類型外，MySQL還支援其他鎖政策，如間隙鎖（Gap Locks）、意向鎖（Intention Locks）等，以解決不同場景下的并發問題。
Record Locks (行鎖)
Record Locks,平時所說的行鎖,Record Locks是通過給索引上的索引項加鎖來實作的 這意味着：隻有通過索引條件檢索資料，InnoDB 才使用行級鎖，否則，InnoDB 将使用表鎖。
jsx複制代碼SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
上面這個語句會對t.c1 = 10記錄加排他鎖，其他事務對 t.c1 = 10 記錄的插入，更新和删除操作都會被阻塞。
在沒有定義索引的情況下,InnoDB 會建立一個隐藏的聚簇索引，并使用此索引進行記錄鎖定。
這是一段事務日志
jsx複制代碼
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
在事務日志中，行鎖一般用這種方式表示
jsx複制代碼lock_mode X locks rec but not gap
X 表示 Exclusive ，于此相對的還有S 表示Shared
Gap Lock （間隙鎖）
間隙鎖是對索引記錄之間的間隙的鎖定，或者是對第一個或最後一個索引記錄之前或之後的間隙的鎖定。例如，執行 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 可阻止其他事務向 t.c1 列插入值 15，無論該列中是否已存在此類值，因為範圍内所有現有值之間的間隙都被鎖定。
間隙可能包括一個索引值、多個索引值，甚至為空。
對于使用唯一索引搜尋唯一行的SQL，不需要間隙鎖定。例如，如果 id 列具有唯一索引，以下語句僅對具有 id 值 100 的行使用Record Locks，而不管其他會話是否在前面的間隙中插入行：
sql複制代碼SELECT * FROM child WHERE id = 100;
如果 id 沒有索引或具有非唯一索引，該語句确實會鎖定前面的間隙。
如果搜尋條件僅包含聯合唯一索引的某些列,還是會加上間隙鎖.
不同僚務可以在間隙上持有沖突的鎖。例如，事務 A 可以在間隙上持有共享間隙鎖（間隙 S-鎖），而事務 B 在同一間隙上持有排他間隙鎖（間隙 X-鎖）。跟間隙鎖存在沖突關系的，是“往這個間隙中插入一個記錄”這個操作。
一般情況下，間隙鎖隻作用在可重複讀隔離級别（RR）下. RC隔離級别下，在進行外鍵限制檢測和唯一鍵限制檢測的時候，會使用到Gap鎖。
Insert Intention Locks(插入意向鎖)
Insert Intention Locks 是一種插入意向鎖,它是在真正插入一行記錄之前由insert操作設定的.
隻要兩個事務插入的不是同一行，就不會互相阻塞。
插入意向鎖在事務日志中的表現：
jsx複制代碼lock_mode X locks gap before rec insert intention waiting
完整日志
jsx複制代碼RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...
RC級别下使用Gap Lock的原因
RC級别下,進行唯一鍵限制檢測的時候需要使用到間隙鎖，是為了解決主從複制的一些bug,比如
當我們并發的用INSERT …ON DUPLICATE KEY UPDATE的時候，如果我們有多個唯一索引，那麼有可能會導緻binlog錯誤，也就是會導緻主從複制不一緻,具體可以參考 :bugs.mysql.com/bug.php?id=…
Gap Lock練習
現在一個表有三個字段 id a b 其中b有索引 現在裡面插入(5，5，5)，(10，10，10)，(15，15，15)
java複制代碼用戶端a:
begin;
select * from tb where b=10 for update;
java複制代碼用戶端b
begin;
select * from tb where b=9 for update;
- 問這個b會卡住 還是查出來空：查出來為空
- insert (9,9,9) 會發生什麼: 會卡死
Next-key lock
Next-key 鎖是一種結合了Record Locks (行鎖) 和Gap Locks (間隙鎖)的鎖定機制,它鎖定一個索引記錄以及記錄之前的間隙.
假設我們有一個按 c1 列排序的索引，索引中的記錄值為
sql複制代碼5, 10, 20, 30, 40
對于查詢 SELECT * FROM t WHERE c1 BETWEEN 10 AND 30 FOR UPDATE;InnoDB 會鎖定值為 10、20 和 30 的記錄，并鎖定它們之間的間隙。鎖定範圍如下：
sql複制代碼(5, 10], [10, 20], [20, 30]
假如索引記錄為
sql複制代碼10, 20, 30, 40
鎖定範圍如下：
sql複制代碼(-∞, 10], [10, 20], [20, 30]
next-key lock的事務資料在SHOW ENGINE INNODB STATUS和INNODB螢幕輸出中顯示如下：
java複制代碼RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
這裡的lock_mode X可以認為就是Next-Key lock
Next-key lock引發的死鎖case
SQL : insert on duplicate key update
死鎖現場
java複制代碼LATEST DETECTED DEADLOCK
------------------------
2021-08-13 16:00:22 0x7fe08f17d700
*** (1) TRANSACTION:
TRANSACTION 10992144786, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 48550292, OS thread handle 140557710284544, query id 51167478136 10.78.184.193 dev_1597323979 update
/*id:8307ed81*/insert into store_visit_info
( mall_id,
has_wechat_group,
wechat_group_number,
store_id )
values ( 614802394,
1,
1,
1952182965122 )
ON DUPLICATE KEY UPDATE mall_id = 614802394,
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 771 page no 6523 n bits 776 index uk_mall_id of table `sanmateo`.`store_visit_info` trx id 10992144786 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 596 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 0000000024a523e3; asc $ # ;;
1: len 8; hex 00000000001548d6; asc H ;;
*** (2) TRANSACTION:
TRANSACTION 10992144785, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4998
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 48549906, OS thread handle 140602450106112, query id 51167478135 10.78.184.193 dev_1597323979 update
/*id:8307ed81*/insert into store_visit_info
( mall_id,
has_wechat_group,
wechat_group_number,
store_id )
values ( 614802394,
1,
1,
1952182965122 )
ON DUPLICATE KEY UPDATE mall_id = 614802394,
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 771 page no 6523 n bits 776 index uk_mall_id of table `sanmateo`.`store_visit_info` trx id 10992144785 lock_mode X locks gap before rec
Record lock, heap no 596 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 0000000024a523e3; asc $ # ;;
1: len 8; hex 00000000001548d6; asc H ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 771 page no 6523 n bits 776 index uk_mall_id of table `sanmateo`.`store_visit_info` trx id 10992144785 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 596 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 0000000024a523e3; asc $ # ;;
1: len 8; hex 00000000001548d6; asc H ;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
死鎖原因分析
- insert on duplicate key update ，當檢測到唯一鍵沖突的時候，會在出現沖突的唯一索引處加上next-key lock。
- 根據上面的日志，我們可以知道，兩個事務成功擷取到同一段間隙的next-key lock，嘗試寫入資料，在寫入資料前需要插入意向鎖，而意向鎖的插入需要等待排他鎖的釋放，事務10992144786在等待事務10992144785釋放排他鎖，而事務10992144785在等待事務10992144786釋放排他鎖，形成死鎖。
解決方案 在有競争條件下，我們很難避免死鎖的形成，我們的資料庫應對死鎖的政策是主動復原其中一個事務，是以我們重點關注事物復原帶來的資料丢失問題。
Metadata Locking 與 DDL
中繼資料鎖定（Metadata Locking）是一種同步機制，用于確定多個并發會話在通路和修改資料庫對象（如表、視圖以及存儲過程等）時能夠保持一緻性和完整性。中繼資料鎖定通過為這些對象添加特定類型的鎖來實作，例如共享鎖（Shared Locks）和排他鎖（Exclusive Locks）。
當對一個表做增删改查操作的時候，加 MDL 讀鎖；當要對表做結構變更操作的時候，加 MDL 寫鎖。
以下是DDL操作過程中中繼資料鎖定的一些典型行為：
- 修改表結構（ALTER TABLE）：在執行ALTER TABLE操作時，MySQL會為要修改的表設定一個排他鎖。在表結構修改期間，其他會話無法通路或修改該表。這確定了表結構更改過程中資料的完整性。一旦表結構修改完成，排他鎖會被釋放，其他會話可以繼續通路表。
- 建立/修改/删除索引（CREATE INDEX、ALTER INDEX、DROP INDEX）：在執行與索引相關的DDL操作時，MySQL會為涉及的表設定一個排他鎖。在索引更改期間，其他會話無法通路或修改該表。這確定了在建立、修改或删除索引時資料的一緻性。操作完成後，排他鎖會被釋放，其他會話可以繼續通路表。
Online DDL
MySQL 5.6中引入Online DDL功能, 它允許在表結構修改過程中，表仍然可以接受讀取和寫入操作。這種方法可以減少由于DDL操作導緻的表鎖定時間，提高資料庫的可用性和并發性能。
傳統的DDL操作，如ALTER TABLE，通常需要在整個操作過程中對表加鎖，進而阻止其他會話執行讀取或寫入操作。線上DDL通過允許表在結構更改過程中繼續接受讀取和寫入操作，可以顯著減少鎖定時間。然而，并非所有的ALTER TABLE操作都可以作為線上DDL執行。針對InnoDB表的支援取決于操作類型、索引類型等因素。
Online DDL的行文和存儲引擎,DDL類型,DML操作類型有關
線上DDL的一些關鍵特性：
- 隐式鎖定：在執行線上DDL操作時，MySQL會隐式地為涉及的表添加中繼資料鎖。這些中繼資料鎖會在DDL操作持續期間保持，以確定其他會話不會執行與目前DDL操作沖突的操作，如另一個ALTER TABLE操作。中繼資料鎖通常比傳統DDL操作中的表鎖定更加輕量級，對資料庫性能的影響較小。
- 鎖定粒度：線上DDL試圖以更細的鎖定粒度來降低鎖定沖突。例如，對于某些操作，它可能隻鎖定表的某個部分，而不是整個表。這允許其他會話在DDL操作執行期間通路和修改表的未鎖定部分。然而，并非所有線上DDL操作都支援細粒度鎖定，某些情況下仍可能需要對整個表加鎖。
- 并發讀取和寫入：線上DDL允許在表結構修改過程中執行SELECT和INSERT、UPDATE、DELETE等DML操作。這是通過在操作過程中使用共享鎖和排他鎖來實作的。例如，在添加或删除索引時，MySQL可能會使用共享鎖來允許其他會話繼續讀取資料，但在此過程中阻止寫操作。這有助于避免長時間的表鎖定。
- 操作階段：線上DDL操作可以分為多個階段，每個階段可能涉及不同類型的鎖定。例如，某些操作可能在開始階段需要短暫的排他鎖，然後在操作的其餘部分使用共享鎖。這可以進一步減少鎖定時間，提高資料庫的可用性。
- 操作中斷：線上DDL支援在操作過程中暫停和恢複，以便在需要時中斷長時間運作的DDL操作。
Online DDL操作階段
- 準備階段：在此階段，MySQL為執行DDL操作做好準備。這可能包括對表結構和限制進行一些初始檢查，以確定操作可以安全地進行。此階段通常不需要對表進行鎖定。
- 建立新結構階段：在此階段，MySQL建立一個新的表結構，以便在操作過程中維護資料的一緻性。新結構通常是舊結構的副本，但包含更改後的列、索引等。在某些情況下，這可能需要在舊表上設定共享鎖，以允許其他會話繼續讀取資料，但阻止寫操作。此階段的鎖定時間可能較短。
- 資料複制階段：在此階段，MySQL将資料從舊表複制到新結構。根據操作的類型和表的大小，這可能需要一定的時間。在資料複制期間，MySQL可能會為舊表和新結構設定共享鎖和排他鎖，以確定資料一緻性。然而，這些鎖定通常具有較細的粒度，進而允許其他會話在操作過程中繼續通路和修改未鎖定的部分。
- 切換結構階段：在此階段，MySQL将新結構替換為舊表。這可能需要在表上設定短暫的排他鎖，以確定其他會話在切換過程中不會通路或修改資料。一旦新結構成功替換舊表，鎖定會被釋放，其他會話可以繼續通路和修改新表。
- 清理階段：在此階段，MySQL完成操作的最終步驟，例如删除舊表、更新中繼資料和統計資訊等。此階段通常不需要對表進行鎖定。
死鎖的定義與解決政策
死鎖：兩個或兩個以上的程序或事務互相等待
政策：
- 一種政策是，直接進入等待，直到逾時。這個逾時時間可以通過參數 innodb_lock_wait_timeout 來設定。
- 另一種政策是，發起死鎖檢測，發現死鎖後，主動復原死鎖鍊條中的某一個事務，讓其他事務得以繼續執行。将參數 innodb_deadlock_detect 設定為 on，表示開啟這個邏輯。
MVCC 多版本并發控制
多版本并發控制（MVCC，Multi-Version Concurrency Control）是一種用于資料庫管理系統的并發控制技術。它允許多個事務同時通路和修改資料，而無需等待鎖定。MVCC通過為每個事務生成資料的"快照"來實作這一目标，進而使事務能夠獨立于其他事務工作，而不會互相幹擾。
MVCC的核心思想是在事務開始時，為每個讀取的資料行建立一個版本。事務會看到這個版本，而不是實際的資料行。這樣，事務可以在不影響其他事務的情況下進行讀取和修改操作。
在InnoDB中，會在每行資料後添加兩個額外的隐藏的值來實作MVCC，這兩個值一個記錄這行資料何時被建立，另外一個記錄這行資料何時過期（或者被删除）。 在實際操作中，存儲的并不是時間，而是事務的版本号，每開啟一個新事務，事務的版本号就會遞增。 在可重讀Repeatable reads事務隔離級别下：
- SELECT時，讀取建立版本号<=目前事務版本号，删除版本号為空或>目前事務版本号。
- INSERT時，儲存目前事務版本号為行的建立版本号
- DELETE時，儲存目前事務版本号為行的删除版本号
- UPDATE時，插入一條新紀錄，儲存目前事務版本号為行建立版本号，同時儲存目前事務版本号到原來删除的行
MVCC在InnoDB中的實作方式
Read View 主要來幫我們解決可見性的問題的, 即他會來告訴我們本次事務應該看到哪個快照，不應該看到哪個快照。
在 Read View 中有幾個重要的屬性：
- trx_ids，系統目前未送出的事務 ID 的清單。
- low_limit_id，未送出的事務中最大的事務 ID。
- up_limit_id，未送出的事務中最小的事務 ID。
- creator_trx_id，建立這個 Read View 的事務 ID。
Innodb存儲引擎中，每行資料都包含了一些隐藏字段：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR和DELETE_BIT。
DB_TRX_ID：用來辨別最近一次對本行記錄做修改的事務的辨別符，即最後一次修改本行記錄的事務id。delete操作在内部來看是一次update操作，更新行中的删除辨別位DELELE_BIT。DB_ROLL_PTR：指向目前資料的undo log記錄，復原資料通過這個指針來尋找記錄被更新之前的内容資訊。
一行資料會對應多行這樣的記錄，例如，如果有多個事物對同一行資料進行更新，會形成這樣的記
事物啟動的時候，mysql會為這個事物建立一個數組A，數組的元素為該事務啟動瞬間，系統中啟動了但還沒送出的所有事務 ，數組中事務id的最小值記為low_limit_id，目前系統裡面已經建立過的事務 ID 的最大值加 1 記為up_limit_id
讀取某一行資料，如果該資料最新的事務ID小于low_limit_id 那麼該版本是可見的
如果事務ID大于up_limit_id 該版本不可見 根據復原指針找到上一個版本記錄
如果事務ID落在low_limit_id和up_limit_id 之間 如果A中包含該事務ID，該版本不可見，未包含該事務ID 該版本可見
原文連結：https://juejin.cn/post/7251501945272844348