Optimizing Locking Operations

8.11.1 Internal Locking Methods
8.11.2 Table Locking Issues
8.11.3 Concurrent Inserts
8.11.4 Metadata Locking
8.11.5 External Locking

MySQL 使用 locking來管理表內(nèi)容的爭(zhēng)用：

Internal locking 發(fā)生在MySQL服務(wù)器內(nèi)部解寝，來管理多線程導(dǎo)致的表內(nèi)容爭(zhēng)用蒲凶。這種鎖是內(nèi)部的搓谆，因?yàn)樗耆欠?wù)器產(chǎn)生的，而不是其他程序顽爹。參見 Section 8.11.1, “Internal Locking Methods”。
External locking 發(fā)生在服務(wù)器和其他程序鎖住 MyISAM 表文件來協(xié)商該時(shí)間點(diǎn)哪個(gè)程序可以訪問改表的時(shí)候骆姐。參見 Section 8.11.5, “External Locking”镜粤。

一、內(nèi)部鎖 Internal Locking Methods

行級(jí)鎖 Row-Level Locking

MySQL 使用row-level locking 給 InnoDB 表來支持不同session的同時(shí)寫玻褪，使得適用于多用戶肉渴、高并發(fā)、以及OLTP應(yīng)用带射。

要在并發(fā)寫某個(gè)InnoDB 表的時(shí)候避免 deadlocks 同规，通過在事務(wù)開頭對(duì)要修改的行提交 SELECT ... FOR UPDATE來獲取必要的鎖，即使在稍后事務(wù)會(huì)修改這些數(shù)據(jù)窟社。如果事務(wù)修改或鎖定多個(gè)表券勺，那么應(yīng)用的語句在每個(gè)事務(wù)內(nèi)都要以同樣的順序執(zhí)行。死鎖會(huì)影響性能桥爽，而不是一個(gè)嚴(yán)重的錯(cuò)誤朱灿，因?yàn)?code>InnoDB自動(dòng)的 detects 死鎖狀態(tài)，然后回滾其中一個(gè)受影響的事務(wù)钠四。

行級(jí)鎖的優(yōu)勢(shì)：

不同session訪問不同行的時(shí)候盗扒，產(chǎn)生更少鎖的沖突跪楞。
回滾更少的變更。
可以長時(shí)間鎖某單行侣灶。

表級(jí)鎖 Table-Level Locking

MySQL使用table-level locking 給 MyISAM甸祭、 MEMORY、和 MERGE 表褥影，一次只允許一個(gè)連接更新表池户。這種鎖使得存儲(chǔ)引擎更適合于只讀、讀多寫少凡怎、或者單用戶應(yīng)用校焦。

這些存儲(chǔ)引擎通過在開始查詢時(shí)即獲取所有需要的鎖，然后以同樣的順序來鎖住這些表來規(guī)避deadlocks 统倒。相對(duì)妥協(xié)的是該策略降低了并發(fā)性能寨典；其他會(huì)話修改數(shù)據(jù)必須等到當(dāng)前修改數(shù)據(jù)的語句結(jié)束。

表級(jí)鎖的優(yōu)勢(shì)：

相對(duì)小的內(nèi)存需求 (行鎖需要鎖定的每行或者行組都分配內(nèi)存)
在使用表的大范圍內(nèi)容的時(shí)候快房匆，因?yàn)閮H一個(gè)鎖
在經(jīng)常在大范圍數(shù)據(jù)執(zhí)行 GROUP BY 操作耸成，或者必須頻繁執(zhí)行全表掃描的時(shí)候快

MySQL通過如下授權(quán)表寫鎖：

如果表上沒鎖，那么推一個(gè)寫鎖給表
或者浴鸿，推鎖的請(qǐng)求進(jìn)寫鎖隊(duì)列

MySQL通過如下授權(quán)讀鎖：

如果表上沒鎖井氢，那么推一個(gè)讀鎖給表
或者，推鎖的請(qǐng)求進(jìn)讀鎖隊(duì)列

