本文更新于2019-09-22，使用MySQL 5.7，操作系統(tǒng)為Deepin 15.4。

鎖

鎖概述

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

• 表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最小。
• 頁級鎖：開銷、加鎖時間、鎖粒度、并發(fā)度介于表級鎖和行級鎖之間；會出現(xiàn)死鎖。
• 行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度最高。

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

MyISAM表級鎖

表級鎖有兩種模式：

• 表共享讀鎖：允許并發(fā)讀，但會阻塞并發(fā)寫。
• 表獨占寫鎖：阻塞并發(fā)讀和并發(fā)寫。

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

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

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

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

即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖之前?？梢允褂?code>max_write_lock_count給予讀請求獲得鎖的機會，或使用以下方法改變請求優(yōu)先級：

• 通過指定啟動參數(shù)low-priority-updates，默認給予寫請求比讀請求更低的優(yōu)先級。
• 通過執(zhí)行SET low_priority_updates=1，給予該連接寫請求比讀請求更低的優(yōu)先級。
• 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY，降低該語句的優(yōu)先級。

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

UNLOCK TABLES

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

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

InnoDB行級鎖

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

InnoDB實現(xiàn)了兩種類型的行級鎖：

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

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

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

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

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

意向鎖是InnoDB自動加的。

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

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

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

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

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

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

• 將innodb_locks_unsafe_for_binlog設(shè)置為on，強制使用多版本數(shù)據(jù)庫（MVCC），但可能無法使用binlog正確恢復(fù)和復(fù)制數(shù)據(jù)。
• 使用SELECT ... INTO OUTFILE ...和LOAD DATA INFILE ...間接實現(xiàn)，這種方式不會對源表加鎖。
• 使用基于行數(shù)據(jù)的binlog格式和基于行數(shù)據(jù)的復(fù)制。

InnoDB表級鎖

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

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

使用表級鎖需要注意：

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

死鎖

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

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

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

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

可以使用SHOW ENGINE INNODB STATUS查看最后一個死鎖產(chǎn)生的原因。

事務(wù)

事務(wù)概述

事務(wù)的ACID屬性：

• 原子性（Actomicity）：事務(wù)是一個原子操作單元，對數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。
• 一致性（Consistent）：在事務(wù)開始和完成時，數(shù)據(jù)必須保持一致狀態(tài)。即所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)中對數(shù)據(jù)的修改，所有內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如索引）也必須是正確的。
• 隔離性（Isolation）：提供一定的隔離機制，保證事務(wù)不受外部并發(fā)操作的影響，事務(wù)處理過程的中間狀態(tài)對外部是不可見的。
• 持久性（Durable）：事務(wù)完成后，其對數(shù)據(jù)的修改是永久性的。

并發(fā)事務(wù)處理的問題：

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

防止更新丟失是應(yīng)用的責(zé)任，需要應(yīng)用對要更新的數(shù)據(jù)加鎖來解決。臟讀、不可重復(fù)讀、幻讀其實都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決。事務(wù)隔離實質(zhì)上是使事務(wù)在一定程度上串行化。數(shù)據(jù)庫實現(xiàn)事務(wù)隔離的方式基本上分兩種：

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

有以下4個事務(wù)隔離級別：

可使用語句改變事務(wù)隔離級別：

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

InnoDB事務(wù)

默認情況下，InnoDB是自動提交事務(wù)的，即每執(zhí)行一條語句提交一次事務(wù)?？稍O(shè)置變量autocommit指定是否自動提交。

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

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

開始事務(wù)：

{START TRANSACTION}|{BEGIN [WORK]}

提交事務(wù)：

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

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

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

CHAIN和RELEASE子句用于定義事務(wù)提交或回滾后的操作：CHAIN會立即啟動一個新事務(wù)，并且和原先的事務(wù)有相同的隔離級別；RELEASE會斷開客戶端和服務(wù)器之間的連接。默認是NO CHAIN NO RELEASE。

定義SAVEPOINT?？梢远x多個SAVEPOINT，如果定義了相同名字的SAVEPOINT，則后面定義的覆蓋前面定義的：

SAVEPOINT savepointname

刪除SAVEPOINT：

RELEASE SAVEPOINT savepointname

分布式事務(wù)

當前分布式事務(wù)只支持InnoDB存儲引擎。

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

使用分布式事務(wù)的應(yīng)用程序涉及一個或多個資源管理器和一個事務(wù)管理器：

• 資源管理器（RM）：必須可以提交和回滾由TM管理的事務(wù)。當執(zhí)行XA語句時，MySQL服務(wù)器相當于資源管理器。
• 事務(wù)管理器（TM）：與RM進行通信，用于協(xié)調(diào)作為分布式事務(wù)一部分的各個XA事務(wù)。當執(zhí)行XA語句時，與服務(wù)器連接的客戶端相當于事務(wù)管理器。

執(zhí)行分布式事務(wù)的過程使用兩階段提交：

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

啟動XA事務(wù)：

XA START|BEGIN xid [JOIN|RESUME]

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

• gtrid是分布式事務(wù)標識符，相同的分布式事務(wù)應(yīng)使用相同的gtrid。
• bqual是一個分支限定符，默認是空串。對一個分布式事務(wù)中的每個XA事務(wù)，bqual值必須是唯一的。
• formatID是一個數(shù)字，用于標識gtrid和bqual值使用的格式，默認是1。

使XA事務(wù)進入PREPARE狀態(tài)，也即兩階段提交的第一階段：

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

提交XA事務(wù)，進入兩階段提交的第二階段：

XA COMMIT xid [ONE PHASE]

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

XA ROLLBACK xid

返回當前數(shù)據(jù)庫中處于PREPARE狀態(tài)的XA事務(wù)詳細信息：

XA RECOVER

MySQL的分布式事務(wù)還存在比較嚴重的缺陷：

1. 如果XA事務(wù)在到達PREPARE狀態(tài)時，數(shù)據(jù)庫異常重啟后，可以繼續(xù)對XA事務(wù)進行提交或回滾，但提交的事務(wù)沒有寫如binlog，可能導(dǎo)致使用binlog恢復(fù)時丟失部分數(shù)據(jù)。如果存在復(fù)制的數(shù)據(jù)庫，則有可能導(dǎo)致主從數(shù)據(jù)庫的數(shù)據(jù)不一致。
2. 如果某個XA事務(wù)的客戶端連接異常終止，數(shù)據(jù)庫會自動回滾未完成的XA事務(wù)。如果此時XA事務(wù)已經(jīng)執(zhí)行到PREPARE狀態(tài)，那么這個分布式事務(wù)的其他XA事務(wù)可能已經(jīng)提交。這個XA事務(wù)回滾，會導(dǎo)致分布式事務(wù)不完整。