跳到主要内容

锁

本地测试的环境：InnoDB 存储引擎；数据库隔离级别：repeatable read;

为什么需要锁呢？

并发下，多个事务对同一个数据进行修改，需要通过锁来保证事务的隔离性和一致性。

数据库锁分类​

1、共享锁、排他锁（从数据库管理的角度对锁进行划分）​

共享锁 share lock

读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

select ...  lock in share mode;

排他锁 exclusive lock

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

select ... for update;

意向锁

意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

2、乐观锁、悲观锁（从程序员的角度对锁进行划分）​

乐观锁和悲观锁并不是锁，而是锁的设计思想。

乐观锁

乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁， 也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。

  • 乐观锁的版本号机制 在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时， 会执行UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改， 修改就不会成功。

  • 乐观锁的时间戳机制 时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较， 如果两者一致则更新成功，否则就是版本冲突。

悲观锁

悲观锁（Pessimistic Locking）也是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现 ， 从而保证数据操作的排它性。

适用场景

  • 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题， 不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
  • 悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止 其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

3、行级锁、表级锁、页级锁（从数据库管理的角度对锁进行划分）​

  • 行级锁 innodb 引擎，只当前操作的行进行加锁。行级锁分为共享锁和排他锁
  • 表级锁 MyISAM 引擎，innodb 引擎，对当前操作的整张表加锁。表级锁定分为表共享锁和表独占写锁
  • 页级锁 BDB 引擎，一次锁定相邻的一组记录。

InnoDB 行锁是通过给索引项加锁来实现的。只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁!

  • 在不通过索引条件查询的时候，InnoDB 确实使用的是表锁，而不是行锁。
  • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
  • 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不用的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
  • 即便在条件中使用了索引字段,但**是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的 **, 如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁, 而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

死锁

MyISAM 是不会出现死锁问题的，因为它总是一次性获取所需的全部锁，要么全部满足，要么全部等待。但是在 InnoDB 中，锁是逐步获取的，这就造成了死锁的可能。

MySQL 中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种。 如果一条 sql 语句操作了主键索引，MySQL 就会锁定这条主键索引。 如果一条 sql 语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。

在 update、delete 语句中，MySQL 不仅锁定 where 条件扫描的所有索引记录，而且会锁定相邻的键值，即所谓的 next-key locking.

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这就会发送死锁。

发送死锁后，InnoDB 一般都可以检测到，并使一个事务回退，另一个获取锁完成事务。

避免死锁

  1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁几率。
  2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源。
  3. 对于非常容易死锁的业务部分，尝试使用表级锁。

行级锁​

不同的隔离级别，行级锁的种类是不同的。

  • read uncommitted , 只有记录锁，也就是仅仅把一条记录锁上。
  • repeatable read , 记录锁，间隙锁，

行级锁的种类

  • Record Lock 记录锁，也就是仅仅把一条记录锁上；
    • S 锁：共享锁，一个事务对一条记录加了 S 锁之后，允许其他事务继续对这条记录加 S 锁，不允许加 X 锁。
    • X 锁：排它锁，一个事务对一条记录加了 X 锁之后，不允许其他事务对这条记录加任何锁。
  • Gap Lock 间隙锁，锁定一个范围，但是不包含记录本身。只存在于 repeatable read 隔离级别中
    • 前开后开
    • 是为了解决可重复读隔离级别下的幻读问题。（通过锁定两个值之间的间隙，确保 InnoDB 在这些间隙内不会插入新的记录，从而避免幻读）
    • 间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系 ，因为间隙锁的目的是防止插入幻影记录而提出的。 （间隙锁就是为了防止幻读的，使用了间隙锁锁定了一定范围的记录，那么其他事务就不能在这个范围内新增记录了，这就防止了幻读问题。）
  • Next-key Lock : Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
    • 前开后闭
    • Next-key Lock 即能保护该记录，又能阻止其他事务将新记录插入到被保护记录前面的间隙中。
    • 如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

MySQL 是如何加锁的。​

加锁的对象是索引。加锁的基本单位是 next-key lock

next-key lock 在一些场景下会退化成记录锁或间隙锁。

在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。 例如，如果一个事务只查询一个索引列，那么 InnoDB 只需要对这个索引列加锁，而不需要加 next-key lock。

唯一索引等值查询​

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

  • 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
  • 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

