首页 › MySQL论坛 › MySQL锁等待超时错误详细解释原因和解决方案

蟹蟹有你 發表於 2025-12-22 11:47:52

MySQL锁等待超时错误详细解释原因和解决方案

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">核心原因</a></li><li><a href="#_label1">详细原因分析</a></li><li><a href="#_label2">解决方案</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_0">1. 立即处理（治标）</a></li><li><a href="#_lab2_2_1">2. 长期优化（治本）</a></li></ul><li><a href="#_label3">总结</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">关键字段解释</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">如何分析结果</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_2">1. 识别问题事务</a></li><li><a href="#_lab2_5_3">2. 重点关注的情况</a></li><ul class="third_class_ul"><li><a href="#_label3_5_3_0">锁等待事务(trx_state = &#39;LOCK WAIT&#39;)</a></li><li><a href="#_label3_5_3_1">长时间运行事务(RUNNING状态但运行时间很长)</a></li></ul><li><a href="#_lab2_5_4">3. 实际分析示例</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_5">4. 进一步定位阻塞关系</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_6">5. 处理建议</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label6">阻塞事务 可能产生的原因</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_7">1. 事务设计问题</a></li><ul class="third_class_ul"><li><a href="#_label3_6_7_2">长事务</a></li><li><a href="#_label3_6_7_3">未提交的事务</a></li></ul><li><a href="#_lab2_6_8">2. SQL性能问题</a></li><ul class="third_class_ul"><li><a href="#_label3_6_8_4">缺乏合适的索引</a></li><li><a href="#_label3_6_8_5">全表扫描操作</a></li></ul><li><a href="#_lab2_6_9">3. 锁机制相关</a></li><ul class="third_class_ul"><li><a href="#_label3_6_9_6">锁升级</a></li><li><a href="#_label3_6_9_7">死锁循环</a></li></ul><li><a href="#_lab2_6_10">4. 应用架构问题</a></li><ul class="third_class_ul"><li><a href="#_label3_6_10_8">同步批量操作</a></li><li><a href="#_label3_6_10_9">嵌套事务问题</a></li></ul><li><a href="#_lab2_6_11">5. 业务逻辑缺陷</a></li><ul class="third_class_ul"><li><a href="#_label3_6_11_10">用户交互式事务</a></li><li><a href="#_label3_6_11_11">循环依赖更新</a></li></ul><li><a href="#_lab2_6_12">6. 数据库配置问题</a></li><ul class="third_class_ul"><li><a href="#_label3_6_12_12">不合理的隔离级别</a></li><li><a href="#_label3_6_12_13">连接池配置不当</a></li></ul><li><a href="#_lab2_6_13">7. 监控和诊断方法</a></li><ul class="third_class_ul"><li><a href="#_label3_6_13_14">识别阻塞事务的SQL</a></li></ul><li><a href="#_lab2_6_14">8. 预防和解决方案</a></li><ul class="third_class_ul"><li><a href="#_label3_6_14_15">优化事务设计</a></li><li><a href="#_label3_6_14_16">添加合适的索引</a></li><li><a href="#_label3_6_14_17">使用乐观锁</a></li></ul></ul><li><a href="#_label7">总结</a></li><ul class="second_class_ul"></ul></ul></div><p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122211450554.png" /></p>
<p>这是一个典型的 <strong>MySQL 锁等待超时</strong> 错误。下面为您详细解释原因和解决方案。</p>
<p class="maodian"><a name="_label0"></a></p><h2>核心原因</h2>
<p>简单来说：<strong>有一个事务正在长时间锁定这条SQL要操作的数据行，导致当前事务一直等待锁释放，最终超过了MySQL的最大等待时间（innodb_lock_wait_timeout，默认50秒），从而失败回滚。</strong></p>
<p class="maodian"><a name="_label1"></a></p><h2>详细原因分析</h2>
<ol><li><strong>锁竞争</strong>：SQL语句是一个复杂的操作。</li><li><strong>阻塞事务</strong>：在这个事务开始之前，很可能已经存在另一个<strong>未提交的事务</strong>（比如一个长时间的查询、更新或插入操作），这个&ldquo;前辈&rdquo;事务已经锁定了您SQL语句中想要更新的部分或全部数据行。</li><li><strong>等待与超时</strong>：这个事务（报错的事务）因为拿不到锁，只能进入等待队列。在MySQL默认的50秒内，如果那个&ldquo;阻塞事务&rdquo;一直没有提交或回滚，您的这个事务就会因超时而失败，抛出 <code>CannotAcquireLockException</code>。</li></ol>
<p class="maodian"><a name="_label2"></a></p><h2>解决方案</h2>
<p class="maodian"><a name="_lab2_2_0"></a></p><h3>1. 立即处理（治标）</h3>
<ul><li><p><strong>重启事务</strong>：正如错误信息提示的 <code>try restarting transaction</code>，最简单的方法就是让您的应用程序自动或手动重试这个操作。确保重试逻辑有次数限制和延迟。</p></li><li><p><strong>找出并终止阻塞进程</strong>：</p>
<ol><li><p>连接到您的MySQL数据库。</p></li><li><p>执行以下SQL，查看当前正在运行的事务和锁信息：</p>
<div class="jb51code"><pre class="brush:sql;">-- 查看当前所有事务（重点关注trx_state为'LOCK WAIT'和'RUNNING'的）
SELECT * FROM information_schema.INNODB_TRX;

