时间戳

首页 > 解决方案 > 了解 MySQL InnoDB 锁

mysql - 了解 MySQL InnoDB 锁

问题描述

我对 MySQL InnoDB 锁的工作方式感到困惑。

为了更好地理解它，我进行了以下实验。我已经使用 Spring boot 和 JPA 在下表中插入/更新记录（尽管这个问题与 Spring boot 或 JPA 无关）。

表名： test

列名 类型 约束
ID 比格特 PK人工智能
姓名 瓦尔查尔
值 诠释
br_id 诠释

Innodb 锁等待超时： 50 秒

存储库： ITestRepository.java

@Modifying
@Query(value = "update test set val=val+1 where val>:val and br_id=:brId", nativeQuery = true) // Just a random update query
public void updateVal(Integer val, Integer brId);

服务： TestService.java

@org.springframework.transaction.annotation.Transactional
public void saveMany(final int brId) { // brId is always passed as 1
    for (int i = 0; i < 200; i++) {
        this.testRepository.updateVal(i, brId); // ======> LINE: 1
        this.testRepository.save(new TestEntity(String.valueOf(i), i, brId)); // ======> LINE: 2
        try {
            Thread.sleep(2000);
        } catch (final InterruptedException e) {
            e.printStackTrace();
        }
    }
}

@org.springframework.transaction.annotation.Transactional
public void saveOne(final int val, final int brId) { // brId is always passed as 2
    this.testRepository.updateVal(val, brId); // ======> LINE: 3
    this.testRepository.save(new TestEntity(String.valueOf(val), val, brId)); // ======> LINE: 4
}

案例1： 只触发saveMany一次方法

在这种情况下，一切正常，事务完成后插入 200 行而没有任何错误。

案例2：先 触发saveMany，后触发saveOne一次

在这种情况下，如果我们触发 whilesaveMany正在执行saveOne，将在 50 秒后因异常saveOne而失败，并在循环结束后成功完成。Lock wait timeoutsaveMany

案例 3：先 评论LINE: 1& LINE: 3OR 评论LINE: 2&LINE: 4和触发saveMany，然后再触发saveOne一次

在这种情况下，saveMany如果我们 trigger 正在执行saveOne，一切都会正常工作，并且这两个方法都会成功完成，没有任何异常。

案例四：先 评论LINE: 1触发saveMany，再评论saveOne一次

在这种情况下，如果我们触发， whilesaveMany正在执行saveOne，saveOne将失败并出现LINE: 3更新lock wait timeout操作异常。

案例5：先 评论LINE: 2触发saveMany，再评论saveOne一次

在这种情况下，如果我们触发， whilesaveMany正在执行saveOne，saveOne将失败并出现LINE: 4插入lock wait timeout操作异常。

从以上案例来看，这是我的推论：

  1. 并行inserts或updates不锁定表
  2. 并行insert并且update会锁表（先触发哪个操作先获取锁）

我无法理解以上两个推导是如何工作的。我的意思是从 MySQL 文档中，他们声明整个表在 InnoDB 中永远不会被锁定，并且只有行在执行时被锁定inserts或updates. 正如您在上述情况中所看到的，br_id两种方法总是不同的，因此updates在不同的行集上执行，那么为什么lock wait timeout会引发异常？此外，并行inserts或updates没有引起任何问题，如何以及为什么？

编辑1：

如果br_id没有被索引，那么它的工作方式与上述情况一样，但如果br_id被索引，则在执行与方法并行的方法Deadlock found when trying to get lock; try restarting transaction时立即抛出异常。saveOnesaveMany

标签： mysqltransactionslockinginnodb

解决方案

update test
    set val = ...
    where val > ...
      and br_id = ...

由于相关列上没有索引，所有行都被锁定。

最好有

INDEX(br_id)

或者

INDEX(br_id, val)

后者更适合定位要修改的行，但缺点是需要更新索引的 BTree。

请注意，有些“锁”是“间隙锁”。这涉及锁定索引值之间的差距。这有时会引起意外。（我不知道这是否与此更新有关。）

查看锁的一种工具是SHOW ENGINE INNODB STATUS; （由于信息是暂时的，它可能无法显示您需要的信息。）

BEGIN;
UPDATE ...
((spend lots of time before COMMIT))
COMMIT;

这将延迟或死锁任何需要相同行锁的竞争连接。

几乎总是 InnoDB 可以看到innodb_lock_wait_timeout为了获得必要的锁而简单地停止（最多几秒钟）。

InnoDB总是可以发现死锁，并且会ROLLBACK处理其中一个事务，同时让另一个完成。

有几种情况（尤其是“间隙锁定”，其中 InnoDB 在可能没有死锁时保守地声明死锁。（我不知道这是不是你的情况。）

推荐阅读