表更新的優(yōu)先級(jí)高于表的檢索岳链。因此花竞，當(dāng)一個(gè)鎖釋放了，先對(duì)寫鎖隊(duì)列的請(qǐng)求可用宠页，然后對(duì)讀鎖隊(duì)列的請(qǐng)求可用左胞。這就保證了即使該表有一個(gè)很大的SELECT，也不會(huì)導(dǎo)致更新等待举户。那么烤宙，如果該表有很多更新，SELECT 語句會(huì)等待直到?jīng)]有更新為止俭嘁。

關(guān)于更改讀寫優(yōu)先級(jí)躺枕，參見Section 8.11.2, “Table Locking Issues”。

表鎖

InnoDB 表使用行級(jí)鎖供填，這樣多連接和應(yīng)用可以同時(shí)讀取和寫入到同一個(gè)表拐云，而不需要等待或產(chǎn)生不一致的結(jié)果。對(duì)于InnoDB引擎近她，避免使用 LOCK TABLES statement叉瘩，因?yàn)樗惶峁┤魏伪Ｗo(hù)反而降低了并發(fā)。自動(dòng)的行級(jí)鎖使這些表適用于你最忙的數(shù)據(jù)庫粘捎，同時(shí)簡(jiǎn)化了應(yīng)用邏輯因?yàn)槟悴恍枰ユi和解鎖表薇缅。因此危彩，InnoDB 存儲(chǔ)引擎成為了MySQL的默認(rèn)引擎。

除了InnoDB泳桦，其他所有的存儲(chǔ)引擎MySQL都使用表鎖 (而不是page, row, or column 鎖)汤徽。鎖操作自身沒什么開銷。但是因?yàn)橥瑫r(shí)只能有一個(gè)連接可以寫入表灸撰，為了這些引擎的最佳性能谒府，它們主要用于查詢多，插入浮毯、更新少的表完疫。

二、外部鎖 External Locking

External locking是使用文件系統(tǒng)鎖定來管理多個(gè)進(jìn)程對(duì)MyISAM 數(shù)據(jù)庫表的爭(zhēng)用亲轨。External locking 適用于單進(jìn)程的情況趋惨，但是該進(jìn)程MySQL server不能將其作為訪問表的唯一進(jìn)程。

三惦蚊、元數(shù)據(jù)鎖

MySQL 使用元數(shù)據(jù)鎖來管理數(shù)據(jù)庫對(duì)象的并發(fā)訪問，確保數(shù)據(jù)一致性讯嫂。元數(shù)據(jù)鎖不僅可以作用于tables蹦锋，還可以作用于 schemas、 stored programs (procedures, functions, triggers, and scheduled events)欧芽。

元數(shù)據(jù)鎖確實(shí)會(huì)產(chǎn)生一些開銷莉掂，隨著查詢列數(shù)量的增加而增加。多個(gè)查詢?cè)噲D訪問同一對(duì)象的時(shí)候千扔，元數(shù)據(jù)爭(zhēng)用會(huì)增加憎妙。

元數(shù)據(jù)鎖不是用于代替表定義緩存的，它的互斥鎖曲楚、鎖跟LOCK_open互斥鎖是不一樣的厘唾。如下內(nèi)容討論了元數(shù)據(jù)鎖是如何工作的。

Metadata Lock Acquisition
Metadata Lock Release

元數(shù)據(jù)鎖(MDL)按鎖住的對(duì)象來分類龙誊，可以分為global抚垃，commit，schema趟大，table鹤树，function，procedure逊朽，trigger罕伯，event，這些對(duì)象發(fā)生鎖等待時(shí)叽讳，我們?cè)趕how processlist可以分別看到如下等待信息追他。

Waiting for global read lock 
Waiting for commit lock
Waiting for schema metadata lock
Waiting for table metadata lock
Waiting for stored function metadata lock
Waiting for stored procedure metadata lock
Waiting for trigger metadata lock
Waiting for event metadata lock

場(chǎng)景一：

