這是一個(gè)典型的 MySQL 鎖等待超時(shí) 錯(cuò)誤。下面為您詳細(xì)解釋原因和解決方案。

核心原因

簡單來說：有一個(gè)事務(wù)正在長時(shí)間鎖定這條SQL要操作的數(shù)據(jù)行，導(dǎo)致當(dāng)前事務(wù)一直等待鎖釋放，最終超過了MySQL的最大等待時(shí)間（innodb_lock_wait_timeout，默認(rèn)50秒），從而失敗回滾。

詳細(xì)原因分析

1. 鎖競(jìng)爭(zhēng)：SQL語句是一個(gè)復(fù)雜的操作。
2. 阻塞事務(wù)：在這個(gè)事務(wù)開始之前，很可能已經(jīng)存在另一個(gè)未提交的事務(wù)（比如一個(gè)長時(shí)間的查詢、更新或插入操作），這個(gè)“前輩”事務(wù)已經(jīng)鎖定了您SQL語句中想要更新的部分或全部數(shù)據(jù)行。
3. 等待與超時(shí)：這個(gè)事務(wù)（報(bào)錯(cuò)的事務(wù)）因?yàn)槟貌坏芥i，只能進(jìn)入等待隊(duì)列。在MySQL默認(rèn)的50秒內(nèi)，如果那個(gè)“阻塞事務(wù)”一直沒有提交或回滾，您的這個(gè)事務(wù)就會(huì)因超時(shí)而失敗，拋出 CannotAcquireLockException。

解決方案

1. 立即處理（治標(biāo)）

• 重啟事務(wù)：正如錯(cuò)誤信息提示的 try restarting transaction，最簡單的方法就是讓您的應(yīng)用程序自動(dòng)或手動(dòng)重試這個(gè)操作。確保重試邏輯有次數(shù)限制和延遲。

• 找出并終止阻塞進(jìn)程：

  1. 連接到您的MySQL數(shù)據(jù)庫。

  2. 執(zhí)行以下SQL，查看當(dāng)前正在運(yùn)行的事務(wù)和鎖信息：

     -- 查看當(dāng)前所有事務(wù)（重點(diǎn)關(guān)注trx_state為'LOCK WAIT'和'RUNNING'的）
     SELECT * FROM information_schema.INNODB_TRX;
     
     -- 或者更詳細(xì)的鎖信息查詢
     SELECT 
         r.trx_id waiting_trx_id,
         r.trx_mysql_thread_id waiting_thread,
         r.trx_query waiting_query,
         b.trx_id blocking_trx_id,
         b.trx_mysql_thread_id blocking_thread,
         b.trx_query blocking_query
     FROM information_schema.INNODB_LOCK_WAITS w
     INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
     INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;
     

  3. 從結(jié)果中找到 blocking_trx_id（阻塞者的事務(wù)ID）或 blocking_thread（阻塞者的連接ID）。

  4. 強(qiáng)制殺死阻塞的數(shù)據(jù)庫連接（請(qǐng)謹(jǐn)慎操作，確認(rèn)不影響業(yè)務(wù)）：

     KILL [blocking_thread_id];
     

     殺死后，您的等待事務(wù)應(yīng)該就能繼續(xù)執(zhí)行了。

2. 長期優(yōu)化（治本）

• 優(yōu)化事務(wù)設(shè)計(jì)：
  • 縮小事務(wù)范圍：確保事務(wù)盡可能短小精悍。不要在事務(wù)中包含不必要的業(yè)務(wù)邏輯、遠(yuǎn)程調(diào)用或文件操作。執(zhí)行完數(shù)據(jù)庫操作后立即提交。
  • 避免長事務(wù)：這是最根本的原因。檢查代碼中是否有事務(wù)方法執(zhí)行時(shí)間過長。
• 優(yōu)化SQL和索引：
  • 您的SQL使用了 IN (?) 和多個(gè) JOIN。確保 tcqp.id、連接條件上的字段（如 tcqpd.contract_process_id, tcaf.contract_process_id）都有合適的索引。沒有索引會(huì)導(dǎo)致鎖升級(jí)（例如鎖表而不是鎖行），大大增加鎖沖突的概率。
  • 使用 EXPLAIN 分析您的SQL，看是否使用了正確的索引。
• 業(yè)務(wù)邏輯分離：
  • 考慮將這個(gè)大而復(fù)雜的更新操作拆分成多個(gè)較小、較快的事務(wù)。例如，可以先更新主表，再更新子表。
• 調(diào)整數(shù)據(jù)庫參數(shù)（需DBA評(píng)估）：
  • innodb_lock_wait_timeout：可以適當(dāng)調(diào)大這個(gè)參數(shù)（例如調(diào)到120秒），但這只是延長了等待時(shí)間，并未解決根本問題。
  • innodb_rollback_on_timeout：默認(rèn)是OFF，超時(shí)后只回滾最后一條語句?？梢栽O(shè)置為ON，讓整個(gè)事務(wù)回滾，但這可能會(huì)影響業(yè)務(wù)邏輯。