-- 或者更详细的锁信息查询
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;
</pre></div></li><li><p>从结果中找到 <code>blocking_trx_id</code>（阻塞者的事务ID）或 <code>blocking_thread</code>（阻塞者的连接ID）。</p></li><li><p>强制杀死阻塞的数据库连接（请谨慎操作，确认不影响业务）：</p>
<div class="jb51code"><pre class="brush:sql;">KILL ;
</pre></div>
<p>杀死后，您的等待事务应该就能继续执行了。</p></li></ol></li></ul>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>2. 长期优化（治本）</h3>
<ul><li><strong>优化事务设计</strong>：
<ul><li><strong>缩小事务范围</strong>：确保事务尽可能短小精悍。不要在事务中包含不必要的业务逻辑、远程调用或文件操作。执行完数据库操作后立即提交。</li><li><strong>避免长事务</strong>：这是最根本的原因。检查代码中是否有事务方法执行时间过长。</li></ul></li><li><strong>优化SQL和索引</strong>：<ul><li>您的SQL使用了 <code>IN (?)</code> 和多个 <code>JOIN</code>。确保 <code>tcqp.id</code>、连接条件上的字段（如 <code>tcqpd.contract_process_id</code>, <code>tcaf.contract_process_id</code>）都有合适的索引。没有索引会导致锁升级（例如锁表而不是锁行），大大增加锁冲突的概率。</li><li>使用 <code>EXPLAIN</code> 分析您的SQL，看是否使用了正确的索引。</li></ul></li><li><strong>业务逻辑分离</strong>：<ul><li>考虑将这个大而复杂的更新操作拆分成多个较小、较快的事务。例如，可以先更新主表，再更新子表。</li></ul></li><li><strong>调整数据库参数（需DBA评估）</strong>：<ul><li><strong>innodb_lock_wait_timeout</strong>：可以适当调大这个参数（例如调到120秒），但这只是延长了等待时间，并未解决根本问题。</li><li><strong>innodb_rollback_on_timeout</strong>：默认是OFF，超时后只回滚最后一条语句。可以设置为ON，让整个事务回滚，但这可能会影响业务逻辑。</li></ul></li><li><strong>使用不同的隔离级别</strong>：<ul><li>默认的 <code>REPEATABLE READ</code> 隔离级别更容易产生锁。如果业务允许，可以考虑在特定查询中使用 <code>READ COMMITTED</code> 级别，但这需要仔细评估一致性要求。</li></ul></li></ul>
<p class="maodian"><a name="_label3"></a></p><p class="maodian"><a name="_label7"></a></p><h2>总结</h2>
<p>这个错误的根本原因是 <strong>数据库中存在&ldquo;长事务&rdquo;阻塞了您的正常操作</strong>。</p>
<p><strong>建议处理流程：</strong></p>
<ol><li><strong>紧急处理</strong>：通过 <code>information_schema</code> 找到并 <code>KILL</code> 阻塞进程。</li><li><strong>分析原因</strong>：审查代码，找到是哪个业务操作导致了长事务。</li><li><strong>根本解决</strong>：优化事务代码和SQL，确保所有数据库操作快速完成并及时提交。</li></ol>
<p>SELECT * FROM information_schema.INNODB_TRX; 这个sql执行完的返回的数据怎么看？</p>
<p>执行 <code>SELECT * FROM information_schema.INNODB_TRX;</code> 后，您会看到当前所有InnoDB事务的详细信息。以下是关键字段的解释和如何分析：</p>
<p class="maodian"><a name="_label4"></a></p><h2>关键字段解释</h2>
<table><thead><tr><th>字段</th><th>说明</th><th>重点关注</th></tr></thead><tbody><tr><td><code>trx_id</code></td><td>InnoDB内部事务ID</td><td>用于识别特定事务</td></tr><tr><td><code>trx_state</code></td><td><strong>事务状态</strong></td><td><strong>LOCK WAIT(锁等待中), RUNNING(运行中), ROLLING BACK(回滚中)</strong></td></tr><tr><td><code>trx_started</code></td><td>事务开始时间</td><td>判断事务运行了多久</td></tr><tr><td><code>trx_requested_lock_id</code></td><td>正在等待的锁ID</td><td>仅在<code>trx_state=&#39;LOCK WAIT&#39;</code>时有值</td></tr><tr><td><code>trx_wait_started</code></td><td>开始等待的时间</td><td>判断等待了多久</td></tr><tr><td><code>trx_weight</code></td><td>事务权重</td><td>值越大越可能被回滚</td></tr><tr><td><code>trx_mysql_thread_id</code></td><td><strong>MySQL连接线程ID</strong></td><td><strong>用于KILL命令</strong></td></tr><tr><td><code>trx_query</code></td><td><strong>当前正在执行的SQL</strong></td><td>查看事务在做什么</td></tr><tr><td><code>trx_operation_state</code></td><td>当前操作状态</td><td></td></tr><tr><td><code>trx_tables_in_use</code></td><td>涉及的表数量</td><td></td></tr><tr><td><code>trx_tables_locked</code></td><td>被锁定的表数量</td><td></td></tr><tr><td><code>trx_lock_structs</code></td><td>锁结构数量</td><td></td></tr><tr><td><code>trx_lock_memory_bytes</code></td><td>锁内存占用</td><td></td></tr><tr><td><code>trx_rows_locked</code></td><td><strong>被锁定的行数</strong></td><td>值过大可能是问题</td></tr><tr><td><code>trx_rows_modified</code></td><td><strong>修改的行数</strong></td><td>值过大可能是长事务</td></tr></tbody></table>
<p class="maodian"><a name="_label5"></a></p><h2>如何分析结果</h2>
<p class="maodian"><a name="_lab2_5_2"></a></p><h3>1. 识别问题事务</h3>
<div class="jb51code"><pre class="brush:sql;">-- 按事务开始时间排序，查看运行时间最长的事务
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;
</pre></div>
<p class="maodian"><a name="_lab2_5_3"></a></p><h3>2. 重点关注的情况</h3>
<p class="maodian"><a name="_label3_5_3_0"></a></p><h4>锁等待事务(trx_state = &#39;LOCK WAIT&#39;)</h4>
<div class="jb51code"><pre class="brush:sql;">-- 查找正在等待锁的事务
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT';
</pre></div>
<ul><li>这类事务就是您的报错事务</li><li>查看 <code>trx_query</code> 了解它在等待什么</li><li>记录 <code>trx_mysql_thread_id</code> 以备需要时终止</li></ul>
<p class="maodian"><a name="_label3_5_3_1"></a></p><h4>长时间运行事务(RUNNING状态但运行时间很长)</h4>
<div class="jb51code"><pre class="brush:sql;">-- 查找运行超过30秒的事务
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING'
AND trx_started &lt; NOW() - INTERVAL 30 SECOND;
</pre></div>
<p class="maodian"><a name="_lab2_5_4"></a></p><h3>3. 实际分析示例</h3>
<p>假设查询结果如下：</p>
<table><thead><tr><th>trx_id</th><th>trx_state</th><th>trx_started</th><th>trx_mysql_thread_id</th><th>trx_query</th><th>trx_rows_locked</th></tr></thead><tbody><tr><td>12345</td><td><strong>RUNNING</strong></td><td>2024-01-01 10:00:00</td><td>100</td><td><code>UPDATE table SET ...</code></td><td>5000</td></tr><tr><td>67890</td><td><strong>LOCK WAIT</strong></td><td>2024-01-01 10:01:30</td><td>101</td><td><code>UPDATE tb_contract_quantities_process ...</code></td><td>0</td></tr></tbody></table>
<p><strong>分析结论：</strong></p>
<ul><li>线程ID 100 的事务从 10:00 开始运行，已经锁定了5000行，很可能是<strong>阻塞事务</strong></li><li>线程ID 101 的事务在等待锁，这就是<strong>您的报错事务</strong></li></ul>
<p class="maodian"><a name="_lab2_5_5"></a></p><h3>4. 进一步定位阻塞关系</h3>
<div class="jb51code"><pre class="brush:sql;">-- 更精确地查找谁阻塞了谁
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;
</pre></div>
<p class="maodian"><a name="_lab2_5_6"></a></p><h3>5. 处理建议</h3>
<ol><li><p><strong>如果是测试环境</strong>：直接终止阻塞事务</p>
<div class="jb51code"><pre class="brush:sql;">KILL 100;-- 终止线程ID为100的连接
</pre></div></li><li><p><strong>如果是生产环境</strong>：</p>
<ul><li>联系相关业务负责人确认该长时间运行的事务是否可以终止</li><li>分析 <code>trx_query</code> 中的SQL，优化其性能</li><li>考虑在业务低峰期执行相关操作</li></ul></li></ol>
<p>通过这种方法，您就能准确识别出导致锁等待超时的根本原因了。</p>
<p class="maodian"><a name="_label6"></a></p><h2>阻塞事务 可能产生的原因</h2>
<p>阻塞事务的产生原因多种多样，以下是主要的分类和具体原因：</p>
<p class="maodian"><a name="_lab2_6_7"></a></p><h3>1. 事务设计问题</h3>
<p class="maodian"><a name="_label3_6_7_2"></a></p><h4>长事务</h4>
<div class="jb51code"><pre class="brush:sql;">-- 典型的长事务模式
BEGIN;
-- 执行复杂的业务逻辑
UPDATE large_table SET ... WHERE ...; -- 耗时操作
-- 中间可能包含业务逻辑、外部API调用等
COMMIT; -- 很久之后才提交
</pre></div>
<p><strong>特征</strong>：事务开始和提交时间间隔很长</p>
<p class="maodian"><a name="_label3_6_7_3"></a></p><h4>未提交的事务</h4>
<div class="jb51code"><pre class="brush:sql;">// 代码中忘记提交或回滚
@Transactional
public void processData() {
    // 执行更新操作
    updateTableA(...);
    // 如果这里发生异常，事务可能一直挂起
    if (someCondition) {
      return; // 忘记提交或回滚
    }
    // ... 其他操作
}
</pre></div>
<p class="maodian"><a name="_lab2_6_8"></a></p><h3>2. SQL性能问题</h3>
<p class="maodian"><a name="_label3_6_8_4"></a></p><h4>缺乏合适的索引</h4>
<div class="jb51code"><pre class="brush: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);-- 使用主键索引，只锁特定行
</pre></div>
<p class="maodian"><a name="_label3_6_8_5"></a></p><h4>全表扫描操作</h4>
<div class="jb51code"><pre class="brush:sql;">-- 导致锁表的操作
UPDATE table_a SET status = 1 WHERE unindexed_column = 'value';
DELETE FROM large_table WHERE create_time &lt; '2023-01-01';
</pre></div>
<p class="maodian"><a name="_lab2_6_9"></a></p><h3>3. 锁机制相关</h3>
<p class="maodian"><a name="_label3_6_9_6"></a></p><h4>锁升级</h4>
<ul><li><strong>行锁升级为表锁</strong>：当一条SQL需要锁定大量数据行时，InnoDB可能将锁升级为表锁</li><li><strong>间隙锁（Gap Lock）</strong>：在REPEATABLE READ隔离级别下，范围查询会锁定不存在的记录区间</li></ul>
<p class="maodian"><a name="_label3_6_9_7"></a></p><h4>死锁循环</h4>
<div class="jb51code"><pre class="brush:sql;">-- 事务A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 事务B (同时执行)
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
</pre></div>
<p class="maodian"><a name="_lab2_6_10"></a></p><h3>4. 应用架构问题</h3>
<p class="maodian"><a name="_label3_6_10_8"></a></p><h4>同步批量操作</h4>
<div class="jb51code"><pre class="brush:java;">// 在事务中处理大量数据
@Transactional
public void batchProcessContracts(List&lt;Long&gt; contractIds) {
    for (Long id : contractIds) {// 循环处理，事务时间很长
      updateContractStatus(id);
      insertProcessLog(id);
      // ... 其他操作
    }
}
</pre></div>
<p class="maodian"><a name="_label3_6_10_9"></a></p><h4>嵌套事务问题</h4>
<div class="jb51code"><pre class="brush:java;">@Transactional
public void mainProcess() {
    // 主事务开始
    updateMainTable();
   
    // 调用另一个事务方法
    subProcess();// 如果subProcess有@Transactional(propagation=REQUIRES_NEW)
   
    // 主事务继续...
}
</pre></div>
<p class="maodian"><a name="_lab2_6_11"></a></p><h3>5. 业务逻辑缺陷</h3>
<p class="maodian"><a name="_label3_6_11_10"></a></p><h4>用户交互式事务</h4>
<div class="jb51code"><pre class="brush:java;">@Transactional
public void approveContract(Long contractId) {
    // 开始事务
    updateContractStatus(contractId, "APPROVING");
    // 等待用户确认（事务一直打开！）
    waitForUserConfirmation();// 错误做法！
    updateContractStatus(contractId, "APPROVED");
}
</pre></div>
<p class="maodian"><a name="_label3_6_11_11"></a></p><h4>循环依赖更新</h4>
<div class="jb51code"><pre class="brush:sql;">-- 事务A
UPDATE table_a SET status = (SELECT count FROM table_b WHERE ...);
-- 事务B
UPDATE table_b SET count = (SELECT status FROM table_a WHERE ...);
</pre></div>
<p class="maodian"><a name="_lab2_6_12"></a></p><h3>6. 数据库配置问题</h3>
<p class="maodian"><a name="_label3_6_12_12"></a></p><h4>不合理的隔离级别</h4>
<ul><li><strong>READ UNCOMMITTED</strong>：脏读风险</li><li><strong>REPEATABLE READ</strong>：容易产生间隙锁（MySQL默认）</li><li><strong>SERIALIZABLE</strong>：严格的锁机制，性能差</li></ul>
<p class="maodian"><a name="_label3_6_12_13"></a></p><h4>连接池配置不当</h4>
<div class="jb51code"><pre class="brush:xml;"># 连接池配置问题
spring.datasource.hikari.maximum-pool-size=200# 连接数过多，竞争激烈
spring.datasource.hikari.connection-timeout=30000# 连接超时时间过长
</pre></div>
<p class="maodian"><a name="_lab2_6_13"></a></p><h3>7. 监控和诊断方法</h3>
<p class="maodian"><a name="_label3_6_13_14"></a></p><h4>识别阻塞事务的SQL</h4>
<div class="jb51code"><pre class="brush:sql;">-- 查看当前执行的所有SQL
SHOW PROCESSLIST;

-- 查看详细的锁信息
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;
</pre></div>
<p class="maodian"><a name="_lab2_6_14"></a></p><h3>8. 预防和解决方案</h3>
<p class="maodian"><a name="_label3_6_14_15"></a></p><h4>优化事务设计</h4>
<div class="jb51code"><pre class="brush:java;">// 正确的做法：短事务
public void processInShortTransactions(List&lt;Long&gt; ids) {
    for (Long id : ids) {
      transactionTemplate.execute(status -&gt; {
            // 每个ID在独立事务中处理
            updateContract(id);
            insertLog(id);
            return null;
      });
    }
}
</pre></div>
<p class="maodian"><a name="_label3_6_14_16"></a></p><h4>添加合适的索引</h4>
<div class="jb51code"><pre class="brush:sql;">-- 为查询和更新条件添加索引
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);
</pre></div>
<p class="maodian"><a name="_label3_6_14_17"></a></p><h4>使用乐观锁</h4>
<div class="jb51code"><pre class="brush:java;">// 使用版本号避免悲观锁
@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 &gt; 0;// 如果失败可以重试
}
</pre></div>
<p>通过分析这些可能的原因，您可以系统地排查和解决阻塞事务问题。</p>
<h2>总结</h2>

查看完整版本: MySQL锁等待超时错误详细解释原因和解决方案