本文更新於2019-09-22，使用MySQL 5.7，操作系統為Deepin 15.4。

目錄

• 鎖
  • 鎖概述
  • MyISAM表級鎖
  • InnoDB行級鎖
  • InnoDB表級鎖
  • 死鎖
• 事務
  • 事務概述
  • InnoDB事務
  • 分散式事務

鎖

鎖概述

MyISAM和MEMORY存儲引擎使用表級鎖。BDB存儲引擎進使用頁級鎖，但也支持表級鎖。InnoDB存儲引擎預設使用行級鎖，也支持表級鎖。

• 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，併發度最小。
• 頁級鎖：開銷、加鎖時間、鎖粒度、併發度介於表級鎖和行級鎖之間；會出現死鎖。
• 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度最高。

預設情況下，表級鎖和行級鎖都是自動獲取的。但在有些情況下，用戶需要明確進行鎖定。

MyISAM表級鎖

表級鎖有兩種模式：

• 表共用讀鎖：允許併發讀，但會阻塞併發寫。
• 表獨占寫鎖：阻塞併發讀和併發寫。

加鎖，如果表已被其他線程鎖定，則當前線程會等待直至獲得鎖：

LOCK TABLE|TABLES
tablename [AS alias] {READ [LOCAL]}|{[LOW_PRIORITY] WRITE}
[, ...]

加鎖時指定LOCAL，則允許在滿足MyISAM表併發插入條件（使用變數concurrent_insert控制）的情況下，其他用戶在表尾併發插入記錄。加鎖時，需一次鎖定所有用到的表，且同一個表在SQL語句中出現多少次，就要通過與SQL語句中相同的別名鎖定多少次（使用AS）。加鎖後，只能訪問加鎖的表，且不支持鎖升級（即如果是讀鎖，那麼只能執行讀操作，不能執行寫操作）。

MyISAM在執行讀操作（SELECT）前，會自動給涉及的所有表加讀鎖，在執行寫操作（UPDATE、DELETE、INSERT）前，會自動給涉及的所有表加寫鎖。

即使讀請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀鎖之前。可以使用max_write_lock_count給予讀請求獲得鎖的機會，或使用以下方法改變請求優先順序：

• 通過指定啟動參數low-priority-updates，預設給予寫請求比讀請求更低的優先順序。
• 通過執行SET low_priority_updates=1，給予該連接寫請求比讀請求更低的優先順序。
• 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY，降低該語句的優先順序。

解鎖，釋放當前線程獲得的所有鎖：

UNLOCK TABLES

如在鎖表期間，當前線程執行另一個LOCK TABLES或START TRANSACTION（對InnoDB存儲引擎），或與伺服器的連接被關閉時，會隱含地執行UNLOCK TABLES。

通過SHOW STATUS LIKE 'table_locks%'查看表級鎖使用情況。table_locks_waited比較高說明存在較嚴重的表級鎖爭用。

InnoDB行級鎖

可以通過SHOW STATUS LIKE 'innodb_row_lock%'，或查看information_schema中相關的表，或通過設置InnoDB Monitors查看行級鎖爭奪情況。

InnoDB實現了兩種類型的行級鎖：

• 共用鎖（S）：允許一個事務取讀一行，阻止其他事務獲得相同行的排他鎖。
• 排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同行的共用鎖和排他鎖。

另外，為了允許行級鎖和表級鎖共存，InoDB還有兩種內部使用的意向鎖，二者都是表鎖：

• 意向共用鎖（IS）：事務在給數據行加S鎖前，必須先取得該表的IS鎖。
• 意向排他鎖（IX）：事務在給數據行加X鎖前，必須先取的該表的IX鎖。

InoDB行級鎖模式相容性如下（縱向是當前鎖模式，橫向是請求鎖模式）：

對於UPDATE、DELETE、INSERT語句會自動給涉及數據集加排他鎖（X）。對普通SELECT語句不會加任何鎖，可通過select_statement LOCK IN SHARE MODE加共用鎖或select_statement FOR UPDATE加排他鎖,並需進行提交或回滾。

意向鎖是InnoDB自動加的。

InnoDB行級鎖是通過給索引上的索引項或間隙加鎖來實現的，共分三種：

• Record鎖：對索引項加鎖。
• Gap鎖：對索引項之間的間隙（包括第一條記錄前和最後一條記錄後）加鎖。
• Next-Key鎖：前兩種的組合，對索引項和間隙加鎖。當使用範圍條件而不是相等條件加鎖時，會對符合條件的已有記錄的索引項加鎖，對並不存在相應記錄但索引值在範圍內的間隙（GAP）也會加鎖。如果使用相等條件給一個不存在的記錄加鎖，也會使用Next-Key鎖。InnoDB使用Next-Key鎖的目的，一方面為了防止幻讀，另一方面為了滿足其恢復和複製的需要。

InnoDB行級鎖的特點，需註意如下問題：

