本文更新于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事務回滾，會導致分布式事務不完整，

標籤：MySQL

上一篇：MySQL修改時區的方法小結

下一篇：MySQL 快速洗掉大量資料（千萬級別）的幾種實踐方案——附原始碼