問(wèn)題背景
錯(cuò)誤信息：SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
接口響應(yīng)時(shí)間超長(zhǎng)聚请，報(bào)警日志中出現(xiàn)Lock wait timeout exceeded; try restarting transaction的錯(cuò)誤
出現(xiàn)原因
mysql數(shù)據(jù)庫(kù)采用InnoDB模式，一旦數(shù)據(jù)庫(kù)鎖超過(guò)innodb_lock_wait_timeout參數(shù)設(shè)置的鎖等待的時(shí)間(默認(rèn)50s)就會(huì)報(bào)錯(cuò)。
> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
解決方法
1 通過(guò)語(yǔ)句修改參數(shù)
set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;
2 修改配置文件參數(shù)項(xiàng)目
# my.ini文件：
innodb_lock_wait_timeout = 100
注意：
修改參數(shù)需要慎重。全局更改，等待時(shí)間加長(zhǎng)，容易使等待事務(wù)增多導(dǎo)致堆積問(wèn)題。
推薦：
- 對(duì)于耗時(shí)任務(wù)宅广，進(jìn)行合理拆分，減少等待時(shí)間些举。
- 找到縮表的業(yè)務(wù)跟狱，對(duì)業(yè)務(wù)代碼進(jìn)行分析，優(yōu)化户魏。從根本解決問(wèn)題驶臊。
相關(guān)信息表
- innodb_trx ## 當(dāng)前運(yùn)行的所有事務(wù)
- innodb_locks ## 當(dāng)前出現(xiàn)的鎖
- innodb_lock_waits ## 鎖等待的對(duì)應(yīng)關(guān)系
--查看事務(wù)
select * from information_schema.INNODB_TRX;
--查看鎖
select * from information_schema.INNODB_LOCKS;
--查看鎖等待
select * from information_schema.INNODB_LOCK_WAITS;
INNODB_TRX 表列信息詳解：
trx_id：
唯一事務(wù)id號(hào)，只讀事務(wù)和非鎖事務(wù)是不會(huì)創(chuàng)建id的绪抛。
TRX_WEIGHT：
事務(wù)的高度资铡，代表修改的行數(shù)（不一定準(zhǔn)確）和被事務(wù)鎖住的行數(shù)。為了解決死鎖幢码，innodb會(huì)選擇一個(gè)高度最小的事務(wù)來(lái)當(dāng)做犧牲品進(jìn)行回滾笤休。已經(jīng)被更改的非交易型表的事務(wù)權(quán)重比其他事務(wù)高，即使改變的行和鎖住的行比其他事務(wù)低症副。
TRX_STATE：
事務(wù)的執(zhí)行狀態(tài)店雅，值一般分為：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
TRX_STARTED：
事務(wù)的開(kāi)始時(shí)間
TRX_REQUESTED_LOCK_ID:
如果trx_state是lockwait,顯示事務(wù)當(dāng)前等待鎖的id，不是則為空贞铣。想要獲取鎖的信息闹啦，根據(jù)該lock_id，以innodb_locks表中l(wèi)ock_id列匹配條件進(jìn)行查詢辕坝，獲取相關(guān)信息窍奋。
TRX_WAIT_STARTED：
如果trx_state是lockwait,該值代表事務(wù)開(kāi)始等待鎖的時(shí)間；否則為空。
TRX_MYSQL_THREAD_ID：
mysql線程id琳袄。想要獲取該線程的信息江场，根據(jù)該thread_id，以INFORMATION_SCHEMA.PROCESSLIST表的id列為匹配條件進(jìn)行查詢窖逗。
TRX_QUERY：
事務(wù)正在執(zhí)行的sql語(yǔ)句址否。
TRX_OPERATION_STATE：
事務(wù)當(dāng)前的操作狀態(tài)，沒(méi)有則為空碎紊。
TRX_TABLES_IN_USE：
事務(wù)在處理當(dāng)前sql語(yǔ)句使用innodb引擎表的數(shù)量佑附。
TRX_TABLES_LOCKED：
當(dāng)前sql語(yǔ)句有行鎖的innodb表的數(shù)量。（因?yàn)橹皇切墟i仗考，不是表鎖音同，表仍然可以被多個(gè)事務(wù)讀和寫）
TRX_LOCK_STRUCTS：
事務(wù)保留鎖的數(shù)量。
TRX_LOCK_MEMORY_BYTES：
在內(nèi)存中事務(wù)索結(jié)構(gòu)占得空間大小秃嗜。
TRX_ROWS_LOCKED：
事務(wù)行鎖最準(zhǔn)確的數(shù)量瘟斜。這個(gè)值可能包括對(duì)于事務(wù)在物理上存在，實(shí)際不可見(jiàn)的刪除標(biāo)記的行痪寻。
TRX_ROWS_MODIFIED：
事務(wù)修改和插入的行數(shù)
TRX_CONCURRENCY_TICKETS：
該值代表當(dāng)前事務(wù)在被清掉之前可以多少工作，由 innodb_concurrency_tickets系統(tǒng)變量值指定虽惭。
TRX_ISOLATION_LEVEL：
事務(wù)隔離等級(jí)橡类。
TRX_UNIQUE_CHECKS：
當(dāng)前事務(wù)唯一性檢查啟用還是禁用。當(dāng)批量數(shù)據(jù)導(dǎo)入時(shí)芽唇，這個(gè)參數(shù)是關(guān)閉的顾画。
TRX_FOREIGN_KEY_CHECKS：
當(dāng)前事務(wù)的外鍵堅(jiān)持是啟用還是禁用。當(dāng)批量數(shù)據(jù)導(dǎo)入時(shí)匆笤，這個(gè)參數(shù)是關(guān)閉的研侣。
TRX_LAST_FOREIGN_KEY_ERROR：
最新一個(gè)外鍵錯(cuò)誤信息，沒(méi)有則為空炮捧。
TRX_ADAPTIVE_HASH_LATCHED：
自適應(yīng)哈希索引是否被當(dāng)前事務(wù)阻塞庶诡。當(dāng)自適應(yīng)哈希索引查找系統(tǒng)分區(qū)，一個(gè)單獨(dú)的事務(wù)不會(huì)阻塞全部的自適應(yīng)hash索引咆课。自適應(yīng)hash索引分區(qū)通過(guò) innodb_adaptive_hash_index_parts參數(shù)控制末誓，默認(rèn)值為8。
TRX_ADAPTIVE_HASH_TIMEOUT：
是否為了自適應(yīng)hash索引立即放棄查詢鎖书蚪，或者通過(guò)調(diào)用mysql函數(shù)保留它喇澡。當(dāng)沒(méi)有自適應(yīng)hash索引沖突，該值為0并且語(yǔ)句保持鎖直到結(jié)束殊校。在沖突過(guò)程中晴玖，該值被計(jì)數(shù)為0，每句查詢完之后立即釋放門閂。當(dāng)自適應(yīng)hash索引查詢系統(tǒng)被分區(qū)（由 innodb_adaptive_hash_index_parts參數(shù)控制）呕屎，值保持為0让簿。
TRX_IS_READ_ONLY：
值為1表示事務(wù)是read only。
TRX_AUTOCOMMIT_NON_LOCKING：
值為1表示事務(wù)是一個(gè)select語(yǔ)句榨惰，該語(yǔ)句沒(méi)有使用for update或者shared mode鎖拜英，并且執(zhí)行開(kāi)啟了autocommit，因此事務(wù)只包含一個(gè)語(yǔ)句琅催。當(dāng)TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同時(shí)為1居凶，innodb通過(guò)降低事務(wù)開(kāi)銷和改變表數(shù)據(jù)庫(kù)來(lái)優(yōu)化事務(wù)。
> desc information_schema.INNODB_LOCKS;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |
| lock_trx_id | varchar(18) | NO | | | |
| lock_mode | varchar(32) | NO | | | |
| lock_type | varchar(32) | NO | | | |
| lock_table | varchar(1024) | NO | | | |
| lock_index | varchar(1024) | YES | | <null> | |
| lock_space | bigint(21) unsigned | YES | | <null> | |
| lock_page | bigint(21) unsigned | YES | | <null> | |
| lock_rec | bigint(21) unsigned | YES | | <null> | |
| lock_data | varchar(8192) | YES | | <null> | |
+-------------+---------------------+------+-----+---------+-------+
INNODB_LOCKS表列信息詳解：
LOCK_ID
一個(gè)唯一的鎖ID號(hào)藤抡，內(nèi)部為 InnoDB侠碧。
LOCK_TRX_ID
持有鎖的交易的ID
LOCK_MODE
如何請(qǐng)求鎖定。允許鎖定模式描述符 S缠黍，X弄兜， IS，IX瓷式， GAP替饿，AUTO_INC，和 UNKNOWN贸典。鎖定模式描述符可以組合使用以識(shí)別特定的鎖定模式视卢。
LOCK_TYPE
鎖的類型
LOCK_TABLE
已鎖定或包含鎖定記錄的表的名稱
LOCK_INDEX
索引的名稱，如果LOCK_TYPE是 RECORD; 否則NULL
LOCK_SPACE
鎖定記錄的表空間ID廊驼，如果 LOCK_TYPE是RECORD; 否則NULL
LOCK_PAGE
鎖定記錄的頁(yè)碼据过，如果 LOCK_TYPE是RECORD; 否則NULL。
LOCK_REC
頁(yè)面內(nèi)鎖定記錄的堆號(hào)妒挎，如果 LOCK_TYPE是RECORD; 否則NULL绳锅。
LOCK_DATA
與鎖相關(guān)的數(shù)據(jù)（如果有）。如果 LOCK_TYPE是RECORD酝掩，是鎖定的記錄的主鍵值鳞芙，否則NULL。此列包含鎖定行中主鍵列的值庸队，格式為有效的SQL字符串积蜻。如果沒(méi)有主鍵，LOCK_DATA則是唯一的InnoDB內(nèi)部行ID號(hào)彻消。如果對(duì)鍵值或范圍高于索引中的最大值的間隙鎖定竿拆，則LOCK_DATA 報(bào)告supremum pseudo-record。當(dāng)包含鎖定記錄的頁(yè)面不在緩沖池中時(shí)（如果在保持鎖定時(shí)將其分頁(yè)到磁盤）宾尚，InnoDB不從磁盤獲取頁(yè)面丙笋，以避免不必要的磁盤操作谢澈。相反， LOCK_DATA設(shè)置為 NULL御板。
> desc information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |
| requested_lock_id | varchar(81) | NO | | | |
| blocking_trx_id | varchar(18) | NO | | | |
| blocking_lock_id | varchar(81) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
INNODB_LOCK_WAITS表列信息詳解：
REQUESTING_TRX_ID
請(qǐng)求（阻止）事務(wù)的ID锥忿。
REQUESTED_LOCK_ID
事務(wù)正在等待的鎖的ID。
BLOCKING_TRX_ID
阻止事務(wù)的ID怠肋。
BLOCKING_LOCK_ID
由阻止另一個(gè)事務(wù)繼續(xù)進(jìn)行的事務(wù)所持有的鎖的ID