• 如不通過索引條件查詢時，會鎖定表中的所有記錄，就如表級鎖。
• 雖然是訪問不同的行，但如果使用的是相同的索引項，是會出現鎖衝突的。這是因為行級鎖是對索引加鎖，而不是對記錄加鎖。
• 當表有多個索引時，不同的事務可以使用不同的索引鎖定不同的行。但如果是相同的行，則會等待。
• 即使在條件中使用了索引欄位，但是否使用索引是由MySQL通過判斷不同執行計劃的代價決定的。

InnoDB存儲引擎中不同SQL在不同隔離級別下的鎖比較（off/on指變數innodb_locks_unsafe_for_binlog的值）：

INSERT INTO ... SELECT ...和CREATE TABLE ... SELECT ...叫做不確定的SQL，屬於不安全的SQL，不推薦使用。如確實需要使用，又不希望因加鎖對源表併發更新產生影響，可使用以下方法：

• 將innodb_locks_unsafe_for_binlog設置為on，強制使用多版本資料庫（MVCC），但可能無法使用binlog正確恢復和複製數據。
• 使用SELECT ... INTO OUTFILE ...和LOAD DATA INFILE ...間接實現，這種方式不會對源表加鎖。
• 使用基於行數據的binlog格式和基於行數據的複製。

InnoDB表級鎖

以下兩種情況可以考慮使用表級鎖：

• 事務需要更新大部分或全部數據，表又比較大。
• 事務涉及多個表，很可能引起死鎖，造成大量事務回滾。

使用表級鎖需要註意：

• 雖然LOCK TABLES可以給InnoDB表加表級鎖，但表級鎖不是由InnoDB存儲引擎管理的，而是由其上一層——MySQL Server管理的。僅當autocommit=0、innodb_table_locks=1時，InnoDB才能知道MySQL Server加的表級鎖，MySQL Server也才能知道InnoDB加的行級鎖。這樣InnoDB才能自動識別涉及表級鎖的死鎖。
• 在用LOCK TABLES給InnoDB表加鎖時，需將autocommit設為0。事務結束前，不要用UNLOCK TABLES，因其會隱含地提交事務。COMMIT和ROLLBACK不能釋放表級鎖，必須使用UNLOCK TABLES。

死鎖

MyISAM總是一次獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖。InnoDB，除單個SQL組成的事務外，鎖是逐步獲得的，這就決定了InnoDB可能發生死鎖。

發生死鎖後，InnoDB一般都能自動檢測到，並使一個事務釋放鎖並回滾，另一個事務獲得鎖繼續完成事務。但在涉及外部鎖或涉及表級鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數innodb_lock_wait_timeout解決。

減少鎖衝突和死鎖的方法：

• 儘量使用較低的隔離級別。
• 精心設計索引，並儘量使用索引訪問數據，使加鎖更精確，從而減少鎖衝突的機會。
• 選擇合理的事務大小，小事務發生鎖衝突的幾率也小。
• 儘量用相等條件訪問數據，這樣可以避免Next-Key鎖對併發插入的影響。
• 不要申請超過實際需要的鎖級別，除非必需，查詢時不要顯式加鎖。
• 對於一些特定的事務，可以使用表鎖來提高處理速度或減少發生死鎖的幾率。
• 在應用中，如果不同的程式會併發存取多個表，應儘量約定以相同順序來訪問表，這樣可以大大降低鎖死發生的概率。
• 在程式以批量方式處理數據的時候，如果實現對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。
• 在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共用鎖，更新時再申請排他鎖，因為用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共用鎖，從而造成鎖衝突，甚至死鎖。
• 在可重覆讀隔離級別下，如果兩個線程同時對相同條件的記錄用SELECT ... FOR UPDATE加排他鎖，在沒有符合該條件記錄的情況下，兩個線程都會加鎖成功。程式發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這麼做，就會出現死鎖。這種情況將隔離級別改成已提交讀就可避免問題。
• 當隔離級別為已提交讀時，如果兩個線程都先執行SELECT ... FOR UPDATE判斷是否存在符合條件的記錄，如果沒有就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待。等第一個線程提交後，第二個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第三個線程來申請排他鎖，也會出現死鎖。對於這種情況，可以直接做插入操作，然後再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行ROLLBACK釋放獲得的排他鎖。

可以使用SHOW ENGINE INNODB STATUS查看最後一個死鎖產生的原因。

事務

事務概述

事務的ACID屬性：

• 原子性（Actomicity）：事務是一個原子操作單元，對數據的修改，要麼全都執行，要麼全都不執行。
• 一致性（Consistent）：在事務開始和完成時，數據必須保持一致狀態。即所有相關的數據規則都必須應用於事務中對數據的修改，所有內部數據結構（如索引）也必須是正確的。
• 隔離性（Isolation）：提供一定的隔離機制，保證事務不受外部併發操作的影響，事務處理過程的中間狀態對外部是不可見的。
• 持久性（Durable）：事務完成後，其對數據的修改是永久性的。

併發事務處理的問題：

• 更新丟失：當多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，最後的更新覆蓋了由其他事務所做的更新。
• 臟讀：一個事務正在對一條記錄做修改，在這個事務提交前，如果另一個事務也來讀取同一條記錄，就會讀取到臟數據（如果不加控制，讀取到第一個事務修改的數據，而後第一個事務回滾，第二個事務讀取到的數據就處於不一致狀態）。
• 不可重覆讀：一個事務在讀取某些數據後的某個時間，再次讀取以前讀取過的數據，卻發現其讀出的數據已經發生改變或被刪除。
• 幻讀：一個事務按照相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足查詢條件的新數據。