记录存在

begin;
select * from user where id = 1 for update;
-- 查看事务执行过程中加了什么锁。
select * from performance_schema.data_locks\G;

为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的 next-key lock 会退化成 record lock 呢？

原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。

  • 主键具有唯一性，不允许重复添加
  • 对主键加记录锁，避免删除主键。

记录不存在

begin;
select * from user where id = 2 for update;
select * from performance_schema.data_locks\G;

next-key lock 会退化成 gap lock。

会在主键在 (1,5) 范围区间内加上间隙锁。

  • 在 (1,5) 增加数据会被阻塞
  • 增加主键为 5 的数据会被禁止，主键具有唯一性。

为什么唯一索引等值查询并且查询记录不存在的场景下，在索引树找到第一条大于该查询记录的记录后， 要将该记录的索引中的 next-key lock 退化成间隙锁？

原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁也能避免幻读的问题。

  • 为什么 id = 5 记录上的主键索引的锁不是 next-key lock？

    因为仅靠 gap lock 就可以解决幻读问题。使用 next-key lock 会把 id = 5 的记录使用记录锁锁住，其他事务就不能删除这条数据， 但这样做是没有必要的，因为 id = 5 的记录删除与不删除，都不会影响 id = 2 的记录的查询结果。

  • 为什么不可以针对不存在的记录加锁呢？

    因为锁是加载索引上的

总结

  • 记录存在，next-key lock 会退化成 record lock
  • 记录不存在，大于当前等值条件的第一个值，在这个值的索引上加间隙锁。 （此时，next-key lock 退化为 gap lock）.

唯一索引范围查询​

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key lock

  • 针对 >= 的范围查询，因为存在等值查询的条件，那么如果等值查询的记录时存在于表中，那么该记录 的索引中的 next-key lock 会退化成 record lock。
  • 针对 < <= 的范围查询，要看条件值的记录是否存在于表中
  • 如果不存在，扫描到终止范围查询的记录时，该记录的索引的 next-key lock 会退回成间隙锁，其他扫描到的记录，都是在

    这些记录的索引上加 next-key lock

  • 如果存在
  • 条件是 < 的范围查询，扫描到终止范围查询记录时，该记录的索引的 next-key lock 会退化成 gap lock，其他扫描到

    的记录都是在这些记录的索引上加 next-key lock

  • 条件是 <= 的范围查询，扫描到的记录都是加 next-key lock

supremum pseudo-record​

在 MySQL InnoDB 存储引擎中，每个索引的 B+ 树结构的最末端，存在一个特殊的记录，称为 supremum pseudo-record（最大伪记录）。 这个记录不对应任何表中的实际数据行，而是一个内部机制，用于辅助 InnoDB 进行各种操作，特别是与锁定机制和隔离级别相关的操作。

  • 边界标记
  • 锁定机制
  • 查询优化

> 范围查询​

边界值存在

begin;
select * from user where id > 15 for update;
select * from performance_schema.data_locks\G;

边界值不存在

begin;
select * from user where id > 14 for update;
select * from performance_schema.data_locks\G;

总结

  • 边界值存不存在，都是加 next-key lock

>= 范围查询​

边界值存在

begin;
select * from user where id >= 15 for update;
select * from performance_schema.data_locks\G;

边界值不存在

begin;
select * from user where id >= 14 for update;
select * from performance_schema.data_locks\G;

总结

  • 如果边界值存在，边界值上的锁会退化成记录锁。其他扫描到的值会加上 next-key lock
  • 如果边界值不存在，所有扫描到的值都加上 next-key lock

< 范围查询​

边界值不存在

begin;
select * from user where id <6 for update;
select * from performance_schema.data_locks\G;

边界值存在

begin;
select * from user where id <5 for update;
select * from performance_schema.data_locks\G;

总结

在执行 < 范围查询的时候，对于被查找到的值，会加上 next-key lock, 一直扫描到超过边界范围，找到第一个超过边界值的记录， 给这个记录加上间隙锁。

<= 范围查询​

边界值不存在

begin;
select * from user where id <= 6 for update;
select * from performance_schema.data_locks\G;

边界值存在

begin;
select * from user where id <= 5 for update;
select * from performance_schema.data_locks\G;

总结

