當(dāng)你開始執(zhí)行一個(gè) ALTER ，而你遇到了可怕的“元數(shù)據(jù)鎖定等待”，我敢肯定你一定遇見過。我最近遇到了一個(gè)案例，其中被更改的表要執(zhí)行一個(gè)很小范圍的更新（100行）。ALTER 在負(fù)載測(cè)試期間一直等待了幾個(gè)小時(shí)。在停止負(fù)載測(cè)試后，ALTER 按預(yù)期在不到一秒的時(shí)間內(nèi)就完成了。那么這里發(fā)生了什么？
成都創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比古浪網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式古浪網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋古浪地區(qū)。費(fèi)用合理售后完善，10多年實(shí)體公司更值得信賴。
檢查外鍵
每當(dāng)有奇數(shù)次鎖定時(shí)，我的第一直覺就是檢查外鍵。當(dāng)然這張表有一些外鍵引用了一個(gè)更繁忙的表。但是這種行為似乎仍然很奇怪。對(duì)表運(yùn)行 ALTER 時(shí)，會(huì)針對(duì)子表請(qǐng)求一個(gè) SHARED_UPGRADEABLE 元數(shù)據(jù)鎖。還有針對(duì)父級(jí)的 SHARED_READ_ONLY 元數(shù)據(jù)鎖。
我們來看看如何根據(jù)文檔獲取元數(shù)據(jù)鎖定[1]：
如果給定鎖定有多個(gè)服務(wù)器，則首先滿足最高優(yōu)先級(jí)鎖定請(qǐng)求，并且與 max_write_lock_count系統(tǒng)變量有關(guān)。寫鎖定請(qǐng)求的優(yōu)先級(jí)高于讀取鎖定請(qǐng)求。
[1]：
請(qǐng)務(wù)必注意鎖定順序是序列化的：語句逐個(gè)獲取元數(shù)據(jù)鎖，而不是同時(shí)獲取，并在此過程中執(zhí)行死鎖檢測(cè)。
通常在考慮隊(duì)列時(shí)考慮先進(jìn)先出。如果我發(fā)出以下三個(gè)語句（按此順序），它們將按以下順序完成：
1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent
但是當(dāng)子 ALTER 語句請(qǐng)求對(duì)父進(jìn)行讀取鎖定時(shí)，盡管排序，但兩個(gè)插入將在 ALTER 之前完成。以下是可以演示此示例的示例場景：
數(shù)據(jù)初始化：
CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`val` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent` (`parent_id`),
CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;
INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");
Session 1:
start transaction;update parent set val = "four-new" where id = 4;
Session 2:
alter table child add index `idx_new` (val);
Session 3:
start transaction;update parent set val = "three-new" where id = 3;
此時(shí)，會(huì)話 1 具有打開的事務(wù)，并且處于休眠狀態(tài)，并在父級(jí)上授予寫入元數(shù)據(jù)鎖定。 會(huì)話 2 具有在子級(jí)上授予的可升級(jí)（寫入）鎖定，并且正在等待父級(jí)的讀取鎖定。最后會(huì)話 3 具有針對(duì)父級(jí)的授權(quán)寫入鎖定：
mysql select * from performance_schema.metadata_locks;+-------------+-------------+-------------------+---------------+-------------+| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE ? ? ? ? | LOCK_DURATION | LOCK_STATUS |+-------------+-------------+-------------------+---------------+-------------+| TABLE ? ? ? | child ? ? ? | SHARED_UPGRADABLE | TRANSACTION ? | GRANTED ? ? | - ALTER (S2)| TABLE ? ? ? | parent ? ? ?| SHARED_WRITE ? ? ?| TRANSACTION ? | GRANTED ? ? | - UPDATE (S1)| TABLE ? ? ? | parent ? ? ?| SHARED_WRITE ? ? ?| TRANSACTION ? | GRANTED ? ? | - UPDATE (S3)| TABLE ? ? ? | parent ? ? ?| SHARED_READ_ONLY ?| STATEMENT ? ? | PENDING ? ? | - ALTER (S2)+-------------+-------------+-------------------+---------------+-------------+
請(qǐng)注意，具有掛起鎖定狀態(tài)的唯一會(huì)話是會(huì)話 2（ALTER）。會(huì)話 1 和會(huì)話 3 （分別在 ALTER 之前和之后發(fā)布）都被授予了寫鎖。排序失敗的地方是在會(huì)話 1 上發(fā)生提交的時(shí)候。在考慮有序隊(duì)列時(shí)，人們會(huì)期望會(huì)話 2 獲得鎖定，事情就會(huì)繼續(xù)進(jìn)行。但是，由于元數(shù)據(jù)鎖定系統(tǒng)的優(yōu)先級(jí)性質(zhì)，會(huì)話 3 具有鎖定，會(huì)話 2 仍然等待。
如果另一個(gè)寫入會(huì)話進(jìn)入并啟動(dòng)新事務(wù)并獲取針對(duì)父表的寫鎖定，則即使會(huì)話 3 完成，ALTER 仍將被阻止。
只要我保持一個(gè)對(duì)父表打開元數(shù)據(jù)鎖定的活動(dòng)事務(wù)，子表上的 ALTER 將永遠(yuǎn)不會(huì)完成。更糟糕的是，由于子表上的寫鎖定成功（但是完整語句正在等待獲取父讀鎖定），所以針對(duì)子表的所有傳入讀取請(qǐng)求都將被阻止！
另外，請(qǐng)考慮一下您通常如何對(duì)無法完成的語句進(jìn)行故障排除。您查看已經(jīng)打開較長時(shí)間的事務(wù)（在進(jìn)程列表和 InnoDB 狀態(tài)中）。但由于阻塞線程現(xiàn)在比 ALTER 線程更年輕，因此您將看到的最舊的事務(wù)/線程是 ALTER 。
這正是這種情況下發(fā)生的情況。在準(zhǔn)備發(fā)布時(shí)，我們的客戶端正在運(yùn)行 ALTER 語句并結(jié)合負(fù)載測(cè)試（一種非常好的做法?。┮源_保順利發(fā)布。問題是負(fù)載測(cè)試保持對(duì)父表打開一個(gè)活動(dòng)的寫事務(wù)。這并不是說它只是一直在寫，而是有多個(gè)線程，一個(gè)總是活躍的。 這阻止了 ALTER 完成并阻止對(duì)相對(duì)靜態(tài)的子表的隨后的讀請(qǐng)求。
幸運(yùn)的是，這個(gè)問題有一個(gè)解決方案（除了從設(shè)計(jì)模式中驅(qū)逐外鍵）。變量?max_write_lock_count[2]?可用于允許在寫入鎖定之后在讀取鎖定之前授予讀取鎖定連續(xù)寫鎖。默認(rèn)情況下，此變量設(shè)置為 18446744073709551615，如果你對(duì)該表發(fā)出 10,000 次寫入/秒，那么你的讀將被鎖定 5800 萬年……
第一步，查看行鎖使用情況，命令：
show statue like 'innodb_row_lock%';
如下圖所示：
第二步，創(chuàng)建數(shù)據(jù)庫表monitor_amount，如下圖所示：
第三步，查看innodb的狀態(tài)，命令：
show innodb status \G;
如下圖所示：
第四步，向數(shù)據(jù)庫表monitor_amount插入四條記錄，如下圖所示：
第五步，再次查看innodb狀態(tài)，如下圖所示：
第六步，可以利用刪除表命令來停止查看，如下圖所示：
1.查看表被鎖狀態(tài)
2.查看造成死鎖的sql語句
3.查詢進(jìn)程
4.解鎖(刪除進(jìn)程)
5.查看正在鎖的事物? (8.0以下版本)
6.查看等待鎖的事物?(8.0以下版本)
一：檢查是否鎖表, 查詢進(jìn)程并殺死進(jìn)程
1) 查詢是否鎖表
show open tables where in_use 0;
2) 查詢進(jìn)程（如果您有SUPER權(quán)限，您可以看到所有線程。否則，您只能看到您自己的線程）
show processlist;
二：查看在鎖事務(wù),殺死事務(wù)對(duì)應(yīng)的線程ID
1) 查看正在鎖的事務(wù)
select * from information_schema.INNODB_LOCKS;
2) 殺死進(jìn)程id（就是[select * from information_schema.INNODB_LOCKS; ]命令的trx_mysql_thread_id列）
kill 線程ID
3) 查看等待鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
其它：
1) 查看服務(wù)器狀態(tài)
show status like '%lock%';
2) 查看超時(shí)時(shí)間：
show variables like '%timeout%';
可直接在mysql命令行執(zhí)行：show engine innodb status\G;
查看造成死鎖的sql語句，分析索引情況，然后優(yōu)化sql然后show processlist;
show status like ‘%lock%’
show OPEN TABLES where In_use 0; 這個(gè)語句記錄當(dāng)前鎖表狀態(tài)
另外可以打開慢查詢?nèi)罩?，linux下打開需在my.cnf的[mysqld]里面加上以下內(nèi)容：
slow_query_log=TRUE(有些mysql版本是ON)
slow_query_log_file=/usr/local/mysql/slow_query_log.txt
long_query_time=3
擴(kuò)展資料：
MySQL鎖定狀態(tài)查看命令
Checking table：正在檢查數(shù)據(jù)表（這是自動(dòng)的）。?
Closing tables：正在將表中修改的數(shù)據(jù)刷新到磁盤中，同時(shí)正在關(guān)閉已經(jīng)用完的表。這是一個(gè)很快的操作，如果不是這樣的話，就應(yīng)該確認(rèn)磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負(fù)中。
Connect Out：復(fù)制從服務(wù)器正在連接主服務(wù)器。?
Copying to tmp table on disk：由于臨時(shí)結(jié)果集大于tmp_table_size，正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤存儲(chǔ)以此節(jié)省內(nèi)存。
Creating tmp table：正在創(chuàng)建臨時(shí)表以存放部分查詢結(jié)果。
deleting from main table：服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個(gè)表。
重慶分公司
重慶分公司
