今天在刪除的時(shí)候出現(xiàn)報(bào)錯(cuò)：ERROR:Lock wait timeout exceeded; try restarting transaction（把這個(gè)問(wèn)題記錄下來(lái)，防止以后再次遇到了方便處理）。

delete from gx_poor_family_standard_basic where id in (958994869806694400, 958994869810888704) 

網(wǎng)上一通查詢，解決方式如下：

1、先查看數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別：

// MySQL默認(rèn)的事務(wù)隔離級(jí)別就是REPEATABLE-READ

2、然后查看當(dāng)前數(shù)據(jù)庫(kù)的線程情況：

SHOW FULL PROCESSLIST;

沒(méi)有看到正在執(zhí)行的很慢SQL記錄線程，再去查看innodb的事務(wù)表INNODB_TRX，看下里面是否有正在鎖定的事務(wù)線程，看看ID是否在show full processlist里面的sleep線程中，如果是，就證明這個(gè)sleep的線程事務(wù)一直沒(méi)有commit或者rollback而是卡住了，我們需要手動(dòng)kill掉。

SELECT * FROM information_schema.INNODB_TRX; 

3、發(fā)現(xiàn)有id為616694的sql，需要手動(dòng)kill掉

KILL 616694;

kill之后，再去執(zhí)行上面的delete語(yǔ)句，就可以執(zhí)行成功了。

注意：MySQL是自動(dòng)提交事務(wù)的（即：autocommit=1），可以使用 show variables like 'autocommit' 或者 select @@autocommit 查看當(dāng)前數(shù)據(jù)庫(kù)是否為自動(dòng)提交事務(wù)；若autocommit的值不是1還可以使用set global autocommit = 1 將自動(dòng)提交設(shè)置為開啟。

問(wèn)題解決

一、問(wèn)題

Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction;

image

現(xiàn)象：接口響應(yīng)時(shí)間超長(zhǎng)，耗時(shí)幾十秒才返回錯(cuò)誤提示，后臺(tái)日志中出現(xiàn)Lock wait timeout exceeded; try restarting transaction的錯(cuò)誤

二、原因分析

使用InnoDB表類型的時(shí)候，鎖等待超過(guò)了innodb_lock_wait_timeout（默認(rèn)是50s）設(shè)置的時(shí)間,所以報(bào)錯(cuò)

三、可能出現(xiàn)場(chǎng)景

1、在同一事務(wù)內(nèi)先后對(duì)同一條數(shù)據(jù)進(jìn)行插入和更新操作

2、多臺(tái)服務(wù)器操作同一數(shù)據(jù)庫(kù)

3、瞬時(shí)出現(xiàn)高并發(fā)現(xiàn)象，spring事務(wù)造成數(shù)據(jù)庫(kù)死鎖，后續(xù)操作超時(shí)拋出異常

4、事務(wù)A對(duì)記錄C進(jìn)行更新/刪除操作的請(qǐng)求未commit時(shí)，事務(wù)B也對(duì)記錄C進(jìn)行更新/刪除操作。此時(shí)，B會(huì)等A提交事務(wù)，釋放行鎖。當(dāng)?shù)却龝r(shí)間超過(guò)innodb_lock_wait_timeout設(shè)置值時(shí)，會(huì)產(chǎn)生“LOCK WAIT”事務(wù)。

四、解決方案

1、【治標(biāo)方法】innodb_lock_wait_timeout 鎖定等待時(shí)間改大

my.ini文件：

innodb_lock_wait_timeout = 50

修改為

innodb_lock_wait_timeout = 500

缺點(diǎn)：全局更改，影響也是全局的，等待時(shí)間加長(zhǎng)，容易使等待事務(wù)增多導(dǎo)致堆積問(wèn)題。

2、【治標(biāo)方法】事務(wù)信息查詢

SELECT * FROM information_schema.innodb_trx

查到一個(gè)一直沒(méi)有提交的只讀事務(wù)（trx_state=”LOCK WAIT”），找到對(duì)應(yīng)線程，執(zhí)行：

kill 線程ID(trx_mysql_thread_id)

3、【治標(biāo)方法】如果殺掉線程依然不能解決，可以查找執(zhí)行線程耗時(shí)比較久的任務(wù)，kill掉

SELECT * from information_schema.PROCESSLIST WHERE Time > 1000 AND USER = 'xxx' ORDER BY TIME desc;

kill 線程ID

4、【根本解決方法！】找到鎖表的事務(wù)，分析鎖表原因，進(jìn)行優(yōu)化。