• 使用不同的隔離級(jí)別：
  • 默認(rèn)的 REPEATABLE READ 隔離級(jí)別更容易產(chǎn)生鎖。如果業(yè)務(wù)允許，可以考慮在特定查詢中使用 READ COMMITTED 級(jí)別，但這需要仔細(xì)評(píng)估一致性要求。

總結(jié)

這個(gè)錯(cuò)誤的根本原因是 數(shù)據(jù)庫中存在“長事務(wù)”阻塞了您的正常操作。

建議處理流程：

1. 緊急處理：通過 information_schema 找到并 KILL 阻塞進(jìn)程。
2. 分析原因：審查代碼，找到是哪個(gè)業(yè)務(wù)操作導(dǎo)致了長事務(wù)。
3. 根本解決：優(yōu)化事務(wù)代碼和SQL，確保所有數(shù)據(jù)庫操作快速完成并及時(shí)提交。

SELECT * FROM information_schema.INNODB_TRX; 這個(gè)sql執(zhí)行完的返回的數(shù)據(jù)怎么看？

執(zhí)行 SELECT * FROM information_schema.INNODB_TRX; 后，您會(huì)看到當(dāng)前所有InnoDB事務(wù)的詳細(xì)信息。以下是關(guān)鍵字段的解釋和如何分析：

關(guān)鍵字段解釋

如何分析結(jié)果

1. 識(shí)別問題事務(wù)

-- 按事務(wù)開始時(shí)間排序，查看運(yùn)行時(shí)間最長的事務(wù)
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMEDIFF(NOW(), trx_started) as running_time,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

2. 重點(diǎn)關(guān)注的情況

鎖等待事務(wù)(trx_state = 'LOCK WAIT')

-- 查找正在等待鎖的事務(wù)
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'LOCK WAIT';

• 這類事務(wù)就是您的報(bào)錯(cuò)事務(wù)
• 查看 trx_query 了解它在等待什么
• 記錄 trx_mysql_thread_id 以備需要時(shí)終止

長時(shí)間運(yùn)行事務(wù)(RUNNING狀態(tài)但運(yùn)行時(shí)間很長)

-- 查找運(yùn)行超過30秒的事務(wù)
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'RUNNING' 
  AND trx_started < NOW() - INTERVAL 30 SECOND;

3. 實(shí)際分析示例

假設(shè)查詢結(jié)果如下：

分析結(jié)論：

• 線程ID 100 的事務(wù)從 10:00 開始運(yùn)行，已經(jīng)鎖定了5000行，很可能是阻塞事務(wù)
• 線程ID 101 的事務(wù)在等待鎖，這就是您的報(bào)錯(cuò)事務(wù)

4. 進(jìn)一步定位阻塞關(guān)系

-- 更精確地查找誰阻塞了誰
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id, 
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

5. 處理建議

1. 如果是測(cè)試環(huán)境：直接終止阻塞事務(wù)

   KILL 100;  -- 終止線程ID為100的連接
   

2. 如果是生產(chǎn)環(huán)境：

   • 聯(lián)系相關(guān)業(yè)務(wù)負(fù)責(zé)人確認(rèn)該長時(shí)間運(yùn)行的事務(wù)是否可以終止
   • 分析 trx_query 中的SQL，優(yōu)化其性能
   • 考慮在業(yè)務(wù)低峰期執(zhí)行相關(guān)操作

通過這種方法，您就能準(zhǔn)確識(shí)別出導(dǎo)致鎖等待超時(shí)的根本原因了。

阻塞事務(wù) 可能產(chǎn)生的原因

阻塞事務(wù)的產(chǎn)生原因多種多樣，以下是主要的分類和具體原因：

1. 事務(wù)設(shè)計(jì)問題

長事務(wù)

-- 典型的長事務(wù)模式
BEGIN;
-- 執(zhí)行復(fù)雜的業(yè)務(wù)邏輯
UPDATE large_table SET ... WHERE ...; -- 耗時(shí)操作
-- 中間可能包含業(yè)務(wù)邏輯、外部API調(diào)用等
COMMIT; -- 很久之后才提交

特征：事務(wù)開始和提交時(shí)間間隔很長

未提交的事務(wù)

// 代碼中忘記提交或回滾
@Transactional
public void processData() {
    // 執(zhí)行更新操作
    updateTableA(...);
    // 如果這里發(fā)生異常，事務(wù)可能一直掛起
    if (someCondition) {
        return; // 忘記提交或回滾
    }
    // ... 其他操作
}

2. SQL性能問題

缺乏合適的索引

-- 沒有索引的更新操作
UPDATE tb_contract_quantities_process 
SET del_flag = 1 
WHERE contract_name LIKE '%某合同%';  -- 全表掃描，鎖住大量行

