本文目錄一覽：
一文詳解-MySQL 事務和鎖
當多個用戶訪問同一份數據時，一個用戶在更改數據的過程中，可能有其他用戶同時發起更改請求，為保證數據庫記錄的更新從一個一致性狀態變為另外一個一致性狀態，使用事務處理是非常必要的，事務具有以下四個特性：
MySQL 提供了多種事務型存儲引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務。為了支持事務，InnoDB 存儲引擎引入了與事務處理相關的 REDO 日誌和 UNDO 日誌，同時事務依賴於 MySQL 提供的鎖機制
事務執行時需要將執行的事務日誌寫入日誌文件，對應的文件為 REDO 日誌。當每條 SQL 進行數據更新操作時，首先將 REDO 日誌寫進日誌緩衝區。當客戶端執行 COMMIT 命令提交時，日誌緩衝區的內容將被刷新到磁盤，日誌緩衝區的刷新方式或者時間間隔可以通過參數 innodb_flush_log_at_trx_commit 控制
REDO 日誌對應磁盤上的 ib_logifleN 文件，該文件默認為 5MB，建議設置為 512MB，以便容納較大的事務。MySQL 崩潰恢復時會重新執行 REDO 日誌的記錄，恢復最新數據，保證已提交事務的持久性
與 REDO 日誌相反，UNDO 日誌主要用於事務異常時的數據回滾，具體內容就是記錄數據被修改前的信息到 UNDO 緩衝區，然後在合適的時間將內容刷新到磁盤
假如由於系統錯誤或者 rollback 操作而導致事務回滾，可以根據 undo 日誌回滾到沒修改前的狀態，保證未提交事務的原子性
與 REDO 日誌不同的是，磁盤上不存在單獨的 UNDO 日誌文件，所有的 UNDO 日誌均存在表空間對應的 .ibd 數據文件中，即使 MySQL 服務啟動了獨立表空間
在 MySQL 中，可以使用 BEGIN 開始事務，使用 COMMIT 結束事務，中間可以使用 ROLLBACK 回滾事務。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務
MySQL 定義了四種隔離級別，指定事務中哪些數據改變其他事務可見、哪些數據該表其他事務不可見。低級別的隔離級別可以支持更高的並發處理，同時佔用的系統資源更少
InnoDB 系統級事務隔離級別可以使用以下語句設置：
查看系統級事務隔離級別：
InnoDB 會話級事務隔離級別可以使用以下語句設置：
查看會話級事務隔離級別：
在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。讀取未提交的數據稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個事務，在 B 事務更新但未提交之前，A 事務讀取到了更新後的數據，但由於 B 事務回滾，導致 A 事務出現了臟讀現象
所有事務只能看見已經提交事務所做的改變，此級別可以解決臟讀，但也會導致不可重複讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務，A事務讀取了 B 事務的數據，在 B 事務更新並提交後，A 事務又讀取到了更新後的數據，此時就出現了同一 A 事務中的查詢出現了不同的查詢結果
MySQL 默認的事務隔離級別，能確保同一事務的多個實例在並發讀取數據時看到同樣的數據行，理論上會導致一個問題，幻讀（Phontom Read）。例如，第一個事務對一個表中的數據做了修改，這種修改會涉及表中的全部數據行，同時第二個事務也修改這個表中的數據，這次的修改是向表中插入一行新數據，此時就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行
InnoDB 通過多版本並發控制機制（MVCC）解決了該問題：InnoDB 通過為每個數據行增加兩個隱含值的方式來實現，這兩個隱含值記錄了行的創建時間、過期時間以及每一行存儲時間發生時的系統版本號，每個查詢根據事務的版本號來查詢結果
通過強制事務排序，使其不可能相互衝突，從而解決幻讀問題。簡而言之，就是在每個讀的數據行上加上共享鎖實現，這個級別會導致大量的超時現象和鎖競爭，一般不推薦使用
為了解決數據庫並發控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對並發操作進行控制，因此產生了鎖
共享鎖的粒度是行或者元組（多個行），一個事務獲取了共享鎖以後，可以對鎖定範圍內的數據執行讀操作
排他鎖的粒度與共享鎖相同，一個事務獲取排他鎖以後，可以對鎖定範圍內的數據執行寫操作
有兩個事務 A 和 B，如果事務 A 獲取了一個元組的共享鎖，事務 B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務 A 釋放共享鎖之後。如果事務 A 獲取了一個元組的排他鎖，事務 B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之後
意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務有意對數據上共享鎖或者排他鎖。有意表示事務想執行操作但還沒真正執行
鎖的粒度主要分為表鎖和行鎖
表鎖的開銷最小，同時允許的並發量也是最小。MyISAM 存儲引擎使用該鎖機制。當要寫入數據時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執行時使用的也是表鎖
行鎖可以支持最大的並發，InnoDB 存儲引擎使用該鎖機制。如果要支持並發讀/寫，建議採用 InnoDB 存儲引擎
MySQL鎖
對錶的增刪改查，都需要MDL鎖，無所不在
MDL讀鎖之間不互斥，但MDL讀寫鎖互斥
#舉個栗子
假設t是一張大表
session1對t執行一個查詢（SR）
session2對t執行一個DDL（SU，可能升級到X）
session3對t執行一個查詢（SR）
可知session1持有t表的MDL讀鎖（SR），session1的查詢還沒有結束的時候，去執行session2的DDL（SU），此時session2需要MDL寫鎖（SU升級到X，需要X鎖），由於MDL讀寫鎖互斥，因此session2需要等待session1釋放MDL讀鎖（SR阻塞X）；同時session2對後面的所有MDL讀鎖互斥（X阻塞SR），因此session2又繼續阻塞了session3…
#注釋：一開始的DDL能看到的狀態是SU，但如果SU的某個階段被阻塞，會被升級到X，從而引發SR阻塞X，達到實驗的效果。但實際測試中，DDL是分階段的，如果沒有滿足一定的要求，就不會引發阻塞，看到的結果就是SR和SU並沒有互相阻塞。這個過程需要具體的去查看源碼，此處不展開。
事務中的MDL鎖在語句開始時申請，但並不會在語句結束後就馬上釋放，而是會等到事務結束時才進行釋放
忙時對大表DDL會產生的災難性的結果就是：如果後續對該表有查詢操作，而且web端又有重試機制的話，那麼會有一個新的session再次發起讀請求，反覆如此，線程池就會在短時間內爆炸
在線執行DDL的時候，需要檢查一下information_schema.innodb_trx表中有沒有當前操作表對應的事務，此外還可以使用ALTER TABLE tbl_name NOWAIT…進行操作（MySQL8.0新特性）
eg.
session1
select * from cpf where payid’xxx’
union
select * from cpf where payid’xxx’
union (union重複50次，確保查詢時間幾十秒以上)
session2
alter table cpf modify payer_userid varchar(500);
session3
select * from cpf where payer_userid=’18051512003600300034′;
#執行結果
session1執行了31秒，當session1完成的時候session2和session3相繼完成
在session4中執行show processlist，結果如下
#變種1
如果session1在執行select之前，添加一句start transaction
會發現session1什麼時候執行完commit，sesssion2和session3什麼時候完成
也就是證實了在事務中的MDL鎖，在語句查詢完之後並不會釋放，而是會隨着事務的釋放而釋放
#變種2
session1和session3在執行select之前，添加一句start transaction，然後session1,2,3依次按順序執行
會發現session1阻塞了session2，而session3在執行完start transaction之後就被阻塞，根本沒有辦法去執行後面的select
當session1執行commit釋放之後，session2仍然處於阻塞狀態，session3亦是如此
直到session2或者session3當中任意一個執行了停止（navicat客戶端操作，類似於rollback）後，另一個才能完成執行
單純從變種2的結果來看，MDL鎖並沒有按照執行時間的先後來進行分配，當session1的鎖釋放之後，session3先獲得了讀鎖
MySQL是server-engine結構，MDL鎖是server層的鎖
通過show processlist可以發現waiting for table metadata lock，但這還遠遠不夠，需要在performance_schema庫中進行設置（MySQL8.0默認開啟）
5.7臨時開啟
UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;
5.7永久開啟（修改cnf配置）
[mysqld]
performance-schema-instrument = ‘wait/lock/metadata/sql/mdl=ON’
global：全局級（FTWRL）
schema：庫級（drop database）
table：表級（lock table read/write）
commit：提交級
關於global對象，主要作用是防止DDL和寫操作的過程中，執行set golbal_read_only = on或flush tables with read lock。
關於commit對象鎖，主要作用是執行flush tables with read lock後，防止已經開始在執行的寫事務提交。insert/update/delete在提交時都會上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）鎖
DML和DDL在執行之前都會申請IX鎖，DML會在global級別上加，而DDL會在global和schema這2個級別上都加IX（也就是2把鎖）
IX與大部分鎖都是兼容的，除了S，當然了X肯定是不兼容的；但IX與IX之間是兼容的，比如下圖
flush table with read lock會持有這個鎖（在global級別和commit級別）
FTWRL在全局級和事務級上分別加上了S鎖
IX與S是不兼容的
所以DML和DDL都會與FTWRL產生阻塞
邏輯備份第一句：flush table with read lock（S鎖）
大表DML（IX鎖）
先執行的阻塞後執行的，邏輯備份之前需要檢查是否有在線DDL（X鎖）以及DML（IX鎖），否則邏輯備份產生等待；盡量不要在忙時進行邏輯備份，否則阻礙忙時DML
如下圖，前面2行是FTWRL持有的S鎖，第3行是一個update語句，IX直接被阻塞，處於pending的鎖等待狀態；同時由於S鎖的持有時間為EXPLICIT，表明FTWRL需要一個顯示的釋放（unlock tables）
DML並不是只有IX鎖，DML和select .. for update在執行中持有的鎖實際是SW鎖（DML需要找一個大一點的表來驗證，目前只驗證了select .. for update），IX只是DML初期需要獲得的鎖
如下圖是一個select for update語句，start transaction對應的是第2行的SR鎖，而語句本身對應的是SW鎖
如果在此時執行一個FTWRL，我們會發現2個會話並不會相互阻塞（因為S鎖與SR和SW都是兼容的），如下圖
但如果我們是先執行的FTWRL再執行的select for update，那麼畫風就不是像上圖那樣了
如下圖所示，在先執行FTWRL的情況下，select for update壓根沒有獲得SW鎖，而是在獲取IX鎖的過程中就受挫了，一直處於pending狀態。（如果這個S鎖不釋放，那麼後面的IX會一直等待，直到超時）
S鎖除了邏輯備份時的FTWRL以外，createa table as也會持有這個鎖
目前已知的是desc操作會持有這個SH鎖
SH鎖與絕大部分鎖都兼容，除開X鎖
也就是說在做rename一類的操作的時候，你是無法去執行desc的
前面提到的start transaction，以及所有的非當前讀都需要持有這個鎖
非當前讀的意思就是快照讀，也就是普通的select
與SR鎖有衝突的有2個，一個是X，另一個是SNRW
研發有時候會很困惑的問我，「我這個表只有幾十行數據，select查不出來？？？」 這時候就需要檢查MDL鎖了
當前讀需要持有此鎖，常見的DML和select for update都對應此鎖，但不包括DDL
與SW鎖有衝突的有4個，SU,SRO,SNRW,X
看到一種說法是這個鎖僅對MyISAM引擎生效，衝突範圍與SW鎖類似
部分alter語句會持有該鎖。該鎖可能會升級成SNW,SNRW,X；而X鎖也有可能逐步降級到SU鎖
SU鎖和SU,SNW,SNRW,X鎖互斥
表面看起來DML的SW鎖和SU鎖不互斥（DML和DDL），但實際上因為SU鎖存在升級的屬性，SU鎖會升級到SNW鎖，從而和SW產生互斥
如下圖，SU並沒有被SW鎖阻塞，但升級到SNW之後，SNW被SW阻塞，一直處於pending狀態
SU鎖的兼容性如下
查看改過源碼的例子，在執行alter的時候，SU會升級到X，之後X降級到SU，然後SU再升級到X
先SU，再SW，SW被SU阻塞
先SW，再SU，SU並未被SW阻塞，但是SU向上升級的過程中產生的SNW被SW阻塞；於是將SW的會話commit，之後SNW向下降級成SU，並成功獲得鎖；
所以雖然看起來SW和SU不是一個雙向阻塞，但實際效果就是雙向阻塞，無論DML和DDL誰在前面，都必然會發生相互的阻塞
不兼容的有點多，先貼一個兼容性
SU升級X的過程中會升級成SNW
SU升級成X的過程中，有一個copy的過程，這個過程就是SNW，在這個copy的過程中，允許DML但是不允許select（SR）
copy是一個非常耗時的過程
lock tables read的語句會持有這個鎖
SRO阻塞SW，SNRW，X
兼容性如圖
lock tables write的語句會持有這個鎖
阻塞的鎖非常多，除開SH和S以外，其他的都阻塞，連SR都阻塞了
兼容性如下
換句話說flush tables with read lock; （S）會堵塞lock table write; （SNRW）
但是flush tables with read lock;（S）卻不會堵塞lock table read （SRO）
阻塞一切
各種DDL均屬於這個範疇
create,drop,rename （alter table add column也屬於這個範疇）
SW鎖阻塞X鎖，（X鎖是為了去執行一個drop）
X鎖阻塞SH
thread104在做一個create table as的表複製操作，在表裏面並沒有發現X鎖的信息，在thread95上對新表做一個desc操作，可以看到SH鎖處於等待狀態，然而這裡阻礙SH的並不是X鎖
只有1行的select被堵住
thread95做一個start transaction之後不提交，thread107對95的表做出一個rename操作，X鎖被前面的SR鎖阻塞，這時候thread108對該表發起一個limit僅僅為1的查詢，但被X鎖阻塞。由於lock_wait_timeout這個參數通常是1年，所以一連串查詢被堵死
alter開頭的幾個SQL，無論是modify還是add，查詢出來都是SU鎖，但DDL是一個過程，其中的有一部分如果發生了阻塞，可能會發現是X鎖阻塞；拿SR阻塞X鎖的實驗來說，SR阻塞X的過程非常短暫，如果沒有剛好卡到那個點，看到的結果可能就是SR和SU互不干涉，但如果卡到那個點，就會觀測到X被SR所阻塞。具體的需要讀源碼，這裡不展開
SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,”transaction_begin;” ,-1)ASblocking_query,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_IDASgranted_thread_id,
a.OBJECT_SCHEMAASlocked_schema,
a.OBJECT_NAMEASlocked_table,
“Metadata Lock”ASlocked_type,
c.PROCESSLIST_IDASwaiting_processlist_id,
c.PROCESSLIST_TIMEASwaiting_age,
c.PROCESSLIST_INFOASwaiting_query,
c.PROCESSLIST_STATEASwaiting_state,
d.PROCESSLIST_IDASblocking_processlist_id,
d.PROCESSLIST_TIMEASblocking_age,
d.PROCESSLIST_INFOASblocking_query,
concat(‘KILL’, d.PROCESSLIST_ID)ASsql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOINperformance_schema.metadata_locks bONa.OBJECT_SCHEMA=b.OBJECT_SCHEMA
ANDa.OBJECT_NAME=b.OBJECT_NAME
ANDa.lock_status=’PENDING’
ANDb.lock_status=’GRANTED’
ANDa.OWNER_THREAD_IDb.OWNER_THREAD_ID
ANDa.lock_type=’EXCLUSIVE’
JOINperformance_schema.threads cONa.OWNER_THREAD_ID=c.THREAD_ID
JOINperformance_schema.threads dONb.OWNER_THREAD_ID=d.THREAD_ID
) t1,
(
SELECT
thread_id,
group_concat(CASEWHENEVENT_NAME=’statement/sql/begin’THEN”transaction_begin”ELSEsql_textENDORDERBYevent_id SEPARATOR “;” )ASsql_text
FROM
performance_schema.events_statements_history
GROUPBYthread_id
) t2
WHERE
t1.granted_thread_id=t2.thread_id
MDL鎖處理
MDL元數據鎖
快速處理MDL鎖
mysql的鎖表機制不太理解,請詳細解釋
表級鎖、頁級鎖和行級鎖是不同粒度上的鎖，區別就是鎖定的範圍大小，顧名思義，表級鎖就是在整個表上請求一個鎖，頁級鎖就是在某個數據頁上請求一個鎖，行級鎖就是在某行上請求一個鎖。
更新操作一般優先級比讀取要高，前提是讀取操作在隊列中尚未執行，如果讀取已經開始從數據中獲取數據了，這時候更新請求的鎖資源已經被讀取操作鎖定了，那它只能等讀取操作結束以後才能執行。
深入理解MySQL的間隙鎖
因為行鎖只能鎖住行，但是新插入記錄這個動作，要更新的是記錄之間的「間隙」。為了解決幻讀問題，InnoDB 只好引入新的鎖，也就是間隙鎖 (Gap Lock)。
間隙鎖，鎖的就是兩個值之間的空隙，不允許兩個值之間再插一個值。
比如初始化插入了 6 個記錄，這就產生了 7 個間隙。分別是 (-∞,0)、(0,5)、(5,10)、(10,15)、(15,20)、(20, 25)、(25, +supremum)，間隙鎖都是開區間
和行鎖不一樣的是，跟間隙鎖存在衝突關係的，是「往這個間隙中插入一個記錄」這個操作。間隙鎖之間都不存在衝突關係。
缺點：可能會導致同樣的語句鎖住更大的範圍，影響了並發度。
間隙鎖和行鎖合稱 next-key lock，每個 next-key lock 是前開後閉區間。如果用 select * from t for update 要把整個表所有記錄鎖起來，就形成了 7 個 next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
和間隙鎖的最大區別是，next-key lock 為前開後閉區間，這樣所有的next-key lock就可以把所有記錄鎖起來。
加鎖規則裏面，包含了兩個「原則」、兩個「優化」和一個「bug」
MySQL從入門到精通(九) MySQL鎖，各種鎖
鎖是計算機協調多個進程或線程並發訪問某一資源的機制，在數據庫中，除傳統的計算資源（CPU、RAM、I/O）爭用外，數據也是一種供許多用戶共享的資源，如何保證數據並發訪問的一致性，有效性是所有數據庫必須解決的一個問題，鎖衝突也是影響數據庫並發訪問性能的一個重要因素，從這個角度來說，鎖對數據庫而言是尤其重要，也更加複雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數據庫中的所有表。2、表級鎖，每次操作鎖住整張表。3、行級鎖，每次操作鎖住對應的行數據。
全局鎖就是對整個數據庫實例加鎖，加鎖後整個實例就處於只讀狀態，後續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性。但是對數據庫加全局鎖是有弊端的，如在主庫上備份，那麼在備份期間都不能執行更新，業務會受影響，第二如果是在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進制日誌，會導致主從延遲。
解決辦法是在innodb引擎中，備份時加上–single-transaction參數來完成不加鎖的一致性數據備份。
添加全局鎖: flush tables with read lock; 解鎖 unlock tables。
表級鎖,每次操作會鎖住整張表.鎖定粒度大,發送鎖衝突的概率最高,並發讀最低,應用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數據鎖(meta data lock, MDL)和意向鎖。
表鎖又分為: 表共享讀鎖 read lock、表獨佔寫鎖write lock
語法: 1、加鎖 lock tables 表名 … read/write
2、釋放鎖 unlock tables 或者關閉客戶端連接
注意: 讀鎖不會阻塞其它客戶端的讀，但是會阻塞其它客戶端的寫，寫鎖既會阻塞其它客戶端的讀，又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。
元數據鎖,在加鎖過程中是系統自動控制的，無需顯示使用，在訪問一張表的時候會自動加上，MDL鎖主要作用是維護表元數據的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫入操作。為了避免DML和DDL衝突，保證讀寫的正確性。
在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當對錶結構進行變更操作時，加MDL寫鎖(排他).
查看元數據鎖:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;
意向鎖，為了避免DML在執行時，加的行鎖與表鎖的衝突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數據是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select … lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;
行級鎖，每次操作鎖住對應的行數據，鎖定粒度最小，發生鎖衝突的概率最高，並發讀最高，應用在innodb存儲引擎中。
innodb的數據是基於索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖，對於行級鎖，主要分為以下三類:
1、行鎖或者叫record lock記錄鎖，鎖定單個行記錄的鎖，防止其他事物對次行進行update和delete操作，在RC,RR隔離級別下都支持。
2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個間隙進行insert操作，產生幻讀，在RR隔離級別下都支持。
3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時鎖住數據，並鎖住數據前面的間隙Gap,在RR隔離級別下支持。
innodb實現了以下兩種類型的行鎖
1、共享鎖 S: 允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
2、排他鎖 X: 允許獲取排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共享鎖和排他鎖。
insert 語句 排他鎖 自動添加的
update語句 排他鎖 自動添加
delete 語句 排他鎖 自動添加
select 正常查詢語句 不加鎖 。。。
select 。。。lock in share mode 共享鎖 需要手動在select 之後加lock in share mode
select 。。。for update 排他鎖 需要手動在select之後添加for update
默認情況下，innodb在repeatable read事務隔離級別運行，innodb使用next-key鎖進行搜索和索引掃描，以防止幻讀。
間隙鎖唯一目的是防止其它事務插入間隙，間隙鎖可以共存，一個事務採用的間隙鎖不會阻止另一個事務在同一間隙上採用的間隙鎖。
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hk/n/304993.html
微信掃一掃 
支付寶掃一掃 