在执行 <= 范围查询的时候，对于被查找到的值，会加上 next-key lock ,一直扫描到边界值，如果边界值存在，给边界值加上 next-key lock 后 停止扫描，否则的话，继续往后扫描。知道找到超过边界值的第一条记录，给这个记录加上 gap lock.

非唯一索引等值查询​

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引 （二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候， 只有满足查询条件的记录才会对它们的主键索引加锁。

mysql> select * from user;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | zhangsan1 |  19 |
|  5 | zhangsan2 |  21 |
| 10 | zhangsan3 |  22 |
| 15 | zhangsan4 |  20 |
| 20 | zhangsan5 |  39 |
+----+-----------+-----+
5 rows in set (0.00 sec)

记录不存在

begin;
select * from user where age = 25 for update;
select * from performance_schema.data_locks\G;
  1. 给 id = 20, age = 39 的记录加一个 gap lock。 gap lock 的范围：(22，29）

gap lock 会阻止新增 age = (22,29) 的记录。但是对于 age = 22 和 age = 29 的记录新增是如何处理呢？

插入语句在插入一条记录之前，需要先定位到该记录在 B+树 的位置，如果插入的位置 的下一条记录的索引上有间隙锁，才会发生阻塞。

二级索引树是如何存放数据的？ 二级索引树是按照二级索引值(age列) 按顺序存放的，在相同的二级索引值情况下，按照主键 id 的顺序存放。

  • 插入 age = 22 的记录
    • 成功 age = 22 , id < 10
    • 失败 age = 22 , id > 10
  • 插入 age = 39 的记录
    • 成功 age = 39, id > 20
    • 失败 age = 39, id < 20

当有一个事务持有二级索引的间隙锁 (22, 39) 时，插入 age = 22 或者 age = 39 记录的语句是否可以执行成功， 关键还要考虑插入记录的主键值，因为「二级索引值（age列）+主键值（id列）」才可以确定插入的位置， 确定了插入位置后，就要看插入的位置的下一条记录是否有间隙锁，如果有间隙锁， 就会发生阻塞，如果没有间隙锁，则可以插入成功。

记录存在

begin;
select * from user where age = 22 for update;
select * from performance_schema.data_locks\G;
  1. 给 id = 10，age = 22 的记录加 next-key lock (21,22]
  2. 给 id = 10 的记录加 record lock
  3. 给 id = 20，age = 39 的记录加 gap lock (22,39)

记录存在，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程， 直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁， 而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。 记录不存在，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引范围查询​

非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况 , 也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

mysql> select * from user;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | zhangsan1 |  19 |
|  5 | zhangsan2 |  21 |
| 10 | zhangsan3 |  22 |
| 15 | zhangsan4 |  20 |
| 20 | zhangsan5 |  39 |
+----+-----------+-----+
5 rows in set (0.00 sec)
begin;
select * from user where age >= 22  for update;
select * from performance_schema.data_locks\G;
  • 首先查询到 age = 22 的记录，对 age = 22 ,id = 10 的记录加 X 锁 ， 对 id = 10 的主键索引加 record lock.
  • 继续查找，会查询到 age = 39 的记录，对 age = 39 ,id = 20 的记录加 X 锁， 对 id = 20 的主键索引加 record lock.
  • 继续查找，知道找到 supremum pseudo-record，对其加 X 锁
  • 停止查询。

为什么对 age = 20 , id = 10 的记录加 X 锁，而不是退化成 record lock 呢？

因为 age 字段是非唯一索引，不具有唯一性，所以，如果只加 record lock 的话，就会导致幻读问题。 record lock 无法防止插入，只能方法删除和修改。这会导致其他事务插入一条 age = 22 的记录 （age = 22,id < 10 的记录）。这样前后两次查询的结果集不同。出现了幻读。

没有索引的查询​

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

总结​

  • 唯一索引等值查询
    • 记录存在，next-key lock 会退化成 record lock
    • 记录不存在，找到下一条记录，next-key lock 会退化成 gap lock
  • 非唯一索引等值查询
    • 记录存在，符合查询的记录加 next-key lock，找到一条不符合的记录，加 gap lock。 同时，在符合查询条件的记录的主键索引上加记录锁。
    • 记录不存在，找到第一条不符合的记录，加 gap lock。 因为不存在满足查询条件的记录，所以不会对主键索引加锁。
  • 范围查询
    • 唯一索引在满足一些条件的时候，索引的 next-key lock 会退化成记录锁或者间隙锁。
    • 非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况。
  • 非索引查询
    • 锁全表，每条记录加 next-key lock

update 没加索引会锁全表？​

update 语句的 where 条件没有使用索引就会全表扫描，于是就会对所有记录加上 next-key lock，相当于锁住了全表。 这个锁并不是在 update 语句执行完就释放，而是在事务提交之后，才会释放锁，在此期间，只能执行 select ... from .. 。

并不是使用了索引就不会锁全表，而是要看优化器最终选择的是索引扫描，还是全表扫描 ，如果走了全表扫描，该是会对全表的记录加锁。

如果发现，where 条条件带索引列，但还是走全表扫描，可以使用 force index([index_name]) 告诉优化器使用哪个索引。

MySQL 记录锁 + 间隙锁可以防止删除操作而导致的幻读吗？​

在使用 select ... from ... for update 语句的时候，会对查询到的记录加上记录锁，同时对查询到的记录的索引加上 next-key lock.

如果此时执行删除操作，由于 for update 会对查询到的语句加 record lock, 会阻塞删除操作。直到 for update 的事务提交。

因此，MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读。 （删除操作并不会执行成功。）

死锁​

表结构

id 为主键索引；order_no 为普通索引

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order` (`order_no`) USING BTREE
) ENGINE=InnoDB ;

数据

insert into t_order (id,order_no,create_date) values (1,1001,'2020-01-01 12:12:12');
insert into t_order (id,order_no,create_date) values (2,1002,'2020-01-01 12:12:13');
insert into t_order (id,order_no,create_date) values (3,1003,'2020-01-01 12:12:14');
insert into t_order (id,order_no,create_date) values (4,1004,'2020-01-01 12:12:15');
insert into t_order (id,order_no,create_date) values (5,1005,'2020-01-01 12:12:16');
insert into t_order (id,order_no,create_date) values (6,1006,'2020-01-01 12:12:17');

死锁的发生​

事务 A 要插入订单 1007，事务 B 要插入订单 1008。

mysql> select * from t_order;
+----+----------+---------------------+
| id | order_no | create_date         |
+----+----------+---------------------+
|  1 |     1001 | 2020-01-01 12:12:12 |
|  2 |     1002 | 2020-01-01 12:12:13 |
|  3 |     1003 | 2020-01-01 12:12:14 |
|  4 |     1004 | 2020-01-01 12:12:15 |
|  5 |     1005 | 2020-01-01 12:12:16 |
|  6 |     1006 | 2020-01-01 12:12:17 |
+----+----------+---------------------+
6 rows in set (0.00 sec)

分析下死锁的产生过程

select id from t_order where order_no = 1007 for update;

事务 A 会对 supremum pseudo-record 加一个 X 锁。范围是 (1006,+∞]

select id from t_order where order_no = 1008 for update;

事务 B 会对 supremum pseudo-record 加一个 X 锁。范围是 (1006,+∞]

在事务 A 执行 insert into t_order(order_no,create_date) values(1007,now()) 的时候会被阻塞，因为事务 B 对 supremum pseudo-record 加的 X 锁。

在事务 B 执行 insert into t_order(order_no,create_date) values(1008,now()) 的时候会被阻塞，因为事务 A 对 supremum pseudo-record 加的X 锁。

这样就发生了死锁。

为什么会产生死锁？​

InnoDB 引擎使用 next-key lock 来解决可重复读隔离级别下的幻读问题

next-key lock = record lock + gap lock.

  • record lock： 锁的是记录本身。
  • gap lock：锁的是一个范围，范围是两个值之间的区间。目的是防止其他事务在这个区间内新增数据。
begin;
//对读取的记录加共享锁
select ... lock in share mode;
commit; //锁释放

begin;
//对读取的记录加排他锁
select ... for update;
commit; //锁释放

在执行插入语句时，会在插入间隙上获取插入意向锁，而插入意向锁与间隙锁是冲突的，所以当其他事务持有该间隙的间隙锁时，需要 等待其他事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间时兼容的，索引两个事务中的 select ...fro update 语句不会相互影响。

事务A 与事务 B 在执行 insert 语句的时候，都在等对方释放间隙锁。这就产生了死锁。

为什么间隙锁之间时兼容的？

间隙锁的目的在于阻止区间被插入数据。因此时可以共存的。一个事务获取的间隙锁并不会影响另一个事务获取同一个间隙范围的间隙锁。 即两个事务可以同时获取同一个间隙的间隙锁。

需要注意的是，next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务 在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

记录锁分为 S 型，X 型。X 型的记录锁之间是冲突的。例如执行两个事务同时执行 select ... where id = 1 for update 语句， 后面执行的事务会被阻塞。因为这个语句会对 id = 1 的记录加上 X 型的记录锁。

插入意向锁

插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁的生成时机。 每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 Insert 语句会被阻塞。

Insert 语句是怎么加行级锁的？​

// TODO

产生死锁的条件​

互斥、占有且等待、不可强占用、循环等待。

如何避免死锁？

  • 设置事务等待锁的时间。 当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。通过参数 innodb_lock_wait_timeout
  • 开启主动死锁检测 。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。通过参数 innodb_deadlock_detect

查看最后一次死锁的日志​

mysql> SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2024-03-27 22:47:29 139756559329024 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 16 srv_active, 0 srv_shutdown, 46700 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 46
OS WAIT ARRAY INFO: signal count 43
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-27 22:46:17 139756474193664
*** (1) TRANSACTION:   *******事务 A 的信息*******
TRANSACTION 11092, ACTIVE 288 sec inserting   *******事务的 ID，以及事务激活的时间*******
mysql tables in use 1, locked 1  *******表使用情况，涉及到一个表，并且这个表被锁定*******
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1   *******事务A正在等待获取锁，事务A创建了3个锁结构，锁结构和相关关系占用 1128 字节内存，事务A已经对2行数据加了锁，事务A产生了1个撤销日志条目，用于在事务回滚时恢复数据。*******
MySQL thread id 18, OS thread handle 139756022585088, query id 813 localhost root update
insert into t_order(order_no,create_date) values(1007,now())   *******死锁的 sql*******

*** (1) HOLDS THE LOCK(S):   *******显示这个事务持有哪些锁*******
RECORD LOCKS space id 6 page no 5 n bits 80 index index_order of table `dbtest1`.`t_order` trx id 11092 lock_mode X   *******事务 A 的信息*******
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:   *******显示这个事务持在等待哪些锁释放*******
RECORD LOCKS space id 6 page no 5 n bits 80 index index_order of table `dbtest1`.`t_order` trx id 11092 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:   *******事务 B 的信息*******
TRANSACTION 11093, ACTIVE 237 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 139756559329024, query id 814 localhost root update
insert into t_order(order_no,create_date) values(1008,now())

*** (2) HOLDS THE LOCK(S):  *******显示这个事务持有哪些锁*******
RECORD LOCKS space id 6 page no 5 n bits 80 index index_order of table `dbtest1`.`t_order` trx id 11093 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:  *******显示这个事务持在等待哪些锁释放*******
RECORD LOCKS space id 6 page no 5 n bits 80 index index_order of table `dbtest1`.`t_order` trx id 11093 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)   *******死锁的处理结果*******
------------
TRANSACTIONS
------------
Trx id counter 11095
Purge done for trx's n:o < 11091 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421232067479928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421232067479120, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421232067477504, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421232067475888, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421232067475080, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 11092, ACTIVE 360 sec
5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 18, OS thread handle 139756022585088, query id 813 localhost root
---TRANSACTION 11091, ACTIVE 23610 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 8, OS thread handle 139756560385792, query id 595 localhost root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests ((null))
I/O thread 1 state: waiting for completed aio requests (insert buffer thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
1014 OS file reads, 849 OS file writes, 583 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 2 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          21010476
Log buffer assigned up to    21010476
Log buffer completed up to   21010476
Log written up to            21010476
Log flushed up to            21010476
Added dirty pages up to      21010476
Pages flushed up to          21010476
Last checkpoint at           21010476
Log minimum file id is       6
Log maximum file id is       6
198 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 649707
Buffer pool size   16383
Free buffers       15228
Database pages     1151
Old database pages 444
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 992, created 159, written 463
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1151, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=17756, Main thread ID=139756457408256 , state=sleeping
Number of rows inserted 12, updated 0, deleted 1, read 90
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 46, updated 361, deleted 8, read 8087
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
最后于 更新