建站
咨詢
售后
建站咨詢
新聞中心
查詢mysql 哪些表正在被鎖狀態(tài)
1.查看表是否被鎖：
創(chuàng)新互聯(lián)成立于2013年，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元永州做網(wǎng)站,已為上家服務(wù),為永州各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:13518219792
（1）直接在mysql命令行執(zhí)行:showengineinnodbstatus\G。
（2）查看造成死鎖的sql語(yǔ)句，分析索引情況，然后優(yōu)化sql。
（3）然后showprocesslist,查看造成死鎖占用時(shí)間長(zhǎng)的sql語(yǔ)句。
（4）showstatuslike‘%lock%。
2.查看表被鎖狀態(tài)和結(jié)束死鎖步驟：
（1）查看表被鎖狀態(tài)：showOPENTABLESwhereIn_use0;這個(gè)語(yǔ)句記錄當(dāng)前鎖表狀態(tài)。
（2）查詢進(jìn)程：showprocesslist查詢表被鎖進(jìn)程；查詢到相應(yīng)進(jìn)程killid。
（3）分析鎖表的SQL：分析相應(yīng)SQL，給表加索引，常用字段加索引，表關(guān)聯(lián)字段加索引。
（4）查看正在鎖的事物：SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS。
（5）查看等待鎖的事物：SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS。
擴(kuò)展資料
MySQL鎖定狀態(tài)查看命令：
Checkingtable：正在檢查數(shù)據(jù)表（這是自動(dòng)的）。
Closingtables：正在將表中修改的數(shù)據(jù)刷新到磁盤中，同時(shí)正在關(guān)閉已經(jīng)用完的表。這是一個(gè)很快的操作，如果不是這樣的話，就應(yīng)該確認(rèn)磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負(fù)中。
ConnectOut：復(fù)制從服務(wù)器正在連接主服務(wù)器。
Copyingtotmptableondisk：由于臨時(shí)結(jié)果集大于tmp_table_size，正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤存儲(chǔ)以此節(jié)省內(nèi)存。
Creatingtmptable：正在創(chuàng)建臨時(shí)表以存放部分查詢結(jié)果。
deletingfrommaintable：服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個(gè)表。
deletingfromreferencetables：服務(wù)器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄。
Flushingtables：正在執(zhí)行FLUSHTABLES，等待其他線程關(guān)閉數(shù)據(jù)表。
Killed：發(fā)送了一個(gè)kill請(qǐng)求給某線程，那么這個(gè)線程將會(huì)檢查kill標(biāo)志位，同時(shí)會(huì)放棄下一個(gè)kill請(qǐng)求。MySQL會(huì)在每次的主循環(huán)中檢查kill標(biāo)志位，不過(guò)有些情況下該線程可能會(huì)過(guò)一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請(qǐng)求會(huì)在鎖釋放時(shí)馬上生效。
Locked：被其他查詢鎖住了。
Sendingdata：正在處理SELECT查詢的記錄，同時(shí)正在把結(jié)果發(fā)送給客戶端。
Sortingforgroup：正在為GROUPBY做排序。
Sortingfororder：正在為ORDERBY做排序。
Openingtables：這個(gè)過(guò)程應(yīng)該會(huì)很快，除非受到其他因素的干擾。例如，在執(zhí)ALTERTABLE或LOCKTABLE語(yǔ)句行完以前，數(shù)據(jù)表無(wú)法被其他線程打開。正嘗試打開一個(gè)表。
Removingduplicates：正在執(zhí)行一個(gè)SELECTDISTINCT方式的查詢，但是MySQL無(wú)法在前一個(gè)階段優(yōu)化掉那些重復(fù)的記錄。因此，MySQL需要再次去掉重復(fù)的記錄，然后再把結(jié)果發(fā)送給客戶端。
Reopentable：獲得了對(duì)一個(gè)表的鎖，但是必須在表結(jié)構(gòu)修改之后才能獲得這個(gè)鎖。已經(jīng)釋放鎖，關(guān)閉數(shù)據(jù)表，正嘗試重新打開數(shù)據(jù)表。
Repairbysorting：修復(fù)指令正在排序以創(chuàng)建索引。
Repairwithkeycache：修復(fù)指令正在利用索引緩存一個(gè)一個(gè)地創(chuàng)建新索引。它會(huì)比Repairbysorting慢些。
Searchingrowsforupdate：正在講符合條件的記錄找出來(lái)以備更新。它必須在UPDATE要修改相關(guān)的記錄之前就完成了。
Sleeping：正在等待客戶端發(fā)送新請(qǐng)求。
Systemlock：正在等待取得一個(gè)外部的系統(tǒng)鎖。如果當(dāng)前沒(méi)有運(yùn)行多個(gè)mysqld服務(wù)器同時(shí)請(qǐng)求同一個(gè)表，那么可以通過(guò)增加--skip-external-locking參數(shù)來(lái)禁止外部系統(tǒng)鎖。
Upgradinglock：INSERTDELAYED正在嘗試取得一個(gè)鎖表以插入新記錄。
Updating：正在搜索匹配的記錄，并且修改它們。
UserLock：正在等待GET_LOCK()。
Waitingfortables：該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能的重新打開數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個(gè)表。
waitingforhandlerinsert：INSERTDELAYED已經(jīng)處理完了所有待處理的插入操作，正在等待新的請(qǐng)求。
如何查詢mysql中是否表被鎖
可直接在mysql命令行執(zhí)行：show engine innodb status\G;
查看造成死鎖的sql語(yǔ)句，分析索引情況，然后優(yōu)化sql然后show processlist;
另外可以打開慢查詢?nèi)罩?，linux下打開需在my.cnf的[mysqld]里面加上以下內(nèi)容：
怎么查看mysql表是否被鎖定
當(dāng)你開始執(zhí)行一個(gè) ALTER ，而你遇到了可怕的“元數(shù)據(jù)鎖定等待”，我敢肯定你一定遇見過(guò)。我最近遇到了一個(gè)案例，其中被更改的表要執(zhí)行一個(gè)很小范圍的更新（100行）。ALTER 在負(fù)載測(cè)試期間一直等待了幾個(gè)小時(shí)。在停止負(fù)載測(cè)試后，ALTER 按預(yù)期在不到一秒的時(shí)間內(nèi)就完成了。那么這里發(fā)生了什么？
檢查外鍵
每當(dāng)有奇數(shù)次鎖定時(shí)，我的第一直覺(jué)就是檢查外鍵。當(dāng)然這張表有一些外鍵引用了一個(gè)更繁忙的表。但是這種行為似乎仍然很奇怪。對(duì)表運(yùn)行 ALTER 時(shí)，會(huì)針對(duì)子表請(qǐng)求一個(gè) SHARED_UPGRADEABLE 元數(shù)據(jù)鎖。還有針對(duì)父級(jí)的 SHARED_READ_ONLY 元數(shù)據(jù)鎖。
我們來(lái)看看如何根據(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ù)必注意鎖定順序是序列化的：語(yǔ)句逐個(gè)獲取元數(shù)據(jù)鎖，而不是同時(shí)獲取，并在此過(guò)程中執(zhí)行死鎖檢測(cè)。
通常在考慮隊(duì)列時(shí)考慮先進(jìn)先出。如果我發(fā)出以下三個(gè)語(yǔ)句（按此順序），它們將按以下順序完成：
1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent
但是當(dāng)子 ALTER 語(yǔ)句請(qǐng)求對(duì)父進(jìn)行讀取鎖定時(shí)，盡管排序，但兩個(gè)插入將在 ALTER 之前完成。以下是可以演示此示例的示例場(chǎng)景：
數(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ì)完成。更糟糕的是，由于子表上的寫鎖定成功（但是完整語(yǔ)句正在等待獲取父讀鎖定），所以針對(duì)子表的所有傳入讀取請(qǐng)求都將被阻止！
另外，請(qǐng)考慮一下您通常如何對(duì)無(wú)法完成的語(yǔ)句進(jìn)行故障排除。您查看已經(jīng)打開較長(zhǎng)時(shí)間的事務(wù)（在進(jìn)程列表和 InnoDB 狀態(tài)中）。但由于阻塞線程現(xiàn)在比 ALTER 線程更年輕，因此您將看到的最舊的事務(wù)/線程是 ALTER 。
這正是這種情況下發(fā)生的情況。在準(zhǔn)備發(fā)布時(shí)，我們的客戶端正在運(yùn)行 ALTER 語(yǔ)句并結(jié)合負(fù)載測(cè)試（一種非常好的做法！）以確保順利發(fā)布。問(wèn)題是負(fù)載測(cè)試保持對(duì)父表打開一個(gè)活動(dòng)的寫事務(wù)。這并不是說(shuō)它只是一直在寫，而是有多個(gè)線程，一個(gè)總是活躍的。 這阻止了 ALTER 完成并阻止對(duì)相對(duì)靜態(tài)的子表的隨后的讀請(qǐng)求。
幸運(yùn)的是，這個(gè)問(wèn)題有一個(gè)解決方案（除了從設(shè)計(jì)模式中驅(qū)逐外鍵）。變量?max_write_lock_count[2]?可用于允許在寫入鎖定之后在讀取鎖定之前授予讀取鎖定連續(xù)寫鎖。默認(rèn)情況下，此變量設(shè)置為 18446744073709551615，如果你對(duì)該表發(fā)出 10,000 次寫入/秒，那么你的讀將被鎖定 5800 萬(wàn)年……
MYSQL數(shù)據(jù)庫(kù)怎么查看 哪些表被鎖了
以下五種方法可以快速定位全局鎖的位置，僅供參考。
方法1：利用 metadata_locks 視圖
此方法僅適用于 MySQL 5.7 以上版本，該版本 performance_schema 新增了 metadata_locks，如果上鎖前啟用了元數(shù)據(jù)鎖的探針（默認(rèn)是未啟用的），可以比較容易的定位全局鎖會(huì)話。
方法2：利用 events_statements_history 視圖此方法適用于 MySQL 5.6 以上版本，啟用 performance_schema.eventsstatements_history（5.6 默認(rèn)未啟用，5.7 默認(rèn)啟用），該表會(huì) SQL 歷史記錄執(zhí)行，如果請(qǐng)求太多，會(huì)自動(dòng)清理早期的信息，有可能將上鎖會(huì)話的信息清理掉。
方法3：利用 gdb 工具如果上述兩種都用不了或者沒(méi)來(lái)得及啟用，可以嘗試第三種方法。利用 gdb 找到所有線程信息，查看每個(gè)線程中持有全局鎖對(duì)象，輸出對(duì)應(yīng)的會(huì)話 ID，為了便于快速定位，我寫成了腳本形式。也可以使用 gdb 交互模式，但 attach mysql 進(jìn)程后 mysql 會(huì)完全 hang 住，讀請(qǐng)求也會(huì)受到影響，不建議使用交互模式。
方法4：show processlist
如果備份程序使用的特定用戶執(zhí)行備份，如果是 root 用戶備份，那 time 值越大的是持鎖會(huì)話的概率越大，如果業(yè)務(wù)也用 root 訪問(wèn)，重點(diǎn)是 state 和 info 為空的，這里有個(gè)小技巧可以快速篩選，篩選后嘗試 kill 對(duì)應(yīng) ID，再觀察是否還有 wait global read lock 狀態(tài)的會(huì)話。
方法5：重啟試試！
分享文章：mysql行鎖怎么檢測(cè) mysql數(shù)據(jù)庫(kù)行鎖
標(biāo)題來(lái)源：http://fisionsoft.com.cn/article/doesspi.html