實(shí)例：司機(jī)APP進(jìn)行運(yùn)單簽收，需要對(duì)et_waybill_info表某些記錄進(jìn)行更新操作。一直處于鎖等待狀態(tài)，直到超時(shí)報(bào)錯(cuò)。

經(jīng)排查，發(fā)現(xiàn)：系統(tǒng)定時(shí)器定時(shí)執(zhí)行任務(wù)，將所有未標(biāo)識(shí)亮的已裝車或簽收的運(yùn)單，按批次處理，如果運(yùn)單裝車了但長(zhǎng)時(shí)間未上傳GPS、溫濕度等信息，會(huì)一直被定時(shí)器處理。數(shù)據(jù)量越積越大，隊(duì)列長(zhǎng)時(shí)間等待，對(duì)et_waybill_info表鎖住沒(méi)有釋放，致使簽收要操作et_waybill_info表無(wú)法拿到鎖，進(jìn)行數(shù)據(jù)操作。

臨時(shí)解決方案：停掉定時(shí)器任務(wù)

根本解決方案：優(yōu)化定時(shí)器

五、預(yù)防措施

1、開始事務(wù)（@transtion）指定超時(shí)時(shí) 間

例：@Transactional( rollbackFor = Exception.class , isolation = Isolation.REPEATABLE_READ, timeout = 30)

2、事務(wù)中存在批量修改、刪除語(yǔ)句的時(shí)候，where條件盡量加索引

3、事務(wù)中存在批量修改、刪除語(yǔ)句的時(shí)候，盡可能減少事務(wù)的執(zhí)行時(shí)間

4、減少并發(fā)線程數(shù)

六、相關(guān)信息

1、innodb_lock_wait_timeout和lock_wait_timeout

innodb_lock_wait_timeout：InnoDB事務(wù)等待一個(gè)行級(jí)鎖的時(shí)間最長(zhǎng)時(shí)間（單位是秒），超過(guò)這個(gè)時(shí)間就會(huì)放棄。默認(rèn)值是50秒

lock_wait_timeout：獲取元數(shù)據(jù)鎖的超時(shí)時(shí)間。這個(gè)適合用于除了系統(tǒng)表之外的所有表（mysql庫(kù)之外）。

區(qū)別于innodb_lock_wait_timeout是針對(duì)dml操作的行級(jí)鎖的等待時(shí)間 ，而lock_wait_timeout是數(shù)據(jù)結(jié)構(gòu)ddl操作的鎖的等待時(shí)間

2、事務(wù)相關(guān)表

INNODB_TRX 當(dāng)前運(yùn)行的所有事務(wù)

image

INNODB_LOCKS 當(dāng)前出現(xiàn)的鎖，查看正在鎖的事務(wù)

image

INNODB_LOCK_WAITS 鎖等待的對(duì)應(yīng)關(guān)系，查看等待鎖的事務(wù)

image

3、information_schema和performance_schema

information_schema：對(duì)數(shù)據(jù)庫(kù)元數(shù)據(jù)的抽象分析，由此提供了SQL語(yǔ)句方式來(lái)查詢數(shù)據(jù)庫(kù)運(yùn)行時(shí)狀態(tài)，每次對(duì)infomation_schema的查詢都產(chǎn)生對(duì)metadata的互斥訪問(wèn)，影響其他數(shù)據(jù)庫(kù)的訪問(wèn)性能。這張數(shù)據(jù)表保存了MySQL服務(wù)器所有數(shù)據(jù)庫(kù)的信息。如數(shù)據(jù)庫(kù)名，數(shù)據(jù)庫(kù)的表，表欄的數(shù)據(jù)類型與訪問(wèn)權(quán)限等。

performance_schema：內(nèi)存型數(shù)據(jù)庫(kù)，使用performance_schema存儲(chǔ)引擎，通過(guò)事件機(jī)制將mysql服務(wù)的運(yùn)行時(shí)狀態(tài)采集并存儲(chǔ)在performance_schema數(shù)據(jù)庫(kù)。用于監(jiān)控MySQL server在一個(gè)較低級(jí)別的運(yùn)行過(guò)程中的資源消耗、資源等待等情況。

七、總結(jié)

1、當(dāng)看到mysql報(bào)錯(cuò)時(shí)，可以根據(jù)報(bào)錯(cuò)的信息及錯(cuò)誤號(hào)去分析報(bào)錯(cuò)原因，然后冷靜分析，透過(guò)現(xiàn)象看本質(zhì)，從根本上解決問(wèn)題。少用治標(biāo)不治本的方案，還可能會(huì)帶來(lái)其他問(wèn)題。

2、了解了mysql里幾張事務(wù)相關(guān)表

3、初識(shí)information_schema和performance_schema