MySQL死锁简单案例演示

原创

说文科技 2022-01-28 09:16:07 ©著作权

文章标签 mysql 死锁数据 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者说文科技的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL死锁简单案例演示【存在疑问】

请各位读者对本篇文章采取疑问的态度。

1.环境

mysql 5.7
windows

2.示例1

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.26 sec)

Client A起事务，以share 锁模式读取数据

mysql> start transaction;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM t WHERE i = 1 lock in SHARE mode;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Client B另起事务，删除表t中的数据

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上面的这个超时错将会在命令执行一段时间之后，才出现。其超时时间默认设置为50s：

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.03 sec)

在这个出错命令尚未出现之前，在Client A中继续执行如下命令：

mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (0.00 sec)

结果在Client B中出现的结果如下：

mysql>  DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

3.示例2【外键约束至死锁】

建表语句

CREATE TABLE `parent` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  KEY `id_for` (`id`),
  CONSTRAINT `id_for` FOREIGN KEY (`id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

执行SQL
在session A执行如下命令：

mysql> begin;
Query OK, 0 rows affected (0.03 sec)

mysql> delete from parent where id = 3;
Query OK, 1 row affected (0.01 sec)

在session B执行如下命令：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into child select 3;

发现Session B的回话一直呈阻塞态。

查看information_schema.innodb_locks表数据，如下：

mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 21305:145:3:4
lock_trx_id: 21305
  lock_mode: S
  lock_type: RECORD
 lock_table: `insidemysql`.`parent`
 lock_index: PRIMARY
 lock_space: 145
  lock_page: 3
   lock_rec: 4
  lock_data: 3
*************************** 2. row ***************************
    lock_id: 21303:145:3:4
lock_trx_id: 21303
  lock_mode: X
  lock_type: RECORD
 lock_table: `insidemysql`.`parent`
 lock_index: PRIMARY
 lock_space: 145
  lock_page: 3
   lock_rec: 4
  lock_data: 3
2 rows in set, 1 warning (0.01 sec)

发现出现死锁。

死锁过程很简单：Session A首先发起一个事务（以begin起），接着欲删除parent表中id=3的行记录。这个操作会发起锁操作，申请一个x锁【如上述SQL所示，lock_trx_id为21303，其lock_mode为x型】。然后另外的一个回话Session B发起一个事务（begin起），欲插入一条id=3的记录【如上述SQL所示，lock_trx_id为21305，其lock_mode为s型】。但是因为parent表中的id是child表中的id的外键，所以导致阻塞的产生。注意它们的lock_id 以及lock_data均相同，表明锁住的行记录相同。