mysql - MySQL 5.7中如何产生gap lock死锁

Question

我想在 MySQL 5.7 上显式生成间隙锁死锁，以确保我的应用程序正确处理由于间隙锁引起的死锁。我正在尝试按照各种应该生成此内容的在线简单示例进行操作，但我根本没有遇到僵局。我想生成 MySQL 检测到死锁并回滚其中一个事务(让另一个事务通过)的情况。

我对此进行测试的方法是打开两个 MySQL 命令行窗口，在两个窗口中我都执行 start transaction; 然后是双方各自的查询以尝试模拟它。

我试过的几个例子是这样的: http://thushw.blogspot.com.mt/2010/11/mysql-deadlocks-with-concurrent-inserts.html

还有这个:https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

在 5.7 中做了一些改变，让它更好地检测间隙锁并避免它们。是否有任何其他我可以遵循的生成它们的简单示例？

Answer 1

这个问题可能已经过时了。但是，我仍然想发布我的答案以帮助有相同问题的其他人。

1. Initial Mysql 5.7 environment by using docker:

docker compose 文件如下:

version: '3.3'
services:
  mysql:
    image: mysql:5.7
    container_name: lzy-test-db-mysql-57
    environment:
      MYSQL_ROOT_PASSWORD: xxxxx
      MYSQL_DATABASE: xxxxx
      MYSQL_USER: xxxxx
      MYSQL_PASSWORD: xxxxx
    ports:
      - 3307:3306

启动你的mysql 5.7 docker:

PS E:\docker-env\test\mysql\57> ls


    Directory: E:\docker-env\test\mysql\57


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----        7/23/2020  10:26 AM            539 docker-compose.yml
PS E:\docker-env\test\mysql\57> docker-compose up -d
Creating network "57_default" with the default driver
Creating lzy-test-db-mysql-57 ... done

2. Connect to your mysql server. Check environment. tx_isolation should be RR

mysql> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

3. Create table

CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `p_name` (`name`)
) ENGINE=InnoDB CHARSET=utf8;

4. Prepare data

insert into t (name) value ('A'), ('C'), ('D');

5. Generate gap lock deadlock

+====================================+============================================================+
|             Session A              |                         Session B                          |
+====================================+============================================================+
| begin;                             |                                                            |
+------------------------------------+------------------------------------------------------------+
|                                    | begin;                                                     |
+------------------------------------+------------------------------------------------------------+
| delete from t where name = 'C';    |                                                            |
+------------------------------------+------------------------------------------------------------+
|                                    | delete from t where name = 'C';  --Blocked!                |
+------------------------------------+------------------------------------------------------------+
| insert into t (name) values ('B'); |                                                            |
+------------------------------------+------------------------------------------------------------+
|                                    | ERROR 1213 (40001): Deadlock found when trying to get lock |
+------------------------------------+------------------------------------------------------------+

出现这种死锁的原因，请引用How does MySQL (Innodb) perform next-key locking when deleting record