問題：Lock wait timeout exceeded; try restarting transaction
創(chuàng)新互聯(lián)專注于企業(yè)全網(wǎng)營銷推廣、網(wǎng)站重做改版、民和網(wǎng)站定制設(shè)計、自適應品牌網(wǎng)站建設(shè)、html5、商城網(wǎng)站建設(shè)、集團公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應式網(wǎng)頁設(shè)計等建站業(yè)務，價格優(yōu)惠性價比高，為民和等各大城市提供網(wǎng)站開發(fā)制作服務。
MySQL版本：5.6.44
官方文檔
意思是：InnoDB在鎖等待超時過期時報告此錯誤。等待時間過長的語句被回滾（而不是整個事務）。如果SQL語句需要等待其他事務完成的時間更長，則可以增加 innodb_lock_wait_timeout 配置選項的值；如果太多長時間運行的事務導致鎖定問題并降低繁忙系統(tǒng)上的并發(fā)性，則可以減少該選項的值。
鎖等待超時，可能是出現(xiàn)了死鎖，也可能有事務長時間未提交
庫：information_schema
表：
查看各表信息
innodb_trx 表
innodb_locks 表
innodb_lock_waits 表
processlist 表
模擬出現(xiàn)死鎖
準備一張只有主鍵的表：t_test (id)
Navicat 新建查詢1
Navicat 新建查詢2
檢查是否鎖表
查詢當前正在執(zhí)行的事務
查詢當前出現(xiàn)的鎖
查詢鎖等待對應的關(guān)系
查詢等待鎖的事務所執(zhí)行的SQL
最后，事務2 等待鎖超時報錯： Lock wait timeout exceeded; try restarting transaction;
通過事務線程ID查找進程信息
win10 查看端口信息
1)interactive_timeout:
參數(shù)含義：服務器關(guān)閉交互式連接前等待活動的秒數(shù)。交互式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端。
參數(shù)默認值：28800秒（8小時）
(2)wait_timeout:
參數(shù)含義：服務器關(guān)閉非交互連接之前等待活動的秒數(shù)。
在線程啟動時，根據(jù)全局wait_timeout值或全局interactive_timeout值初始化會話wait_timeout值，取決于客戶端類型(由mysql_real_connect()的連接選項CLIENT_INTERACTIVE定義)。
參數(shù)默認值：28800秒（8小時）
MySQL服務器所支持的最大連接數(shù)是有上限的，因為每個連接的建立都會消耗內(nèi)存，因此我們希望客戶端在連接到My SQL Server 處理完相應的操作后，應該斷開連接并釋放占用的內(nèi)存。如果你的MySQL Server有大量的閑置連接，他們不僅會白白消耗內(nèi)存，而且如果連接一直在累加而不斷開，最終肯定會達到MySQL Server的連接上限數(shù)，這會報'too many connections'的錯誤。對于wait_timeout的值設(shè)定，應該根據(jù)系統(tǒng)的運行情況來判斷。在系統(tǒng)運行一段時間后，可以通過show processlist命令查看當前系統(tǒng)的連接狀態(tài)，如果發(fā)現(xiàn)有大量的sleep狀態(tài)的連接進程，則說明該參數(shù)設(shè)置的過大，可以進行適當?shù)恼{(diào)整小些。
問題：
如果在配置文件my.cnf中只設(shè)置參數(shù)wait_timeout=100,則重啟服務器后進入，執(zhí)行:
Mysql show variables like “%timeout%”;
會發(fā)現(xiàn)參數(shù)設(shè)置并未生效，仍然為28800（即默認的8個小時）。
查詢資料后，要同時設(shè)置interactive_timeout和wait_timeout才會生效。
【mysqld】
wait_timeout=100
interactive_timeout=100
重啟MySQL Server進入后，查看設(shè)置已經(jīng)生效。
建議您可以下載安裝NavicatPremium。百度搜索:NavicatPremium.解壓NavicatPremium.rar。啟動NavicatPremium.exe連接-》mysql。配置遠程mysql賬號，密碼。填寫之后，點擊連接測試按鈕。雙擊左側(cè)的mysql配置鏈接。選擇你要的數(shù)據(jù)庫，再雙擊
my.cnf默認都是沒有的，但其實你裝的時候會在/usr/share/mysql 這個路徑下有類似的，根據(jù)你數(shù)據(jù)庫大小不同的推薦配置，有my-small.cnf，my-large.cnf等等，如果需要配置文件，選擇一個拷到/etc下，重命名為my.cnf即可，默認超時時間等都在這里進行配置，這樣啟動會就會是你設(shè)置的默認值了
如果你在命令行里改，只會修改當前會話，退出重進或者重啟mysql之后就會變回默認值
back_log：MySQL服務器連接請求隊列所能處理的最大連接請求數(shù)，如果隊列放滿了，后續(xù)的連接才會拒絕。當主要的MySQL線程在很短時間內(nèi)獲取大量連接請求時，這個參數(shù)會生效。接下來，MySQL主線程會花費很短的時間去檢查連接，然后開啟新的線程。這個參數(shù)指定了MySQL的TCP/IP監(jiān)聽隊列的大小。如果MySQL服務器在短時間內(nèi)有大量的連接，可以增加這個參數(shù)。
文件相關(guān)參數(shù)sync_binlog：控制二進制日志被同步到磁盤前二進制日志提交組的數(shù)量。當這個參數(shù)為0的時候，二進制日志不會被同步到磁盤；當這個參數(shù)設(shè)為0以上的數(shù)值時，就會有設(shè)置該數(shù)值的二進制提交組定期同步日志到磁盤。當這個參數(shù)設(shè)為1的時候，所有事務在提交前會被同步到二進制日志中，因而即使MySQL服務器發(fā)生意外重啟，任何二進制日志中沒有的事務只會處于準備狀態(tài)，這會導致MySQL服務器自動恢復以回滾這些事務。這樣就會保證二進制日志不會丟失事務，是最安全的選項；同時由于增加了磁盤寫，這對性能有一定降低。將這個參數(shù)設(shè)為1以上的數(shù)值會提高數(shù)據(jù)庫的性能，但同時會伴隨數(shù)據(jù)丟失的風險。建議將該參數(shù)設(shè)為2、4、6、8、16
當你開始執(zhí)行一個 ALTER ，而你遇到了可怕的“元數(shù)據(jù)鎖定等待”，我敢肯定你一定遇見過。我最近遇到了一個案例，其中被更改的表要執(zhí)行一個很小范圍的更新（100行）。ALTER 在負載測試期間一直等待了幾個小時。在停止負載測試后，ALTER 按預期在不到一秒的時間內(nèi)就完成了。那么這里發(fā)生了什么？
檢查外鍵
每當有奇數(shù)次鎖定時，我的第一直覺就是檢查外鍵。當然這張表有一些外鍵引用了一個更繁忙的表。但是這種行為似乎仍然很奇怪。對表運行 ALTER 時，會針對子表請求一個 SHARED_UPGRADEABLE 元數(shù)據(jù)鎖。還有針對父級的 SHARED_READ_ONLY 元數(shù)據(jù)鎖。
我們來看看如何根據(jù)文檔獲取元數(shù)據(jù)鎖定[1]：
如果給定鎖定有多個服務器，則首先滿足最高優(yōu)先級鎖定請求，并且與 max_write_lock_count系統(tǒng)變量有關(guān)。寫鎖定請求的優(yōu)先級高于讀取鎖定請求。
[1]：
請務必注意鎖定順序是序列化的：語句逐個獲取元數(shù)據(jù)鎖，而不是同時獲取，并在此過程中執(zhí)行死鎖檢測。
通常在考慮隊列時考慮先進先出。如果我發(fā)出以下三個語句（按此順序），它們將按以下順序完成：
1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent
但是當子 ALTER 語句請求對父進行讀取鎖定時，盡管排序，但兩個插入將在 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;
此時，會話 1 具有打開的事務，并且處于休眠狀態(tài)，并在父級上授予寫入元數(shù)據(jù)鎖定。 會話 2 具有在子級上授予的可升級（寫入）鎖定，并且正在等待父級的讀取鎖定。最后會話 3 具有針對父級的授權(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)+-------------+-------------+-------------------+---------------+-------------+
請注意，具有掛起鎖定狀態(tài)的唯一會話是會話 2（ALTER）。會話 1 和會話 3 （分別在 ALTER 之前和之后發(fā)布）都被授予了寫鎖。排序失敗的地方是在會話 1 上發(fā)生提交的時候。在考慮有序隊列時，人們會期望會話 2 獲得鎖定，事情就會繼續(xù)進行。但是，由于元數(shù)據(jù)鎖定系統(tǒng)的優(yōu)先級性質(zhì)，會話 3 具有鎖定，會話 2 仍然等待。
如果另一個寫入會話進入并啟動新事務并獲取針對父表的寫鎖定，則即使會話 3 完成，ALTER 仍將被阻止。
只要我保持一個對父表打開元數(shù)據(jù)鎖定的活動事務，子表上的 ALTER 將永遠不會完成。更糟糕的是，由于子表上的寫鎖定成功（但是完整語句正在等待獲取父讀鎖定），所以針對子表的所有傳入讀取請求都將被阻止！
另外，請考慮一下您通常如何對無法完成的語句進行故障排除。您查看已經(jīng)打開較長時間的事務（在進程列表和 InnoDB 狀態(tài)中）。但由于阻塞線程現(xiàn)在比 ALTER 線程更年輕，因此您將看到的最舊的事務/線程是 ALTER 。
這正是這種情況下發(fā)生的情況。在準備發(fā)布時，我們的客戶端正在運行 ALTER 語句并結(jié)合負載測試（一種非常好的做法！）以確保順利發(fā)布。問題是負載測試保持對父表打開一個活動的寫事務。這并不是說它只是一直在寫，而是有多個線程，一個總是活躍的。 這阻止了 ALTER 完成并阻止對相對靜態(tài)的子表的隨后的讀請求。
幸運的是，這個問題有一個解決方案（除了從設(shè)計模式中驅(qū)逐外鍵）。變量?max_write_lock_count[2]?可用于允許在寫入鎖定之后在讀取鎖定之前授予讀取鎖定連續(xù)寫鎖。默認情況下，此變量設(shè)置為 18446744073709551615，如果你對該表發(fā)出 10,000 次寫入/秒，那么你的讀將被鎖定 5800 萬年……
重慶分公司
重慶分公司