-- 有索引的高效更新
UPDATE tb_contract_quantities_process 
SET del_flag = 1 
WHERE id IN (1, 2, 3);  -- 使用主鍵索引，只鎖特定行

全表掃描操作

-- 導(dǎo)致鎖表的操作
UPDATE table_a SET status = 1 WHERE unindexed_column = 'value';
DELETE FROM large_table WHERE create_time < '2023-01-01';

3. 鎖機(jī)制相關(guān)

鎖升級(jí)

• 行鎖升級(jí)為表鎖：當(dāng)一條SQL需要鎖定大量數(shù)據(jù)行時(shí)，InnoDB可能將鎖升級(jí)為表鎖
• 間隙鎖（Gap Lock）：在REPEATABLE READ隔離級(jí)別下，范圍查詢會(huì)鎖定不存在的記錄區(qū)間

死鎖循環(huán)

-- 事務(wù)A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 事務(wù)B (同時(shí)執(zhí)行)
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

4. 應(yīng)用架構(gòu)問題

同步批量操作

// 在事務(wù)中處理大量數(shù)據(jù)
@Transactional
public void batchProcessContracts(List<Long> contractIds) {
    for (Long id : contractIds) {  // 循環(huán)處理，事務(wù)時(shí)間很長
        updateContractStatus(id);
        insertProcessLog(id);
        // ... 其他操作
    }
}

嵌套事務(wù)問題

@Transactional
public void mainProcess() {
    // 主事務(wù)開始
    updateMainTable();
    
    // 調(diào)用另一個(gè)事務(wù)方法
    subProcess();  // 如果subProcess有@Transactional(propagation=REQUIRES_NEW)
    
    // 主事務(wù)繼續(xù)...
}

5. 業(yè)務(wù)邏輯缺陷

用戶交互式事務(wù)

@Transactional
public void approveContract(Long contractId) {
    // 開始事務(wù)
    updateContractStatus(contractId, "APPROVING");
    // 等待用戶確認(rèn)（事務(wù)一直打開?。?
    waitForUserConfirmation();  // 錯(cuò)誤做法！
    updateContractStatus(contractId, "APPROVED");
}

循環(huán)依賴更新

-- 事務(wù)A
UPDATE table_a SET status = (SELECT count FROM table_b WHERE ...);
-- 事務(wù)B  
UPDATE table_b SET count = (SELECT status FROM table_a WHERE ...);

6. 數(shù)據(jù)庫配置問題

不合理的隔離級(jí)別

• READ UNCOMMITTED：臟讀風(fēng)險(xiǎn)
• REPEATABLE READ：容易產(chǎn)生間隙鎖（MySQL默認(rèn)）
• SERIALIZABLE：嚴(yán)格的鎖機(jī)制，性能差

連接池配置不當(dāng)

# 連接池配置問題
spring.datasource.hikari.maximum-pool-size=200  # 連接數(shù)過多，競(jìng)爭(zhēng)激烈
spring.datasource.hikari.connection-timeout=30000  # 連接超時(shí)時(shí)間過長

7. 監(jiān)控和診斷方法

識(shí)別阻塞事務(wù)的SQL

-- 查看當(dāng)前執(zhí)行的所有SQL
SHOW PROCESSLIST;

-- 查看詳細(xì)的鎖信息
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_query blocking_query,
    b.trx_started blocking_started,
    TIMEDIFF(NOW(), b.trx_started) blocking_duration
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

8. 預(yù)防和解決方案

優(yōu)化事務(wù)設(shè)計(jì)

// 正確的做法：短事務(wù)
public void processInShortTransactions(List<Long> ids) {
    for (Long id : ids) {
        transactionTemplate.execute(status -> {
            // 每個(gè)ID在獨(dú)立事務(wù)中處理
            updateContract(id);
            insertLog(id);
            return null;
        });
    }
}

添加合適的索引

-- 為查詢和更新條件添加索引
CREATE INDEX idx_contract_process ON tb_contract_quantities_process(id);
CREATE INDEX idx_process_details ON tb_contract_quantities_process_details(contract_process_id);
CREATE INDEX idx_arrival_file ON tb_contract_arrival_file(contract_process_id);

使用樂觀鎖

// 使用版本號(hào)避免悲觀鎖
@Transactional
public boolean updateWithOptimisticLock(Long id, String newValue) {
    Entity entity = entityDao.findById(id);
    int version = entity.getVersion();
    
    int affected = entityDao.updateWithVersion(id, newValue, version, version + 1);
    return affected > 0;  // 如果失敗可以重試
}

通過分析這些可能的原因，您可以系統(tǒng)地排查和解決阻塞事務(wù)問題。

總結(jié)

到此這篇關(guān)于MySQL鎖等待超時(shí)錯(cuò)誤詳細(xì)解釋原因和解決方案的文章就介紹到這了,更多相關(guān)MySQL鎖等待超時(shí)錯(cuò)誤內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論