通過show processlist可以看到TableA上有正在進(jìn)行的操作（包括讀）坟募，此時(shí)alter table語句無法獲取到metadata 獨(dú)占鎖，會(huì)進(jìn)行等待湿酸。出現(xiàn)Waiting for table metadata lock

場(chǎng)景二：

通過show processlist看不到TableA上有任何操作婿屹，但實(shí)際上存在有未提交的事務(wù)，可以information_schema.innodb_trx中查看到推溃。在事務(wù)沒有完成之前昂利，TableA上的鎖不會(huì)釋放，alter table同樣獲取不到metadata的獨(dú)占鎖铁坎。

場(chǎng)景三：

通過show processlist看不到TableA上有任何操作蜂奸，在information_schema.innodb_trx中也沒有任何進(jìn)行中的事務(wù)。這很可能是因?yàn)樵谝粋€(gè)顯式的事務(wù)中硬萍，對(duì)TableA進(jìn)行了一個(gè)失敗的操作（比如查詢了一個(gè)不存在的字段）扩所，這時(shí)事務(wù)沒有開始，但是失敗語句獲取到的鎖依然有效朴乖。從performance_schema.events_statements_current表中可以查到失敗的語句祖屏。

狀態(tài) Waiting for table metadata lock

mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| Id  | User | Host      | db     | Command | Time | State                           | Info                                      |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| 267 | root | localhost | sbtest | Query   |    7 | Sending data                    | insert into sbtest2 select * from sbtest1 |
| 271 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | alter table sbtest2 add test1 int         |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)

狀態(tài) Waiting for global read lock

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+------------------------------+------------------+
| 1 | root | localhost:5202  | test | Query | 12 | altering table | alter table t1 add c3 bigint |
| 2 | root | localhost:14699 | test | Query | 3 | Waiting for global read lock | set global read_only=on |
| 3 | root | localhost:17085 | NULL | Query | 0 | init | show processlist |
+----+------+-----------------+------+---------+------+------------------------------+------------------+

狀態(tài) Waiting for commit lock

mysql> show processlist;
+----+------+-----------------+------+------------+------+-------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+------------+------+-------------------------+------------------+
| 1 | root | 127.0.0.1:5202 | test | Query | 7 | Waiting for commit lock | commit |
| 2 | root | 127.0.0.1:14699 | test | Sleep | 13 | | NULL |
| 3 | root | 127.0.0.1:17085 | NULL | Query | 0 | init | show processlist |
+----+------+-----------------+------+---------+------+-------------------------+------------------+

metadata lock.png

MDL鎖的診斷
MySQL 5.7版本之前并沒有提供一個(gè)方便的途徑來查看MDL鎖，github上有一名為mysql-plugin-mdl-info的項(xiàng)目买羞，通過插件的方式來查看袁勺，非常有想法的實(shí)現(xiàn)。好在官方也意識(shí)到了這個(gè)問題畜普，于是在MySQL 5.7中的performance_schea庫下新增了一張表metadata_locks期丰，用其來查看MDL鎖那是相當(dāng)?shù)姆奖悖?/p>

mysql> SELECT * FROM performance_schema.metadata_locks;
    
mysql> SELECT * FROM performance_schema.setup_instruments;

對(duì)于5.7之前版本，沒有具體的方法查看全局讀鎖吃挑，表鎖钝荡，MDL鎖信息〔俺模可以通過 information_schema.processlist埠通， performance_schema.events_statements_history， performance_schema.events_statements_current 等表大概推測(cè)阻塞情況

四约炎、 InnoDB 鎖

Shared and Exclusive Locks 共享鎖/排他鎖
Intention Locks 意向鎖
Record Locks 行鎖
Gap Locks 間隙鎖
Next-Key Locks 臨鍵鎖
Insert Intention Locks 插入意向鎖
AUTO-INC Locks 自增鎖

五植阴、 InnoDB 死鎖

死鎖是這樣一個(gè)場(chǎng)景，不同的事務(wù)不能推進(jìn)圾浅，因?yàn)楸舜硕颊剂藢?duì)方需要的鎖掠手。因?yàn)檫@些事務(wù)都在等資源釋放，而不釋放自己占的鎖狸捕。

