問(wèn)題：Lock wait timeout exceeded; try restarting transaction
MySQL版本：5.6.44
意思是：InnoDB在鎖等待超時(shí)過(guò)期時(shí)報(bào)告此錯(cuò)誤汉操。等待時(shí)間過(guò)長(zhǎng)的語(yǔ)句被回滾（而不是整個(gè)事務(wù)）怪嫌。如果SQL語(yǔ)句需要等待其他事務(wù)完成的時(shí)間更長(zhǎng)航夺，則可以增加 innodb_lock_wait_timeout 配置選項(xiàng)的值；如果太多長(zhǎng)時(shí)間運(yùn)行的事務(wù)導(dǎo)致鎖定問(wèn)題并降低繁忙系統(tǒng)上的并發(fā)性，則可以減少該選項(xiàng)的值。
分析
鎖等待超時(shí)叭披，可能是出現(xiàn)了死鎖，也可能有事務(wù)長(zhǎng)時(shí)間未提交
解決辦法
- 如果是死鎖玩讳，MySQL提供了超時(shí)等待機(jī)制涩蜘，超時(shí)獲取不到資源即
rollback
四張重要的表
庫(kù)：information_schema
表：
innodb_trx：當(dāng)前運(yùn)行的所有事務(wù)
innodb_locks：當(dāng)前出現(xiàn)的鎖
innodb_lock_waits：鎖等待對(duì)應(yīng)的關(guān)系
processlist：當(dāng)前進(jìn)程信息
查看各表信息
desc information_schema.innodb_trx
desc information_schema.innodb_locks
desc information_schema.innodb_lock_waits
desc information_schema.processlist
innodb_trx 表
| 字段 | 釋義 |
|---|---|
| trx_id | 事務(wù)ID |
| trx_state | 事務(wù)狀態(tài) |
| trx_started | 事務(wù)開(kāi)始時(shí)間 |
| trx_requested_lock_id | innodb_locks.lock_id |
| trx_wait_started | 事務(wù)開(kāi)始等待的時(shí)間 |
| trx_weight | |
| trx_mysql_thread_id | 事務(wù)線程ID |
| trx_query | 具體SQL語(yǔ)句 |
| trx_operation_state | 事務(wù)當(dāng)前操作狀態(tài) |
| trx_tables_in_use | 事務(wù)中有多少個(gè)表被使用 |
| trx_tables_locked | 事務(wù)擁有多少個(gè)鎖 |
| trx_lock_structs | |
| trx_lock_memory_bytes | 事務(wù)鎖住的內(nèi)存大小（B） |
| trx_rows_locked | 事務(wù)鎖住的行數(shù) |
| trx_rows_modified | 事務(wù)更改的行數(shù) |
| trx_concurrency_tickets | 事務(wù)并發(fā)票數(shù) |
| trx_isolation_level | 事務(wù)隔離級(jí)別 |
| trx_unique_checks | 是否唯一性檢查 |
| trx_foreign_key_checks | 是否外鍵檢查 |
| trx_last_foreign_key_error | 最后的外鍵錯(cuò)誤 |
| trx_adaptive_hash_latched | |
| trx_adaptive_hash_timeout | |
| trx_is_read_only | 事務(wù)是否只讀 |
| trx_autocommit_non_locking |
innodb_locks 表
| 字段 | 釋義 |
|---|---|
| lock_id | 鎖ID |
| lock_trx_id | 擁有鎖的事務(wù)ID |
| lock_mode | 鎖模式 |
| lock_type | 鎖類型 |
| lock_table | 被鎖的表 |
| lock_index | 被鎖的索引 |
| lock_space | 被鎖的表空間號(hào) |
| lock_page | 被鎖的頁(yè)號(hào) |
| lock_rec | 被鎖的記錄號(hào) |
| lock_data | 被鎖的數(shù)據(jù) |
innodb_lock_waits 表
| 字段 | 釋義 |
|---|---|
| requesting_trx_id | 請(qǐng)求鎖的事務(wù)ID（等待鎖的ID） |
| requested_lock_id | 請(qǐng)求鎖的鎖ID |
| blocking_trx_id | 當(dāng)前擁有鎖的事務(wù)ID |
| blocking_lock_id | 當(dāng)前擁有鎖的鎖ID |
processlist 表
| 字段 | 釋義 |
|---|---|
| Id | 線程ID |
| User | 用戶 |
| Host | 主機(jī) |
| db | 數(shù)據(jù)庫(kù) |
| Command | 命令 |
| Time | 時(shí)間 |
| State | 狀態(tài) |
| Info | 信息 |
排查思路
模擬出現(xiàn)死鎖
準(zhǔn)備一張只有主鍵的表：t_test (id)
Navicat 新建查詢1
begin
update t_test set id = 30 where id = 3;
-- 執(zhí)行完 update 后锋边，暫時(shí)不 commit
commit;
Navicat 新建查詢2
begin
update t_test set id = 40 where id = 3;
-- 執(zhí)行完 update 后皱坛，暫時(shí)不 commit
commit;
檢查是否鎖表
show open tables where in_use > 0;
查詢當(dāng)前正在執(zhí)行的事務(wù)
select * from information_schema.innodb_trx
可以看到，此時(shí)的事務(wù)2豆巨，出現(xiàn)了 鎖等待
查詢當(dāng)前出現(xiàn)的鎖
select * from information_schema.innodb_locks
查詢鎖等待對(duì)應(yīng)的關(guān)系
查詢等待鎖的事務(wù)所執(zhí)行的SQL
SELECT
a.trx_id '事務(wù) id',
a.trx_mysql_thread_id '事務(wù)線程 id',
a.trx_query '事務(wù) SQL'
FROM
information_schema.innodb_locks b,
information_schema.innodb_trx a
WHERE
b.lock_trx_id = a.trx_id;
可以看到剩辟，對(duì)應(yīng)的 SQL 就是事務(wù)2的
最后，事務(wù)2 等待鎖超時(shí)報(bào)錯(cuò)：Lock wait timeout exceeded; try restarting transaction;
時(shí)間大概50s往扔， 鎖等待超時(shí)時(shí)間配置
show variables like 'innodb_lock_wait_timeout';
通過(guò)事務(wù)線程ID查找進(jìn)程信息
select * from information_schema.processlist where id = 388654
通過(guò)其中的 HOST 字段信息就可以定位到是哪個(gè)程序的事務(wù)贩猎。結(jié)合事務(wù)的 SQL 語(yǔ)句可以定位到業(yè)務(wù)代碼。
win10 查看端口信息
netstat -ano | findstr 59501