防止更新丟失是應用的責任，需要應用對要更新的數據加鎖來解決。臟讀、不可重覆讀、幻讀其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。事務隔離實質上是使事務在一定程度上串列化。資料庫實現事務隔離的方式基本上分兩種：

• 在讀數據前加鎖，阻止其他事務對數據進行修改。
• 數據多版本併發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也稱為多版本資料庫。不用加鎖，通過生成數據請求時間點的一致性數據快照來提供一定級別的一致性讀取。

有以下4個事務隔離級別：

可使用語句改變事務隔離級別：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED}|{READ COMITTED}|{REPEATABLE READ}|SERIALIZABLE

InnoDB事務

預設情況下，InnoDB是自動提交事務的，即每執行一條語句提交一次事務。可設置變數autocommit指定是否自動提交。

在同一個事務中，最好不要使用不同存儲引擎的表，否則ROLLBACK需要對非事務表進行特別的處理，因為COMMIT、ROLLBACK只能對事務表有效。通常情況下，只對提交的事務記錄到二進位日誌中，但如果一個事務中包含非事務表，那麼回滾的操作也會被記錄到二進位日誌中，以確保非事務表的更新也可以被覆制到從資料庫中。

所有的DDL語句都是不能回滾的，並且部分DDL語句會造成隱式的事務提交。

開始事務：

{START TRANSACTION}|{BEGIN [WORK]}

提交事務：

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

回滾事務，可以回滾到指定的savepointname。註意，可以回滾事務的一個部分，但不能提交事務的一個部分：

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] [TO SAVEPOINT savepointname]

CHAIN和RELEASE子句用於定義事務提交或回滾後的操作：CHAIN會立即啟動一個新事務，並且和原先的事務有相同的隔離級別；RELEASE會斷開客戶端和伺服器之間的連接。預設是NO CHAIN NO RELEASE。

定義SAVEPOINT。可以定義多個SAVEPOINT，如果定義了相同名字的SAVEPOINT，則後面定義的覆蓋前面定義的：

SAVEPOINT savepointname

刪除SAVEPOINT：

RELEASE SAVEPOINT savepointname

分散式事務

當前分散式事務只支持InnoDB存儲引擎。

一個分散式事務會涉及多個分支事務（XA事務），這些XA事務必須一起被提交，或一起被回滾。

使用分散式事務的應用程式涉及一個或多個資源管理器和一個事務管理器：

• 資源管理器（RM）：必須可以提交和回滾由TM管理的事務。當執行XA語句時，MySQL伺服器相當於資源管理器。
• 事務管理器（TM）：與RM進行通信，用於協調作為分散式事務一部分的各個XA事務。當執行XA語句時，與伺服器連接的客戶端相當於事務管理器。

執行分散式事務的過程使用兩階段提交：

1. 第一階段，所有分支事務被預備好，即它們被TM告知準備提交。
2. 第二階段，TM告知所有RM需要提交還是回滾。如果在第一階段，所有XA事務指示都能提交，則在第二階段所有XA事務都被告知需要提交；如在第一階段，任一XA事務指示不能提交，則在第二階段所有XA事務都被告知需要回滾。

啟動XA事務：

XA START|BEGIN xid [JOIN|RESUME]

每個XA事務必須有一個唯一的xid，該值不能被其他的XA事務使用。xid由客戶端提供，或由MySQL伺服器生成，包含3個部分：'gtrid'[,'bqual'[,formatID]]。

• gtrid是分散式事務標識符，相同的分散式事務應使用相同的gtrid。
• bqual是一個分支限定符，預設是空串。對一個分散式事務中的每個XA事務，bqual值必須是唯一的。
• formatID是一個數字，用於標識gtrid和bqual值使用的格式，預設是1。

使XA事務進入PREPARE狀態，也即兩階段提交的第一階段：

XA END xid [SUSPEND [FOR MIGRATE]];
XA PREPARE xid;

提交XA事務，進入兩階段提交的第二階段：

XA COMMIT xid [ONE PHASE]

回滾XA事務，進入兩階段提交的第二階段：

XA ROLLBACK xid

返回當前資料庫中處於PREPARE狀態的XA事務詳細信息：

XA RECOVER

MySQL的分散式事務還存在比較嚴重的缺陷：

1. 如果XA事務在到達PREPARE狀態時，資料庫異常重啟後，可以繼續對XA事務進行提交或回滾，但提交的事務沒有寫如binlog，可能導致使用binlog恢復時丟失部分數據。如果存在複製的資料庫，則有可能導致主從資料庫的數據不一致。
2. 如果某個XA事務的客戶端連接異常終止，資料庫會自動回滾未完成的XA事務。如果此時XA事務已經執行到PREPARE狀態，那麼這個分散式事務的其他XA事務可能已經提交。這個XA事務回滾，會導致分散式事務不完整。