死鎖發(fā)生在事務(wù)在多個(gè)表產(chǎn)生行鎖喷鸽，但是以相反的順序的時(shí)候 (比如通過 UPDATE or SELECT ... FOR UPDATE)。死鎖還會(huì)發(fā)生在灸拍，這些statements鎖住某范圍的 index records and gaps的時(shí)候做祝，每個(gè)事務(wù)都在請(qǐng)求鎖但是沒有而導(dǎo)致的超時(shí)問題砾省。死鎖的范例，參見 Section 14.7.5.1, “An InnoDB Deadlock Example”混槐。

要降低死鎖的幾率编兄，使用事務(wù)而不是 LOCK TABLES statements；使得insert or update 數(shù)據(jù)的事務(wù)足夠的小声登，這樣它們不會(huì)長時(shí)間打開狠鸳；當(dāng)不同的事務(wù)update多個(gè)表或者大范圍行的時(shí)候，在每個(gè)事務(wù)都要使用同樣的操作順序 (比如 SELECT ... FOR UPDATE)悯嗓；給 SELECT ... FOR UPDATE and UPDATE ... WHERE statements 操作的列增加索引件舵。死鎖的幾率不受隔離級(jí)別的影響，因?yàn)楦綦x級(jí)別是變更的讀操作脯厨，而死鎖是因?yàn)閷懖僮鲗?dǎo)致的铅祸。關(guān)于如何避免死鎖和恢復(fù)死鎖，參見 Section 14.7.5.3, “How to Minimize and Handle Deadlocks”.

如果死鎖發(fā)生合武，InnoDB 會(huì)檢測(cè)到临梗，然后回滾其中一個(gè)事務(wù) (the victim)。因此稼跳，即使你應(yīng)用的邏輯是對(duì)的夜焦，如果該事務(wù)必須被重試，你還是必須要處理這個(gè)問題岂贩。查看 InnoDB用戶事務(wù)最近的死鎖，使用命令 SHOW ENGINE INNODB STATUS 巷波。如果頻繁的死鎖導(dǎo)致了事務(wù)問題或者應(yīng)用報(bào)錯(cuò)萎津，配置innodb_print_all_deadlocks 設(shè)置為 enabled 來將所有的死鎖信息都打印到mysqld error log。關(guān)于死鎖如何自動(dòng)檢測(cè)和處理的更詳細(xì)信息抹镊，參見Section 14.7.5.2, “Deadlock Detection and Rollback”锉屈。

六、死鎖檢測(cè)和回滾

InnoDB自動(dòng)檢測(cè)事務(wù) 死鎖并且回滾其中一個(gè)事務(wù)來打破死鎖垮耳。 InnoDB 選擇小一些的事務(wù)來進(jìn)行回滾颈渊，判斷事務(wù)大小的標(biāo)準(zhǔn)是 inserted, updated, or deleted的行數(shù)。

只有設(shè)置innodb_table_locks = 1 (the default) and autocommit = 0的時(shí)候终佛，InnoDB 才會(huì)識(shí)別表級(jí)鎖俊嗽，MySQL才會(huì)感知行級(jí)鎖。否則铃彰，當(dāng)MySQL LOCK TABLES statement 設(shè)置了表鎖绍豁，或其他不是InnoDB的存儲(chǔ)引擎設(shè)置鎖的時(shí)候，InnoDB 不能檢測(cè)到死鎖牙捉≈褡幔可以通過設(shè)置 innodb_lock_wait_timeout 系統(tǒng)變量來解決這個(gè)問題敬飒。

當(dāng) InnoDB 執(zhí)行一個(gè)事務(wù)的完全回滾時(shí)，所有該事務(wù)設(shè)置的鎖會(huì)被釋放芬位。但是无拗，如果一個(gè)SQL statement 回滾出錯(cuò)，那么該statement設(shè)置的一些鎖還會(huì)保留昧碉。這是因?yàn)?code>InnoDB以某格式存儲(chǔ)行鎖英染，這樣它就無法知道哪個(gè)鎖是哪個(gè)語句設(shè)置的。

如果一個(gè) SELECT 在事務(wù)內(nèi)調(diào)用了存儲(chǔ)函數(shù)晌纫，然后該函數(shù)內(nèi)的一個(gè)statement失敗了税迷，那么這個(gè)statement會(huì)回滾。此外锹漱，如果之后執(zhí)行了 ROLLBACK 箭养，整個(gè)事務(wù)都會(huì)回滾。

如果InnoDB 監(jiān)控的 LATEST DETECTED DEADLOCK 部分輸出包含了如下信息哥牍， “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION”毕泌，這表明當(dāng)前等待清單內(nèi)的事務(wù)數(shù)量已經(jīng)達(dá)到了200上限。等待清單超過200的事務(wù)會(huì)被視為死鎖嗅辣，正在等待檢查等待清單的事務(wù)會(huì)被回滾撼泛。如果等待清單內(nèi)事務(wù)產(chǎn)生的鎖的線程超過1,000,000 locks，同樣的報(bào)錯(cuò)還會(huì)發(fā)生澡谭。

如何通過技術(shù)管理數(shù)據(jù)庫來避免死鎖愿题，參見 Section 14.7.5, “Deadlocks in InnoDB”.

通過檢查innodb_row_lock變量來檢查innodb行鎖爭(zhēng)用情況

select * from information_schema.innodb_locks 了解鎖等待

設(shè)置innodb monitors觀察鎖沖突情況
show enging innodb status

首先，從鎖的粒度蛙奖，我們可以分成兩大類：
表鎖
開銷小潘酗，加鎖快；不會(huì)出現(xiàn)死鎖雁仲；鎖定力度大仔夺，發(fā)生鎖沖突概率高，并發(fā)度最低
行鎖
開銷大攒砖，加鎖慢缸兔；會(huì)出現(xiàn)死鎖；鎖定粒度小吹艇，發(fā)生鎖沖突的概率低惰蜜，并發(fā)度高

不同的存儲(chǔ)引擎支持的鎖粒度是不一樣的：InnoDB行鎖和表鎖都支持、MyISAM只支持表鎖掐暮！InnoDB只有通過索引條件檢索數(shù)據(jù)才使用行級(jí)鎖蝎抽，否則，InnoDB使用表鎖也就是說，InnoDB的行鎖是基于索引的樟结！

MySQL里面的鎖大致可以分成全局鎖养交、表級(jí)鎖和行鎖三類
MySQL提供了一個(gè)加全局讀鎖的方法，命令是Flush tables with read lock瓢宦。
MySQL里面表級(jí)別的鎖有兩種：一種是表鎖碎连，一種是元數(shù)據(jù)鎖（meta data lock，MDL）

MySQL 5.6 鎖

MySQL 5.6 鎖

Optimizing Locking Operations

一、內(nèi)部鎖 Internal Locking Methods

行級(jí)鎖 Row-Level Locking

表級(jí)鎖 Table-Level Locking

表鎖

二、外部鎖 External Locking

三惦蚊、元數(shù)據(jù)鎖

四约炎、 InnoDB 鎖

五植阴、 InnoDB 死鎖

六、死鎖檢測(cè)和回滾

MySQL 5.6 鎖

Optimizing Locking Operations

一、 內(nèi)部鎖 Internal Locking Methods

行級(jí)鎖 Row-Level Locking

表級(jí)鎖 Table-Level Locking

表鎖

二、 外部鎖 External Locking

三惦蚊、 元數(shù)據(jù)鎖

四约炎、 InnoDB 鎖

五植阴、 InnoDB 死鎖

六、 死鎖檢測(cè)和回滾

一、內(nèi)部鎖 Internal Locking Methods

二、外部鎖 External Locking

三惦蚊、元數(shù)據(jù)鎖

六、死鎖檢測(cè)和回滾